Using .limit & .order

[TheKidCoder]

I have a query that I would like to see working:

ApplicationDocument.root.order(state: :asc).limit(10).find_forest

When I run this I get an error:

  ApplicationDocument Load (26.0ms)  WITH RECURSIVE "all_nodes" AS ( SELECT  "application_documents"."id", "application_documents"."parent_id" FROM "application_documents"  WHERE "application_documents"."parent_id" IS NULL  ORDER BY "application_documents"."state" ASC LIMIT 10 UNION SELECT "application_documents"."id", "application_documents"."parent_id" FROM "application_documents" INNER JOIN "all_nodes" ON "application_documents"."parent_id" = "all_nodes"."id" ) SELECT * FROM "all_nodes" INNER JOIN "application_documents" ON "all_nodes"."id" = "application_documents"."id"  ORDER BY position
ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: syntax error at or near "UNION"
  Position: 242: WITH RECURSIVE "all_nodes" AS ( SELECT  "application_documents"."id", "application_documents"."parent_id" FROM "application_documents"  WHERE "application_documents"."parent_id" IS NULL  ORDER BY "application_documents"."state" ASC LIMIT 10 UNION SELECT "application_documents"."id", "application_documents"."parent_id" FROM "application_documents" INNER JOIN "all_nodes" ON "application_documents"."parent_id" = "all_nodes"."id" ) SELECT * FROM "all_nodes" INNER JOIN "application_documents" ON "all_nodes"."id" = "application_documents"."id"  ORDER BY position
ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: syntax error at or near "UNION"
  Position: 242: WITH RECURSIVE "all_nodes" AS ( SELECT  "application_documents"."id", "application_documents"."parent_id" FROM "application_documents"  WHERE "application_documents"."parent_id" IS NULL  ORDER BY "application_documents"."state" ASC LIMIT 10 UNION SELECT "application_documents"."id", "application_documents"."parent_id" FROM "application_documents" INNER JOIN "all_nodes" ON "application_documents"."parent_id" = "all_nodes"."id" ) SELECT * FROM "all_nodes" INNER JOIN "application_documents" ON "all_nodes"."id" = "application_documents"."id"  ORDER BY position
from arjdbc/jdbc/RubyJdbcConnection.java:838:in `execute_query'

[jackc]

Hmm... That is could be difficult. Conceptually this could be read as find the first 10 root nodes ordered by state and then get the forests for them. I don't think it is possible to do in a single query. I think it would need to first do the query to get the correct roots, then find forest on all of them, then sort the returned records in Ruby to the same order as the first query returned. The following code (untested) should do what you want:

roots = ApplicationDocument.root.order(state: :asc).limit(10)
trees = ApplicationDocument.where(id: roots.map(&:id)).find_forest
trees.sort_by! { |t| roots.index(t) }

I'm not sure what I think of edge supporting this natively. It would be nice, but it would mean find_forest would need to inspect the already build ARel tree to see if it needs to do the 2 queries.

[TheKidCoder]

Thanks.

I figured it wasn't a simple problem. Feel free to close if you would like.