-
Notifications
You must be signed in to change notification settings - Fork 9
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Recursive + 7.1 #16
Comments
Very good question @nhorton. I didn't try it with edge rails but I suppose it would override original method and everything would work. Will need to test it and add note to Readme when I catch some time. Feel free to do it and open PR if you gonna have time before me :) Also, recursive support was dropped from rails/rails#37944 case we couldn't agree on the right API for it. I never needed / used recursive CTEs in "real life" so I would be interested if you can provide some examples. |
I use recursion in The Brick to rapidly calculate inherited permissions. (This part of the gem hasn't been publicly released yet -- it's currently being tested by a limited number of users.) Here's the monkey patch I currently use on Rails 7.1 in order to add this necessary recursive support:
With that in place, you can run a recursive CTE by providing a name for the common table and then two additional parts -- an anchor query and a recursive query, like this:
|
Thanks to both of you. I will open a README PR if I get a chance to run against edge. @vlado - my recursive use case is that we have a directed acyclic graph that we create in our database and need to send it to serialize it with some requests, so populating the graph in one query is really valuable. @lorint - I have looked at The Brick a couple times and love how you think. We should chat sometime - much of what my company does (unsupervised.com) is AI data analytics and we do some interesting stuff with exploring schemas too. The Brick is cool work. |
Sounds cool! Have filled out the "let's nerd out together" contact form on your site -- I'm originally from the US but now living in UK. Feel free to email or call at your convenience -- I'm usually up until around 4pm Pacific time. |
Sweet! I am on vacation this week but will ping when I am back! |
We would also significantly benefit from being able to write recursive CTEs in ActiveRecord, so we are interested in having this feature supported in a maintained gem, or even better, in ActiveRecord itself. @vlado @lorint is either of you interested in working to get this in ActiveRecord? @lorint your monkey-patch has been a great starting point for us! I just wanted to check whether you're fine with us using it under MIT or AGPLv3 license? Also, I did run into an issue with it losing parameter binds information, which I solved by not using module ActiveRecord
module QueryMethods
def with_recursive(as, anchor, recursive)
@is_recursive = true
common_table = anchor.arel.union(:all, recursive.arel)
from(as).with(as => common_table)
end
private
def build_with(arel)
return if with_values.empty?
with_statements = with_values.map do |with_value|
raise ArgumentError, "Unsupported argument type: #{with_value} #{with_value.class}" unless with_value.is_a?(Hash)
build_with_value_from_hash(with_value)
end
# Was: arel.with(with_statements)
@is_recursive ? arel.with(:recursive, with_statements) : arel.with(with_statements)
end
def build_with_value_from_hash(hash)
hash.map do |name, value|
expression =
case value
when Arel::Nodes::SqlLiteral then Arel::Nodes::Grouping.new(value)
when ActiveRecord::Relation then value.arel
# Was: when Arel::SelectManager then value
when Arel::SelectManager, Arel::Nodes::UnionAll then value
else
raise ArgumentError, "Unsupported argument type: `#{value}` #{value.class}"
end
Arel::Nodes::TableAlias.new(expression, name)
end
end
end
end |
@ClearlyClaire I'm interested in adding this to ActiveRecord, I have it on my radar but it is hard to find time :) It will be easiest I think if added to the gem first so I will prioritise that. |
I ended up giving a try to having the feature in ActiveRecord itself: rails/rails#51601 |
You bet -- and I see that this has progressed quite a bit from a week ago! Grateful to see the PR for recursive CTE stuff to make it into Rails. |
Hi,
First off - thanks a ton for writing this and fighting to get most of it into ActiveRecord!
The "issue" in this report is that there is not a roadmap-y mention in the README on how this is going to play with 7.1. When 7.1. releases, is it expected that this gem will work, or do we need to pull the gem out when it releases?
I am particularly interested as we need recursive CTEs, and I was hoping we could use this gem and that post-7.1, it would remain with just the recursive stuff added to base activerecord.
Thanks again for the great work here!
The text was updated successfully, but these errors were encountered: