[Feature Proposal] Common Table Expression support

rubyonrails-core
1

I’d like to be able to use my ActiveRecord scopes in a CTE for another scope.

Proposed syntax

class Post < ActiveRecord::Base has_many :comments end

class Comment < ActiveRecord::Base belongs_to :post end

Post.with(Comment.select(:post_id), name: :post_ids, recursive: false).where(id: {post_ids: :post_id})

Would a patch supporting this syntax be accepted?

Thanks,

Daniel Heath

2

Thank you for the feature proposal. It doesn’t seem to be difficult to implement this as a gem given most of the APIs you need in able to implement something like this are public.

What do you think about implementing this first as a gem and see if there are adoption of this feature before proposing adding to the rails framework?

Thanks.

3

postgres_ext | RubyGems.org | your community gem host implements this for rails up to version 4, and has ~2 million downloads.

GitHub - kmurph73/ctes_in_my_pg: ActiveRecord support for PostgreSQL's CTEs ports the code to rails 5.

There are also numerous blog posts around documenting how to work around the issue by dropping down to raw Arel or string interpolation.

eg:

CockroachDB + ActiveRecord (and Ruby on Rails!) also mentions that ActiveRecord uses CTEs internally.

Would you agree that’s a suitable level of usage to justify including this SQL-99 feature in core ActiveRecord? Or is the complexity likely to be too much for the number of users affected?

Thanks,

Daniel Heath

4

While we’re on the subject, I have a PR to get postgres_ext working with Rails 5.2, but need help from someone that has experience with Arel’s internals.

https://github.com/danmcclain/postgres_ext/pull/2

Though more broadly I’m of the opinion that most of it should be inlined into Rails, and would be happy to work on that effort if it’s welcome.

5

Cool! I think the hard work is already done. But I still find it hard to justify this in the framework. It is a feature that is out of Rails for more than 10 years and I personally never needed it working in many applications through the years so I’m not sure if I want to add that complexity to the framework and maintain this feature.

We are planing to expose arel as public API sometime in the future so maybe that would be enough to allow people who wants to use CTE to be able to use in their application.

Rafael Mendonça França http://twitter.com/rafaelfranca https://github.com/rafaelfranca

1 Like
6

IME, which subset of features you use largely depends on the industry sector you’re in. There are many rails features I haven’t used in the past decade of working on rails apps, but they are important to some users.

CTEs are primarily useful when dealing with much larger databases and complex schemas. There are very few rails codebases old enough to have developed a highly-complex schema because rails isn’t very old. However, as more rails applications become large and long-established, this feature will affect more developers.

As a result, I think that at some point this will become an important feature. Whether it belongs in core right now hinges (imo) primarily on how much complexity the implementation adds.

I’ve had a read through the postgres_ext functionality to add CTEs; it’s 80 lines of very straightforward code which should work unmodified for mysql 8+, sqlite 3.8.3+, oracle, frontbase, IBM DB, and MS SQL server.

There’ll be a bit of extra work to make Relation::Merger#merge work correctly - alongside merge_multi_values and merge_clauses etc, we’d have to add merge_ctes.

Relation::QueryMethods#build_arel and Relation::Merger#merge are the only places anyone not working on CTEs would notice that this change exists.

Thanks,

Daniel Heath

7

IME, which subset of features you use largely depends on the industry sector you’re in. There are many rails features I haven’t used in the past decade of working on rails apps, but they are important to some users.

CTEs are primarily useful when dealing with much larger databases and complex schemas. There are very few rails codebases old enough to have developed a highly-complex schema because rails isn’t very old.

Yes, that is true. It is a good coincidence that I work in one of those old enough codebases, maybe the oldest in active development.

However, as more rails applications become large and long-established, this feature will affect more developers.

I still doubt this. Like I said, working in a large scale application with a complex schema I didn’t needthis feature so far, so I think the size and complexity of the application doesn’t matter.

As a result, I think that at some point this will become an important feature. Whether it belongs in core right now hinges (imo) primarily on how much complexity the implementation adds.

Agree, but complexity is not only the number of lines the implementation requires.

To me the primary question that I need to answer is: do I want to maintain this feature for the next years given it can be implemented outside of the framework?

My answer right now is: no.

I’d prefer to spend the energy to maintain this code in other things like working to expose arel to public API, that would not only make CTE easier but also other kinds of complex queries.

2 Likes
8

Just wanted to let you know I opened PR for CTE support. Since it was not yet merged I also extracted it to gem GitHub - vlado/activerecord-cte: Brings Common Table Expressions support to ActiveRecord and makes it super easy to build and chain complex CTE queries

2 Likes
9

(Slightly off topic but its related to Arel)

@rafaelfranca you mentioned polishing/publishing arel so we can make it a public API, that would be awesome! I use a number of the API but seeing as it’s private and subject to breaking what can be done to push that forward?

I mention this as its related to one of the PR’s discussing arel recently re: Remove useless `eq_all`, `eq_any`, etc by kamipo · Pull Request #39150 · rails/rails · GitHub