The after_destroy callback in the list plugin causes a syntax error when reordering subclass instances whose position is defined on a root class (using class_table_inheritance)

[thomascchen]

Description

The after_destroy callback in the list plugin that reorders entries after destroying an instance seems to cause a syntax error when trying to reorder instances of a subclass (using class_table_inheritance) whose :position field is defined on the root class and is scoped to another column, in my case a foreign key reference to another table. This seems like a bug to me, but I'm not 100% sure so thought I'd post here before opening an issue.

A Simplified Example

class Exercise < Sequel::Model
  one_to_many :items
  one_to_many :notes
end

class Note < Item
end

class Item < Sequel::Model
  plugin :class_table_inheritance, key: :kind
  plugin :list, scope: :exercise_id

  many_to_one :exercise
end

When I try to destroy an instance of note (e.g., note.destroy), I get the following output:

  Sequel (0.6ms)  SELECT * FROM "items" ORDER BY "exercise_id" DESC, "position" DESC LIMIT 1
  Sequel (0.3ms)  BEGIN
  Sequel (0.3ms)  DELETE FROM "notes" WHERE ("id" = 1)
  Sequel (0.4ms)  DELETE FROM "items" WHERE ("id" = 1)
  Sequel (0.3ms)  UPDATE (SELECT "items"."id", "items"."exercise_id", "items"."position", "items"."kind", "items"."discarded_at", "items"."created_at", "items"."updated_at", "notes"."body" FROM "items" INNER JOIN "notes" ON ("notes"."id" = "items"."id")) AS "items" SET "position" = ("position" - 1) WHERE (("exercise_id" = 1) AND ("position" > 1))
  Sequel (0.1ms)  ROLLBACK

/Users/thomaschen/.rbenv/versions/3.1.2/lib/ruby/gems/3.1.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/pg.rb:110:in `exec': PG::SyntaxError: ERROR:  syntax error at or near "(" (Sequel::DatabaseError)
LINE 1: UPDATE (SELECT "items"."id", "exerc...
               ^
/Users/thomaschen/.rbenv/versions/3.1.2/lib/ruby/gems/3.1.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/pg.rb:110:in `exec': ERROR:  syntax error at or near "(" (PG::SyntaxError)
LINE 1: UPDATE (SELECT "items"."id", "exerc...
               ^

I was able to fix this by updating the scope on the Item root class to use a proc, which I adapted from here:

class Item < Sequel::Model
  plugin :class_table_inheritance, key: :kind
  plugin :list, scope: proc {|obj| Item.order_prepend(:exercise_id).where(exercise_id: obj.exercise_id)} 

  many_to_one :exercise
end

This then gives me the following SQL output, which is more in line of what I would expect:

  Sequel (184.1ms)  SELECT * FROM "items" ORDER BY "position" DESC LIMIT 1
  Sequel (0.2ms)  BEGIN
  Sequel (0.2ms)  DELETE FROM "notes" WHERE ("id" = 1)
  Sequel (0.2ms)  DELETE FROM "items" WHERE ("id" = 1)
  Sequel (0.1ms)  UPDATE "items" SET "position" = ("position" - 1) WHERE (("exercise_id" = 1) AND ("position" > 1))
  Sequel (0.4ms)  COMMIT

Is this behavior expected, and are we meant to pass a proc to the :scope option in cases like this, or does this seem like a bug? Thank you!

[jeremyevans]

I don't think this is a bug. When you use class_table_inheritance, subclass model datasets use a subquery of a joined dataset. When you use the list plugin, the after_destroy hook uses Dataset#update to update the position of all following members in the list in a single query. That cannot work when the model's dataset is a subquery of a joined dataset.

I'll update the list plugin documentation to specify that you must provide the :scope option if the model's dataset uses a subquery.

[thomascchen]

Ah okay, that makes sense. Thank you!