Skip to content

Instantly share code, notes, and snippets.

@halostatue
Created April 26, 2016 20:05
Show Gist options
  • Save halostatue/b4039c821cf30af844b7d2da5820c7d7 to your computer and use it in GitHub Desktop.
Save halostatue/b4039c821cf30af844b7d2da5820c7d7 to your computer and use it in GitHub Desktop.
A small issue with many_to_one in nested models
require 'sqlite3'
require 'sequel'
require 'ostruct'
require_relative 'schema'
require_relative 'models'
require 'byebug'
$prime = Catalog.create(name: 'prime')
$prime_v1 = CatalogVersion.create(version: 1, catalog_id: $prime.id)
$prime.update(active_version_id: $prime_v1.id)
$cat = OpenStruct.new
%w(shoes boots flats bags purses backpacks luggage carryons).each do |name|
$cat[name] = Category.create(name: name, catalog_id: $prime.id)
$cat[name].add_tree(catalog_version_id: $prime_v1.id)
end
%w(boots flats).each do |name|
$cat[name].tree.update(parent_id: $cat.shoes.tree.id)
end
%w(purses backpacks luggage).each do |name|
$cat[name].tree.update(parent_id: $cat.bags.tree.id)
end
$cat.carryons.tree.update(parent_id: $cat.luggage.tree.id)
class Catalog < Sequel::Model
one_to_many :catalog_versions
one_to_one :active_version, class: :CatalogVersion
one_to_many :categories
one_to_many :products
end
class CatalogVersion < Sequel::Model
many_to_one :catalogs
one_to_many :category_trees, class: 'Category::Tree'
end
class Category < Sequel::Model
many_to_one :catalog
one_to_many :trees, class: 'Category::Tree'
def tree(active_version_id = catalog.active_version_id)
trees_dataset[catalog_version_id: active_version_id]
end
class Tree < Sequel::Model(:category_trees)
plugin :rcte_tree
many_to_one :category_version
many_to_one :category# class: '::Category'
end
end
OK
SELECT `products`.*, `categories_products`.`sort_order` AS 'in_category_sort_order' FROM `products` INNER JOIN `categories_products` ON (`categories_products`.`product_id` = `products`.`id`) WHERE (`categories_products`.`category_id` = 2) ORDER BY `categories_products`.`sort_order`, `products`.`sort_order`
[#<Product @values={:id=>3, :name=>"DocEms", :sort_order=>2, :in_category_sort_order=>-2}>, #<Product @values={:id=>2, :name=>"Cowboys", :sort_order=>1, :in_category_sort_order=>-1}>, #<Product @values={:id=>1, :name=>"Stompers", :sort_order=>0, :in_category_sort_order=>0}>]
----
FAIL
SELECT `products`.*, `categories_products`.`sort_order` AS 'in_category_sort_order' FROM `products` WHERE (`products`.`id` IN (SELECT `categories_products`.`product_id` FROM `products` INNER JOIN `categories_products` ON (`categories_products`.`product_id` = `products`.`id`) WHERE ((`categories_products`.`category_id` = 1) OR (`categories_products`.`category_id` = 2)))) ORDER BY `categories_products`.`sort_order`, `products`.`sort_order`
SQLite3::SQLException: no such column: categories_products.sort_order
----
OK
SELECT * FROM `categories` WHERE (`id` IN (WITH `t` AS (SELECT * FROM `categories` WHERE (`parent_id` = 1) UNION ALL SELECT `categories`.* FROM `categories` INNER JOIN `t` ON (`t`.`id` = `categories`.`parent_id`)) SELECT * FROM (SELECT * FROM (SELECT `id` FROM `categories` WHERE (`id` = 1) LIMIT 1) AS 't1' UNION SELECT `id` FROM `t` AS 'categories') AS 't1'))
[#<Category @values={:id=>1, :parent_id=>nil, :name=>"shoes"}>, #<Category @values={:id=>2, :parent_id=>1, :name=>"boots"}>]
----
FAIL
SELECT `products`.*, `categories_products`.`sort_order` AS 'in_category_sort_order' FROM `products` WHERE (`products`.`id` IN (SELECT `categories_products`.`product_id` FROM `products` INNER JOIN `categories_products` ON (`categories_products`.`product_id` = `products`.`id`) WHERE ((`categories_products`.`category_id` = 1) OR (`categories_products`.`category_id` = 2)))) ORDER BY `categories_products`.`sort_order`, `products`.`sort_order`
SQLite3::SQLException: no such column: categories_products.sort_order
----
DB = Sequel.sqlite
DB.extension :null_dataset
DB.create_table :catalogs do
primary_key :id
String :name, null: false
end
DB.create_table? :catalog_versions do
primary_key :id
foreign_key :catalog_id, :catalogs, deferrable: true, on_delete: :cascade,
null: false
Integer :version, null: false
end
DB.alter_table :catalogs do
add_foreign_key :active_version_id, :catalog_versions, deferrable: true,
on_delete: :set_null
end
DB.create_table? :categories do
primary_key :id
foreign_key :catalog_id, :catalogs, deferrable: true, on_delete: :cascade,
null: false
String :name
end
DB.create_table? :category_trees do
primary_key :id
foreign_key :catalog_version_id, :catalog_versions, deferrable: true,
on_delete: :cascade, null: false
foreign_key :category_id, :categories, deferrable: true, on_delete: :cascade,
null: false
foreign_key :parent_id, :category_trees, deferrable: true,
on_delete: :set_null
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment