Closed serra92 closed 1 year ago
Looking at the SQL code generated, it seems that there are some extra quotation marks on the query that is being generated for the recursive view.
Hey @serra92 thanks for sending this in. What version of Rails are you on?
Currently I'm using rails (6.0.3.1). Not sure if this may be a thing, but since your implementation is using Arel can arel_extensions (2.0.8) dependency I'm using also be the culprit?
From what i could gather from the query, it's the Arel Node As that is putting extra quotation marks.
Ah yes, I believe that is probably the issue. I'm starting a Rails 6 project Wednesday of this coming week and I'll update the library to support it. Unless you'd like to take a crack at it yourself before then.
I can try to but no promises. Any specific structure for the PRs?
That's all any of us can ask for :)
Ideally start with a failing test. If one of the existing tests fails, that's acceptable. Other than that the basic contribution guidelines are here:
https://github.com/take-five/activerecord-hierarchical_query/blob/master/CONTRIBUTING.md
Hey @serra92 is this still an issue? We merged a Rails 6.1 fix recently and it may have fixed it.
Hello,
Currently, I'm using this gem to try it out on my data model, and I'm having Postgres SQL syntax errors on the most basic recursive queries. This is the table in question:
create_table "hierarchy_level_elements", force: :cascade do |t| t.string "name" t.bigint "parent_id" t.bigint "level_id", null: false t.datetime "created_at", precision: 6, null: false t.datetime "updated_at", precision: 6, null: false t.index ["level_id"], name: "index_hierarchy_level_elements_on_level_id" t.index ["parent_id"], name: "index_hierarchy_level_elements_on_parent_id" end
And this is my query:
HierarchyLevelElement.join_recursive do |query| query.start_with(parent_id: nil) .connect_by(id: :parent_id) .order_siblings(:name) end
What I get is the following error:
ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR: syntax error at or near ""( SELECT "")
The query that the gem is generating is the following:
SELECT "hierarchy_level_elements".* FROM "hierarchy_level_elements" INNER JOIN (WITH RECURSIVE "hierarchy_level_elements__recursive" AS "( SELECT "hierarchy_level_elements"."id", "hierarchy_level_elements"."parent_id", ARRAY[ROW_NUMBER() OVER (ORDER BY "hierarchy_level_elements"."name" ASC)] AS "__order_column" FROM "hierarchy_level_elements" WHERE "hierarchy_level_elements"."parent_id" IS NULL UNION ALL SELECT "hierarchy_level_elements"."id", "hierarchy_level_elements"."parent_id", "hierarchy_level_elements__recursive"."__order_column"||ROW_NUMBER() OVER (ORDER BY "hierarchy_level_elements"."name" ASC) FROM "hierarchy_level_elements" INNER JOIN "hierarchy_level_elements__recursive" ON "hierarchy_level_elements__recursive"."id" = "hierarchy_level_elements"."parent_id" )" SELECT "hierarchy_level_elements__recursive".* FROM "hierarchy_level_elements__recursive") AS ""hierarchy_level_elements__recursive"" ON "hierarchy_level_elements"."id" = "hierarchy_level_elements__recursive"."id" ORDER BY "hierarchy_level_elements__recursive"."__order_column" ASC LIMIT $1 [["LIMIT", 11]]
Can someone help me? Am I doing something wrong?
Thanks in advance