Rails 3 ActiveRecord: UNION

Question

Can I use MySQL UNION in Rails 3?

Answer 1

I think the only way to get this to work is by directly executing the request.

ActiveRecord::Base.connection.execute("SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10)") 

This returns an ActiveRecord result set. If you want the results wrapped in a model to do something like this:

 MyModel.find_by_sql("...") 

Answer 2

I found a neat hack using select. For example, if you want to create a connection between User and OtherUser.

 User.select('id from other_users union select id') 

it will generate this SQL

 "SELECT id from other_users union select id FROM users " 

Answer 3

 Model.find_by_sql("your union query") 

Answer 4

As you can read in this thread, there are people working on a better solution for creating join queries in Rails:

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

Meanwhile, I wrote a small hack with which you can create simple combined queries and do some filters with DISTINCT, ORDER BY and LIMIT:

http://coderwall.com/p/9hohaa