Skip to content
Go to file
This branch is 2 commits ahead, 45 commits behind brianhempel:master.

Latest commit


Git stats


Failed to load latest commit information.
Latest commit message
Commit time


Gem Version Build Status

Use unions on ActiveRecord scopes without ugliness.

If you find yourself writing pluck(:id) and then feeding that into another query, you may be able to reduce the number of database requests by using a nested query or a UNION without writing crazy JOIN statements.


Add this line to your application's Gemfile:

gem 'active_record_union', github: 'mackaber/active_record_union'

And then execute:

$ bundle

Or install it yourself as:

$ git clone
$ cd active_record_union && gem build active_record_union.gemspec
$ gem install active_record_union-1.0.10b.gem


ActiveRecordUnion adds a union method to ActiveRecord::Relation so we can easily gather together queries on mutiple scopes.

Consider some users with posts:

class User < ActiveRecord::Base
  has_many :posts

class Post < ActiveRecord::Base
  belongs_to :user

  scope :published, -> { where("published_at < ?", }

With ActiveRecordUnion, we can do:

# the current user's (draft) posts and all published posts from anyone

Which is equivalent to the following SQL: [1]

SELECT "posts".* FROM (
  SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" = ?
  SELECT "posts".* FROM "posts"  WHERE (published_at < '2014-07-19 16:04:21.918366')
) posts

Because the union method returns another ActiveRecord::Relation, we can run further queries on the union.

current_user.posts.union(Post.published).where(id: [6, 7])
SELECT "posts".* FROM (
  SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" = ?
  SELECT "posts".* FROM "posts"  WHERE (published_at < '2014-07-19 16:06:04.460771')
) posts  WHERE "posts"."id" IN (6, 7)

The union method can also accepts anything that where does.

current_user.posts.union("published_at < ?",
# equivalent to...
current_user.posts.union(Post.where("published_at < ?",

We can also chain union calls to UNION more than two scopes, though the UNIONs will be nested which may not be the prettiest SQL.

# equivalent to...
[user_1.posts, user_2.posts, Post.published].inject(:union)
SELECT "posts".* FROM (
  SELECT "posts".* FROM (
    SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" = ?
    SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" = ?
  ) posts
  SELECT "posts".* FROM "posts"  WHERE (published_at < '2014-07-19 16:12:45.882648')
) posts

#Mackaber's addition: I noticed brianhempel, used the table name for the query alias when the union is made, however, for my projects I had to change it, because I need to write a query to select the values from the table and the subquery, so I added a little hack to separate it from the table name using union(relation_or_where_arg,separated).

# the current user's (draft) posts and all published posts from anyone
SELECT "posts".* FROM (
  SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" = ?
  SELECT "posts".* FROM "posts"  WHERE (published_at < '2014-07-19 16:04:21.918366')
) posts_union, posts

[1] Note: the ? in the SQL is bound to the correct value when ActiveRecord executes the query. Also, the SQL examples here were generated for a SQLite database. The syntax generated for other databases may vary slightly.


There's a couple things to be aware of when using ActiveRecordUnion:

  1. ActiveRecordUnion with raise an error if you try to UNION any relations that do any preloading/eager-loading. There's no sensible way to do the preloading in the subselects. If enough people complain maybe we can change ActiveRecordUnion to let the queries run anyway but without preloading any records.
  2. There's no easy way to get SQLite to allow ORDER BY in the UNION subselects. If you get a syntax error, you can either write my_relation.reorder(nil).union(other.reorder(nil)) or switch to Postgres.

Another nifty way to reduce extra queries

ActiveRecord already supports turning scopes into nested queries in WHERE clauses. The nested relation defaults to selecting id by default.

For example, if a user has_and_belongs_to_many :favorited_posts, we can quickly find which of the current user's posts are liked by a certain other user.

current_user.posts.where(id: other_user.favorited_posts)
SELECT "posts".* FROM "posts"
  WHERE "posts"."user_id" = ?
  AND "posts"."id" IN (
    SELECT "posts"."id"
      FROM "posts" INNER JOIN "user_favorited_posts" ON "posts"."id" = "user_favorited_posts"."post_id"
      WHERE "user_favorited_posts"."user_id" = ?

If we want to select something other than id, we use select to specify. The following is equivalent to the above, but the query is done against the join table.

current_user.posts.where(id: UserFavoritedPost.where(user_id:
SELECT "posts".* FROM "posts"
  WHERE "posts"."user_id" = ?
  AND "posts"."id" IN (
    SELECT "user_favorited_posts"."post_id"
      FROM "user_favorited_posts"
      WHERE "user_favorited_posts"."user_id" = 2

(The above example is illustrative only. It might be better with a JOIN.)

State of the Union in ActiveRecord

Why does this gem exist?

Right now in ActiveRecord, if we call scope.union we get an Arel::Nodes::Union object instead of an ActiveRecord::Relation.

We could call to_sql on the Arel object and then use find_by_sql, but that's not super clean and if the original scopes included an association, then the to_sql may produce a query with values that need to be bound (represented by ?s in the SQL) and we have to provide those ourselves. (E.g. user.posts.to_sql produces SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?.)

While ActiveRecord may eventually have the ability to cleanly perform UNIONs, it's currently stalled. If you're interested, the relevant URLs as of July 2014 are: and and

This is a gem not a Rails pull request because the standard of code quality for a PR is a bit higher, and we'd have to wait for the PR to be merged and relased to use UNIONs. That said, the code here is fairly clean and it may end up in a PR sometime.


1.0.1 - Sept 2, 2014 - Allow ORDER BY in UNION subselects for databases that support it (not SQLite).

1.0.0 - July 24, 2014 - Initial release.


ActiveRecordUnion is dedicated to the public domain by its author, Brian Hempel. No rights are reserved. No restrictions are placed on the use of ActiveRecordUnion. That freedom also means, of course, that no warrenty of fitness is claimed; use ActiveRecordUnion at your own risk.

This public domain dedication follows the the CC0 1.0 at


  1. Fork it ( )
  2. Create your feature branch (git checkout -b my-new-feature)
  3. Run the tests with rspec
  4. There is also a bin/console command to load up a REPL for playing around
  5. Commit your changes (git commit -am 'Add some feature')
  6. Push to the branch (git push origin my-new-feature)
  7. Create a new Pull Request


UNIONs in ActiveRecord! Adds a proper union method to ActiveRecord::Relation.




No packages published


You can’t perform that action at this time.