I've written a couple of complex queries (at least to me) with Ruby on Rail's query interface:
watched_news_posts = Post.joins(:news => :watched).where(:watched => {:user_id => id})
watched_topic_posts = Post.joins(:post_topic_relationships => {:topic => :watched}).where(:watched => {:user_id => id})
Both of these queries work fine by themselves. Both return Post objects. I would like to combine these posts into a single ActiveRelation. Since there could be hundreds of thousands of posts at some point, this needs to be done at the database level. If it were a MySQL query, I could simply user the UNION
operator. Does anybody know if I can do something similar with RoR's query interface?
Arguably, this improves readability, but not necessarily performance:
This method returns an ActiveRecord::Relation, so you could call it like this:
There is an active_record_union gem. Might be helpful
https://github.com/brianhempel/active_record_union
SELECT "posts".* FROM ( SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = 1 UNION SELECT "posts".* FROM "posts" WHERE (published_at < '2014-07-19 16:04:21.918366') ) posts
Elliot Nelson answered good, except the case where some of the relations are empty. I would do something like that:
end
In a similar case I summed two arrays and used
Kaminari:paginate_array()
. Very nice and working solution. I was unable to usewhere()
, because I need to sum two results with differentorder()
on the same table.You could also use Brian Hempel's active_record_union gem that extends
ActiveRecord
with anunion
method for scopes.Your query would be like this:
Hopefully this will be eventually merged into
ActiveRecord
some day.Could you use an OR instead of a UNION?
Then you could do something like:
(Since you are joins the watched table twice I'm not too sure what the names of the tables will be for the query)
Since there are a lot of joins, it might also be quite heavy on the database, but it might be able to be optimized.