rcte_tree problem when using model with set_dataset_filter #581

Closed
agios opened this Issue Nov 9, 2012 · 2 comments

Comments

Projects
None yet
2 participants

agios commented Nov 9, 2012

When using a model with plugin :rcte_tree and set_dataset filter, the generated query for descendants causes the following error on postgresql:

ERROR - PG::Error: ERROR: column reference "xxx" is ambiguous

Example:

Sequel.migration do            
  up do                        
    create_table :tests do     
      primary_key :id
      foreign_key :parent_id, :test
      DateTime :deleted_at, :null => true
    end
  end
end

class Test < Sequel::Model
  plugin :rcte_tree
  set_dataset filter(:deleted_at => nil)
end

Test.create.descendants #Throws the error above, xxx => deleted_at

The generated SQL in this case is

WITH RECURSIVE "t" AS (
  SELECT * FROM "tests" WHERE (("deleted_at" IS NULL) AND ("parent_id" = 1)) 
  UNION ALL (SELECT "tests".* FROM "tests" INNER JOIN "t" ON ("t"."id" = "tests"."parent_id") WHERE ("deleted_at" IS NULL))
) SELECT * FROM "t" AS "tests" WHERE ("deleted_at" IS NULL)

The fix in SQL is simple, I just don't understand how to implement it in the code

WITH RECURSIVE "t" AS (
  SELECT * FROM "tests" WHERE (("deleted_at" IS NULL) AND ("parent_id" = 1)) 
  UNION ALL (SELECT "t1".* FROM "tests" AS "t1" INNER JOIN "t" ON ("t"."id" = "t1"."parent_id") WHERE ("t1"."deleted_at" IS NULL))
) SELECT * FROM "t" AS "tests" WHERE ("deleted_at" IS NULL)
Owner

jeremyevans commented Nov 9, 2012

Can't you just qualify the column in the set_dataset filter: set_dataset filter(:tests__deleted_at=>nil)? I don't understand the purpose of the aliasing in your fixed SQL.

agios commented Nov 9, 2012

True, if it is done that way it works like a charm, thanks!

On Fri, Nov 9, 2012 at 6:32 PM, Jeremy Evans notifications@github.comwrote:

Can't you just qualify the column in the set_dataset filter: set_dataset
filter(:tests__deleted_at=>nil)? I don't understand the purpose of the
aliasing in your fixed SQL.


Reply to this email directly or view it on GitHubhttps://github.com/jeremyevans/sequel/issues/581#issuecomment-10235382.

jeremyevans closed this Nov 9, 2012

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment