search

jackc / edge

Edge provides graph functionality to ActiveRecord.
MIT License
90 stars 10 forks source link

.find_forest broken in 0.5.0 #19

Open TheKidCoder opened 8 years ago

TheKidCoder commented 8 years ago

In 0.4.4:

Group.where(name: "Universal Questions").find_forest

will generate the following SQL:

WITH RECURSIVE "all_nodes" AS ( SELECT "prompts"."id", "prompts"."parent_id" FROM "prompts" WHERE "prompts"."type" IN ('Group') AND "prompts"."name" = $1 UNION SELECT "prompts"."id", "prompts"."parent_id" FROM "prompts" INNER JOIN "all_nodes" ON "prompts"."parent_id" = "all_nodes"."id" ) SELECT * FROM "all_nodes" INNER JOIN "prompts" ON "all_nodes"."id" = "prompts"."id"  ORDER BY position
Group.where(name: "Universal Questions").find_forest.first.children.count #70

In 0.5.0: The same ruby statement generates the following query:

WITH RECURSIVE all_nodes AS (
            SELECT "prompts"."id", "prompts"."parent_id" FROM "prompts" WHERE "prompts"."type" IN ('Group') AND "prompts"."name" = 'Universal Questions'
            UNION
            SELECT "prompts"."id", "prompts"."parent_id" FROM "prompts" INNER JOIN all_nodes ON "prompts"."parent_id"=all_nodes."id" WHERE "prompts"."type" IN ('Group')
          )
          SELECT "prompts".* FROM "prompts" INNER JOIN all_nodes USING("id") WHERE "prompts"."type" IN ('Group')  ORDER BY position
Group.where(name: "Universal Questions").find_forest.first.children.count #0

Returning only a single record with the descendants & children properties being empty arrays.

jackc commented 8 years ago

Based on the SQL you have, it looks like the problem is the where clause is being applied to the children as well as the root nodes when it should just be applied to the root nodes. Do you have a complete test case? I added a test to try to reproduce this (https://github.com/jackc/edge/blob/master/spec/forest_spec.rb#L168), but I couldn't get it to fail.