Adds Common Table Expression support to ActiveRecord (Rails).
It adds .with query method and makes it super easy to build and chain complex CTE queries. Let's explain it using simple example.
Post.with(
posts_with_comments: Post.where("comments_count > ?", 0),
posts_with_tags: Post.where("tags_count > ?", 0)
)Will return ActiveRecord::Relation and will generate SQL like this.
WITH posts_with_comments AS (
SELECT * FROM posts WHERE (comments_count > 0)
), posts_with_tags AS (
SELECT * FROM posts WHERE (tags_count > 0)
)
SELECT * FROM postsPlease note that this creates the expressions but is not using them yet. See Taking it further for more info.
Without this gem you would need to use Arel directly.
post_with_comments_table = Arel::Table.new(:posts_with_comments)
post_with_comments_expression = Post.arel_table.where(posts_with_comments_table[:comments_count].gt(0))
post_with_tags_table = Arel::Table.new(:posts_with_tags)
post_with_tags_expression = Post.arel_table.where(posts_with_tags_table[:tags_count].gt(0))
Post.all.arel.with([
Arel::Node::As.new(posts_with_comments_table, posts_with_comments_expression),
Arel::Node::As.new(posts_with_tags_table, posts_with_tags_expression)
])Instead of Arel you could also pass raw SQL string but either way you will NOT get ActiveRecord::Relation and
you will not be able to chain them further, cache them easily, call count and other aggregates on them, ...
Add this line to your application's Gemfile:
gem "activerecord-cte"And then execute:
$ bundle
Or install it yourself as:
$ gem install activerecord-cte
Easiest way to build the WITH query is to pass the Hash where keys are used as names of the tables and values are used to
generate the SQL. You can pass ActiveRecord::Relation, String or Arel::Nodes::As node.
Post.with(
posts_with_comments: Post.where("comments_count > ?", 0),
posts_with_tags: "SELECT * FROM posts WHERE tags_count > 0"
)
# WITH posts_with_comments AS (
# SELECT * FROM posts WHERE (comments_count > 0)
# ), posts_with_tags AS (
# SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM postsYou can also pass complete CTE as a single SQL string
Post.with("posts_with_tags AS (SELECT * FROM posts WHERE tags_count > 0)")
# WITH posts_with_tags AS (
# SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM postsIf you already have Arel::Node::As node you can just pass it as is
posts_table = Arel::Table.new(:posts)
cte_table = Arel::Table.new(:posts_with_tags)
cte_select = posts_table.project(Arel.star).where(posts_table[:tags_count].gt(100))
as = Arel::Nodes::As.new(cte_table, cte_select)
Post.with(as)
# WITH posts_with_tags AS (
# SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM postsYou can also pass array of Arel Nodes
posts_table = Arel::Table.new(:posts)
with_tags_table = Arel::Table.new(:posts_with_tags)
with_tags_select = posts_table.project(Arel.star).where(posts_table[:tags_count].gt(100))
as_posts_with_tags = Arel::Nodes::As.new(with_tags_table, with_tags_select)
with_comments_table = Arel::Table.new(:posts_with_comments)
with_comments_select = posts_table.project(Arel.star).where(posts_table[:comments_count].gt(100))
as_posts_with_comments = Arel::Nodes::As.new(with_comments_table, with_comments_select)
Post.with([as_posts_with_tags, as_posts_with_comments])
# WITH posts_with_comments AS (
# SELECT * FROM posts WHERE (comments_count > 0)
# ), posts_with_tags AS (
# SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM postsAs you probably noticed from the examples above .with is only a half of the equation. Once we have CTE results we also need to do the select on them somehow.
You can write custom FROM that will alias your CTE table to the table ActiveRecord expects by default (Post -> posts) for example.
Post
.with(posts_with_tags: "SELECT * FROM posts WHERE tags_count > 0")
.from("posts_with_tags AS posts")
# WITH posts_with_tags AS (
# SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM posts_with_tags AS posts
Post
.with(posts_with_tags: "SELECT * FROM posts WHERE tags_count > 0")
.from("posts_with_tags AS posts")
.count
# WITH posts_with_tags AS (
# SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT COUNT(*) FROM posts_with_tags AS postsAnother option would be to use join
Post
.with(posts_with_tags: "SELECT * FROM posts WHERE tags_count > 0")
.joins("JOIN posts_with_tags ON posts_with_tags.id = posts.id")
# WITH posts_with_tags AS (
# SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM posts JOIN posts_with_tags ON posts_with_tags.id = posts.idThere are other options also but that heavily depends on your use case and is out of scope of this README :)
Recursive queries are also supported Post.with(:recursive, popular_posts: "... union to get popular posts ...").
posts = Arel::Table.new(:posts)
top_posts = Arel::Table.new(:top_posts)
anchor_term = posts.project(posts[:id]).where(posts[:comments_count].gt(1))
recursive_term = posts.project(posts[:id]).join(top_posts).on(posts[:id].eq(top_posts[:id]))
Post.with(:recursive, top_posts: anchor_term.union(recursive_term)).from("top_posts AS posts")
# WITH RECURSIVE "popular_posts" AS (
# SELECT "posts"."id" FROM "posts" WHERE "posts"."comments_count" > 0 UNION SELECT "posts"."id" FROM "posts" INNER JOIN "popular_posts" ON "posts"."id" = "popular_posts"."id" ) SELECT "posts".* FROM popular_posts AS postsPlease note that update_all and delete_all methods are not implemented and will not work as expected. I tried to implement them and was succesfull
but the "monkey patching" level was so high that I decided not to keep the implementation.
If my Pull Request gets merged adding them to Rails direcly will be easy and since I did not need them yet I decided to wait a bit :)
After checking out the repo, run bin/setup to install dependencies.
bundle exec rubocop
To run the tests using SQLite adapter and latest version on Rails run
bundle exec rake test
GitHub Actions will run the test matrix with multiple ActiveRecord versions and database adapters. You can also run the matrix locally with
bundle exec rake test:matrix
This will build Docker image with all dependencies and run all tests in it. See bin/test for more info.
You can run bin/console for an interactive prompt that will allow you to experiment.
To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.
Bug reports and pull requests are welcome on GitHub at https://github.com/vlado/activerecord-cte. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.
The gem is available as open source under the terms of the MIT License.
Everyone interacting in the Activerecord::Cte projectβs codebases, issue trackers, chat rooms and mailing lists is expected to follow the code of conduct.