take-five / activerecord-hierarchical_query Goto Github PK
View Code? Open in Web Editor NEWSimple DSL for creating recursive queries with ActiveRecord
License: MIT License
Simple DSL for creating recursive queries with ActiveRecord
License: MIT License
Hi there,
Thank you for making this. I know you do not have a lot of time so I've created a branch for Rails 5.1 support and I've placed it here:
https://github.com/zachaysan/activerecord-hierarchical_query/tree/rails-5.1
If you would like me to created a pull request you can either add me to the repo and I can create the same branch for you or you can create the branch yourself and I'll issue the pull request. It's up to you.
All the tests pass after updating some gems and I verified that basic features work with my rails project. I try to keep my rails projects as up to date as possible so I will continue to upgrade this gem as I need it.
For other using rails-5.1 the following code in your Gemfile will do the trick as a temporary measure:
# TODO: Check back at https://github.com/take-five/activerecord-hierarchical_query to replace gem
gem "activerecord-hierarchical_query",
">= 1.0.0",
git: "https://github.com/zachaysan/activerecord-hierarchical_query",
branch: "rails-5.1"
Hi,
I'd like to start using PG 1.2, and found this incompatibility:
Bundler could not find compatible versions for gem "pg":
In Gemfile:
pg (~> 1.2)
activerecord-hierarchical_query (= 1.2.2) was resolved to 1.2.2, which depends on
pg (< 1.2, >= 0.21)
Could activerecord-hierarchical_query be updated to support pg 1.2? I'm happy to help if needed, but I thought I'd bring it up in an issue first.
Thanks, and happy new year!
Simply adding Rails 5.0 to dependency leads to broken tests.
Does this gem support LTree, and if not, are there plans to add support for it?
I'm trying to traverse a table with circular references. The table looks like this:
create_table "umbrella_relationships" do |t|
t.integer "umbrella_id"
t.integer "underlying_id"
t.index ["umbrella_id"], name: "index_umbrella_id_on_umbrellables", using: :btree
t.index ["underlying_id"], name: "index_underlying_id_on_umbrellables", using: :btree
end
There are umbrella_relationships
record pairs where the foreign keys permutate (i.e. for a record with { umbrella_id: 1, underlying_id: 2 }
, it'd be usual to see another record with { umbrella_id: 2, underlying_id: 1 }
)
To retrieve umbrella_relationships
that "stack" above a given underlying_id
, I wrote the following:
UmbrellaRelationship.join_recursive do |query|
query.
start_with(underlying_id: id).
connect_by(umbrella_id: :underlying_id).
nocycle.
distinct
end
However, for an example that expects to get 13 records back, this returns a 13310-row result set, with many duplications of the expected 13 records. My first guess is that the query would iterate multiple times through all relationships, using the nocycle
method to detect where to stop, but, unless I'm not understanding something correctly, the call to distinct
should apply to CTEs, so I fail to see why am I receiving so many records. This scales to be a major problem when expecting longer stacks, where I keep running into OOM errors on the RDBMS's end. Could you please advise on how to minimize memory usage on a query like this?
I have tested this in Postgres 9.4, 9.6 and 12.2, in case it helps. Thanks!
Is there a reason to not support it? Thanks
Hi,
Is rails 7 support coming?
ruby 2.1.2, rails 3.2.14, gem version 1.0.0
When traversing descendants, i've got error from .rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-hierarchical_query-1.0.0/lib/active_record/hierarchical_query/cte/non_recursive_term.rb:18:in `bind_values'
def bind_values
scope.bound_attributes
end
I've looked into source here, at github, and have found that in master branch you have next lines in that file:
def bind_values
scope.bind_values
end
When i replaced the line in this local file, i've got similar error for file .rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-hierarchical_query-1.0.0/lib/active_record/hierarchical_query/cte/recursive_term.rb
That error had been bypassed the same way and traversing started to work as expected.
Help me, please, to use your very handful gem without that local files editings.
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
Initially including the gemfile did not allow me to use the join_recursive function immediately in the console I had to do:
require 'active_record/hierarchical_query'
Is there a specific reason why the pg dependency is set to ~> 0.21 in rails5 branch? The pg gem is at 1.1.3 currently.
We have a hierarchical tree of X
which this works well for but each leaf on that tree has_many Y
relations and we need a way to recursively select all of the Y
from all descendants of a particular instance x
of X
So / in pseudocode
class X < ActiveRecord
belongs_to :parent, class_name: "X", optional: true, touch: true
has_many :children, inverse_of: :parent, class_name: "X", foreign_key: :parent
has_many :y
end
class Y < ActiveRecord
belongs_to :x
end
Then we would want
x = X.all.sample
ys = x.all_child_ys # this is all elements of Y which are in z.ys for all z which are of type X and children of x
Is there a way to do this now or some ( reasonable ) way to add it as a new feature ?
There's a new gem version, but no tag for the release. Could you add one, so we can see what is in the new version?
Hi,
I'm trying to use v1.0.0 with Rails 5, and my queries don't quite work. I could really use some help.
This does what is expected (gets the ancestors as a relation):
relation = self.class.join_recursive do |query|
query.start_with(id: id).connect_by(parent_organization_id: :id)
end
and if I call #to_a
on that, I get the correct results. However, if I do any of these things:
relation.first
, relation.where(...)
, relation.limit(...)
, relation.order(..)
I get an error like this:
ActiveRecord::StatementInvalid: PG::ProtocolViolation: ERROR: bind message supplies 1 parameters, but prepared statement "a3" requires 2
Here's an example of the generated SQL when it fails to on relation.first
:
SELECT "organizations".* FROM "organizations"
INNER JOIN
(WITH RECURSIVE "organizations__recursive" AS
(SELECT "organizations"."id", "organizations"."parent_organization_id"
FROM "organizations"
WHERE "organizations"."deleted_at" IS NULL
AND "organizations"."id" = $1
UNION ALL
SELECT "organizations"."id",
"organizations"."parent_organization_id"
FROM "organizations"
INNER JOIN "organizations__recursive" ON "organizations__recursive"."parent_organization_id" = "organizations"."id" WHERE "organizations"."deleted_at" IS NULL ) SELECT "organizations__recursive".*
FROM "organizations__recursive") AS "organizations__recursive" ON "organizations"."id" = "organizations__recursive"."id"
WHERE "organizations"."deleted_at" IS NULL
ORDER BY "organizations"."id" ASC
LIMIT $2
Any ideas? Thanks in advance!
Is there an issue supporting it? Thanks
Schema:
create_table "products", force: :cascade do |t|
t.string "title"
t.text "description"
t.integer "parent" #Foreign key
t.boolean "leaf"
end
code:
Product.join_recursive { start_with(parent: nil).connect_by(id: :parent).order_siblings(:title) }
Error:
PG::UndefinedColumn: ERROR: column products.parent_id does not exist
This gem cannot work with Rails 6.1.0.rc1 at the moment. #32 fixes this.
is there anyway to convert results into nested hashes representing a tree of results?
Hi,
Thanks for writing this gem, it is very helpful and has saved me some headaches. Great work!
There was an issue in one of my apps today where I got a PG::ProtocolViolation
when trying to nest a recursive query in the where
clause of another query. There is a workaround, but here is a description of the problem:
# Query for the user's organization with all of its parent organizations recursively
organizations = Organization.join_recursive do |query|
query.start_with(id: current_user.organization.id).connect_by(parent_organization_id: :id)
end
# Query for all licenses that belong to these organization and belong to this app
licenses = License.where(organization_id: organizations, license_app_id: license_app.id)
This fails because the last where
clause has a $1
in the prepared statement, but the nested select
from the recursive join also has a $1
. Full example:
PG::ProtocolViolation: ERROR: bind message supplies 2 parameters, but prepared statement "a5" requires 1 : SELECT "licenses".* FROM "licenses" WHERE "licenses"."organization_id" IN (SELECT "organizations"."id" FROM "organizations" INNER JOIN (WITH RECURSIVE "organizations__recursive" AS ( SELECT "organizations"."id", "organizations"."parent_organization_id" FROM "organizations" WHERE "organizations"."id" = $1 UNION ALL SELECT "organizations"."id", "organizations"."parent_organization_id" FROM "organizations" INNER JOIN "organizations__recursive" ON "organizations__recursive"."parent_organization_id" = "organizations"."id" ) SELECT "organizations__recursive".* FROM "organizations__recursive") AS "organizations__recursive" ON "organizations"."id" = "organizations__recursive"."id") AND "licenses"."license_app_id" = $1
Notice that $1
appears twice. The solution was to do a separate query for the list of IDs like so:
licenses = License.where(organization_id: organizations.pluck(:id)).where(license_app_id: license_app.id)
Do you know of a way this can be fixed?
Thanks,
Justin
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.