ActiveRecord 查询联合 [英] ActiveRecord Query Union

问题描述

我已经使用 Ruby on Rail 的查询界面编写了几个复杂的查询(至少对我而言):

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

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

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?

推荐答案

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

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.

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

要点如下:https://gist.github.com/tlowrimore/5162327

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

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

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

这篇关于ActiveRecord 查询联合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！