ActiveRecord UNION hack

There are some times you need to merge the results of two or more queries in Rails. A typical situation for doing that is when you are implementing a timeline where you fetch events from yourself and also from your friends:

class Profile < ActiveRecord::Base
  has_many :events
  has_many :friends
  has_many :friend_events, :through => :friends, :source => :events

  def my_timeline
    events + friend_events
  end
end

That would work unless, for example, you share some events with your friends or you need to sort events by creation time and then take the last ones only. Removing duplicates or sorting and limiting results into ruby world would be terribly slow when number of events grow, so it would be awesome to be able to delegate this work to our optimized databases thanks to SQL UNION operations.

The bad news are that ActiveRecord doesn't yet support this kind of operation, and the good ones are that there is people already working to bring this capability to our beloved Rails as you can see on this thread:

https://github.com/rails/arel/pull/118

While they find an awesome solution, I've wrote this mini-module to pretend my Models can do unions:

module UnionHack
  def union(relations, opts={})
    query = 'SELECT '+ (opts[:distinct] ? 'DISTINCT ' : '' ) +'* FROM ((' + relations.map { |r| r.ast.to_sql }.join(') UNION (') + ')) AS t'
    query << " ORDER BY #{opts[:order]}" if opts[:order]
    query << " LIMIT #{opts[:limit]}" if opts[:limit]
    find_by_sql(query)
  end
end

Add it to your project, for example at lib folder and ensure you're loading it on your application.rb file:

# You'll need to add something like that
config.autoload_paths += %W(#{config.root}/lib)

Then extend your favourite model with the module:

class Profile < ActiveRecord::Base
  extend UnionHack
  ...
end

And you'll be able to do unions with unique records, sorted by any fields and limited in number of records on the next way:

...
def my_timeline
  Profile.union([events, friend_events], :distinct => true, :order => 'created_at DESC', :limit => 20)
end
...

For your convenience you can download the module file on the following Gist:
https://gist.github.com/3662866