A many_to_many issue I’m experiencing with Sequel

manyjoin.rb

require 'sqlite3'
require 'sequel'
require 'ostruct'

require_relative 'schema'
require_relative 'models'

require 'byebug'

$cat = OpenStruct.new
%w(shoes boots).each do |name|
  $cat[name] = Category.create(name: name)
end

$cat.boots.update(parent_id: $cat.shoes.id)

$product = OpenStruct.new
%w(Stompers Cowboys DocEms).each_with_index do |name, index|
  product = Product.create(
    name: name,
    sort_order: index,
  )

  $product[name.downcase] = product

  CategoryProduct.create(
    category_id: $cat.boots.id,
    product_id: product.id,
    sort_order: -index,
  )
end

def example(msg = nil)
  puts msg if msg
  yield
rescue => ex
  puts ex.message
ensure
  puts "\n----\n\n"
end

example "OK" do
  puts $cat.boots.products_dataset.sql
  p $cat.boots.products
end

# This does *not* work and blows up with:
# SQLite3::SQLException: no such column: categories_products.sort_order
# (Sequel::DatabaseError)
example "FAIL" do
  puts Category.products.sql
  p Category.products.all
end

# This produces the list of descendant categories…
example "OK" do
  puts Product.descended_from_category($cat.shoes).sql
  p Product.descended_from_category($cat.shoes).all
end

# This does *not* work and blows up with:
# SQLite3::SQLException: no such column: categories_products.sort_order
# (Sequel::DatabaseError)
example "FAIL" do
  puts Product.descended_from_category($cat.shoes).products.sql
  p Product.descended_from_category($cat.shoes).products.all
end

models.rb

# If this works, it would be useful.
Sequel::Model.plugin :dataset_associations

class Category < Sequel::Model
  # Categories are a tree hierarchy. Shoes have Boots, Heels, Athletic, and
  # Flats.
  plugin :rcte_tree

  # A sorted list of products belonging to this category. Sorted by
  # categories_products.sort_order and products.sort_order.
  many_to_many :products,
    order: %i(categories_products__sort_order products__sort_order),
    select: [
      Sequel.expr(:products).*,
      Sequel.expr(:categories_products__sort_order).as(:in_category_sort_order)
    ]
end

class CategoryProduct < Sequel::Model(:categories_products)
  many_to_one :category
  many_to_one :product

  # Because the association *is* the PK; no synthetic key here.
  unrestrict_primary_key
end

class Product < Sequel::Model
  many_to_many :categories

  dataset_module do
    def descended_from_category(category)
      return nullify unless category

      sq = category.this.select(:id).
        union(category.descendants_dataset.select(:id))
      Category.where(id: sq)
    end
  end
end

result.txt

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

----

schema.rb

DB = Sequel.sqlite
DB.extension :null_dataset

DB.create_table? :categories do
  primary_key :id
  foreign_key :parent_id, :categories, deferrable: true,
    on_delete: :set_null
  String :name
end

DB.create_table? :products do
  primary_key :id
  String :name
  Integer :sort_order
end

DB.create_table? :categories_products do
  foreign_key :category_id, :categories, deferrable: true, on_delete: :cascade,
    null: false
  foreign_key :product_id, :products, deferrable: true, on_delete: :cascade,
    null: :false
  primary_key [ :category_id, :product_id ]

  Integer :sort_order
end