Having syntax errors with this gem

[serra92]

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

[serra92]

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.

[zachaysan]

Hey @serra92 thanks for sending this in. What version of Rails are you on?

[serra92]

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?

[serra92]

From what i could gather from the query, it's the Arel Node As that is putting extra quotation marks.

[zachaysan]

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.

[serra92]

I can try to but no promises. Any specific structure for the PRs?

[zachaysan]

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

[zachaysan]

Hey @serra92 is this still an issue? We merged a Rails 6.1 fix recently and it may have fixed it.