Ruby-on-rails ActiveRecord 查询联合

ActiveRecord Query Union

提问by LandonSchropp

I've written a couple of complex queries (at least to me) with Ruby on Rail's query interface:

我已经用 Ruby on Rail 的查询接口编写了几个复杂的查询（至少对我而言）：

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 UNIONoperator. Does anybody know if I can do something similar with RoR's query interface?

这两个查询本身都可以正常工作。两者都返回 Post 对象。我想将这些帖子合并为一个 ActiveRelation。由于在某个时候可能有数十万个帖子，因此需要在数据库级别完成。如果是 MySQL 查询，我可以简单地使用UNION操作符。有人知道我是否可以用 RoR 的查询界面做类似的事情吗？

回答by Tim Lowrimore

Here's a quick little module I wrote that allows you to UNION multiple scopes. It also returns the results as an instance of ActiveRecord::Relation.

这是我编写的一个快速小模块，它允许您联合多个范围。它还将结果作为 ActiveRecord::Relation 的实例返回。

module ActiveRecord::UnionScope
  def self.included(base)
    base.send :extend, ClassMethods
  end

  module ClassMethods
    def union_scope(*scopes)
      id_column = "#{table_name}.id"
      sub_query = scopes.map { |s| s.select(id_column).to_sql }.join(" UNION ")
      where "#{id_column} IN (#{sub_query})"
    end
  end
end

Here's the gist: https://gist.github.com/tlowrimore/5162327

这是要点：https: //gist.github.com/tlowrimore/5162327

Edit:

编辑：

As requested, here's an example of how UnionScope works:

根据要求，以下是 UnionScope 工作原理的示例：

class Property < ActiveRecord::Base
  include ActiveRecord::UnionScope

  # some silly, contrived scopes
  scope :active_nearby,     -> { where(active: true).where('distance <= 25') }
  scope :inactive_distant,  -> { where(active: false).where('distance >= 200') }

  # A union of the aforementioned scopes
  scope :active_near_and_inactive_distant, -> { union_scope(active_nearby, inactive_distant) }
end

回答by Elliot Nelson

I also have encountered this problem, and now my go-to strategy is to generate SQL (by hand or using to_sqlon an existing scope) and then stick it in the fromclause. I can't guarantee it's any more efficient than your accepted method, but it's relatively easy on the eyes and gives you a normal ARel object back.

我也遇到过这个问题，现在我的首选策略是生成 SQL（手动或to_sql在现有范围上使用），然后将其粘贴在from子句中。我不能保证它比您接受的方法更有效，但它对眼睛来说相对容易，并为您提供一个正常的 ARel 对象。

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})

Post.from("(#{watched_news_posts.to_sql} UNION #{watched_topic_posts.to_sql}) AS posts")

You can do this with two different models as well, but you need to make sure they both "look the same" inside the UNION -- you can use selecton both queries to make sure they will produce the same columns.

您也可以使用两个不同的模型来执行此操作，但是您需要确保它们在 UNION 中“看起来相同”——您可以select在两个查询中使用以确保它们将生成相同的列。

topics = Topic.select('user_id AS author_id, description AS body, created_at')
comments = Comment.select('author_id, body, created_at')

Comment.from("(#{comments.to_sql} UNION #{topics.to_sql}) AS comments")

回答by LandonSchropp

Based on Olives' answer, I did come up with another solution to this problem. It feels a little bit like a hack, but it returns an instance of ActiveRelation, which is what I was after in the first place.

根据 Olives 的回答，我确实想出了另一个解决此问题的方法。感觉有点像 hack，但它返回一个 的实例ActiveRelation，这就是我最初想要的。

Post.where('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" WHERE "watched"."user_id" = ?
      )
      OR posts.id IN
      (
        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" WHERE "watched"."user_id" = ?
      )', id, id)

I'd still appreciate it if anybody has any suggestions to optimize this or improve the performance, because it's essentially executing three queries and feels a little redundant.

如果有人有任何建议来优化它或提高性能，我仍然会很感激，因为它本质上是在执行三个查询并且感觉有点多余。

回答by dgilperez

You could also use Brian Hempel's active_record_uniongem that extends ActiveRecordwith an unionmethod for scopes.

您还可以使用Brian Hempel的active_record_uniongem，它扩展ActiveRecord了一个union范围方法。

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 ActiveRecordsome day.

希望这最终会合并到ActiveRecord某一天。

回答by Richard Wan

How about...

怎么样...

def union(scope1, scope2)
  ids = scope1.pluck(:id) + scope2.pluck(:id)
  where(id: ids.uniq)
end

回答by Olives

Could you use an OR instead of a UNION?

你能用 OR 代替 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.

由于有很多连接，它可能对数据库也很重，但它可能可以优化。

回答by richardsun

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:

这个方法返回一个 ActiveRecord::Relation，所以你可以这样调用它：

my_posts.order("watched_item_type, post.id DESC")

回答by Mike Lyubarskyy

There is an active_record_union gem. Might be helpful

有一个 active_record_union gem。可能会有所帮助

https://github.com/brianhempel/active_record_union

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:

使用 ActiveRecordUnion，我们可以：

当前用户的（草稿）帖子和任何人发布的所有帖子， current_user.posts.union(Post.published)相当于以下 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

回答by Dmitry Polushkin

Less problems and easier to follow:

问题更少，更容易遵循：

    def union_scope(*scopes)
      scopes[1..-1].inject(where(id: scopes.first)) { |all, scope| all.or(where(id: scope)) }
    end

So in the end:

所以最后：

union_scope(watched_news_posts, watched_topic_posts)

回答by Jeffrey Alan Lee

I would just run the two queries you need and combine the arrays of records that are returned:

我只会运行您需要的两个查询并组合返回的记录数组：

@posts = watched_news_posts + watched_topics_posts

Or, at the least test it out. Do you think the array combination in ruby will be far too slow? Looking at the suggested queries to get around the problem, I'm not convinced that there will be that significant of a performance difference.

或者，至少测试一下。你认为 ruby​​ 中的数组组合会太慢吗？查看解决该问题的建议查询，我不相信会有那么大的性能差异。