ActiveRecord Query Union

2019-01-03 13:22发布

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?

11条回答
我命由我不由天
2楼-- · 2019-01-03 13:52

Arguably, this improves readability, but not necessarily performance:

def my_posts
  Post.where <<-SQL, self.id, self.id
    posts.id IN 
    (SELECT post_topic_relationships.post_id FROM post_topic_relationships
    INNER JOIN watched ON watched.watched_item_id = post_topic_relationships.topic_id 
    AND watched.watched_item_type = "Topic" 
    AND watched.user_id = ?
    UNION
    SELECT posts.id FROM posts 
    INNER JOIN news ON news.id = posts.news_id 
    INNER JOIN watched ON watched.watched_item_id = news.id 
    AND watched.watched_item_type = "News" 
    AND watched.user_id = ?)
  SQL
end

This method returns an ActiveRecord::Relation, so you could call it like this:

my_posts.order("watched_item_type, post.id DESC")
查看更多
戒情不戒烟
3楼-- · 2019-01-03 13:53

There is an active_record_union gem. Might be helpful

https://github.com/brianhempel/active_record_union

With ActiveRecordUnion, we can do:

the current user's (draft) posts and all published posts from anyone current_user.posts.union(Post.published) Which is equivalent to the following SQL:

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

查看更多
Ridiculous、
4楼-- · 2019-01-03 13:53

Elliot Nelson answered good, except the case where some of the relations are empty. I would do something like that:

def union_2_relations(relation1,relation2)
sql = ""
if relation1.any? && relation2.any?
  sql = "(#{relation1.to_sql}) UNION (#{relation2.to_sql}) as #{relation1.klass.table_name}"
elsif relation1.any?
  sql = relation1.to_sql
elsif relation2.any?
  sql = relation2.to_sql
end
relation1.klass.from(sql)

end

查看更多
Ridiculous、
5楼-- · 2019-01-03 13:57

In a similar case I summed two arrays and used Kaminari:paginate_array(). Very nice and working solution. I was unable to use where(), because I need to sum two results with different order() on the same table.

查看更多
做自己的国王
6楼-- · 2019-01-03 14:03

You could also use Brian Hempel's active_record_union gem that extends ActiveRecord with an union method for scopes.

Your query would be like this:

Post.joins(:news => :watched).
  where(:watched => {:user_id => id}).
  union(Post.joins(:post_topic_relationships => {:topic => :watched}
    .where(:watched => {:user_id => id}))

Hopefully this will be eventually merged into ActiveRecord some day.

查看更多
The star\"
7楼-- · 2019-01-03 14:13

Could you use an OR instead of a UNION?

Then you could do something like:

Post.joins(:news => :watched, :post_topic_relationships => {:topic => :watched})
.where("watched.user_id = :id OR topic_watched.user_id = :id", :id => id)

(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.

查看更多
登录 后发表回答