Rails 3 ActiveRecord: UNION

Question

Is there any way to use MySQL UNION in Rails 3?

score 11 · Accepted Answer · answered Apr 07 '11 at 15:52

I think the only way you're going to get this to work by directly executing the query.

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

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

MyModel.find_by_sql("...")

score 5 · Answer 2 · answered Nov 30 '12 at 20:20

5

I found a neat hack using select . For example if you want to make a union between User and OtherUser .

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

this will generate this SQL

"SELECT id from other_users union select id FROM users "

answered Nov 30 '12 at 20:20

lesce

6,224
5
29
35

This what I called awesome, but just to warn some people that would make any mistake like what I did before. By default, when do with pure SQL it should be `(SELECT id FROM other_users) UNION (SELECT id FROM users);` when take it to Rails, just don't write like `User.select("(id from other_users) UNION (select id)")` because it will generate `SELECT (id from other_users) UNION (SELECT id) FROM users` Just saying – ksugiarto Sep 10 '13 at 06:24

score 3 · Answer 3 · answered Apr 07 '11 at 15:52

3

Model.find_by_sql("your union query")

answered Apr 07 '11 at 15:52

Nick

1,315
9
16

score 0 · Answer 4 · answered Sep 07 '12 at 04:05

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

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

Meanwhile, I have written an small hack with which you'll be able to create simple union queries and do some filters with DISTINCT, ORDER BY and LIMIT:

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

Rails 3 ActiveRecord: UNION

4 Answers4

Linked