Ruby-on-rails 如何合并来自同一模型的两个查询的结果？

How do I combine results from two queries on the same model?

提问by Preacher

I need to return exactly ten records for use in a view. I have a highly restrictive query I'd like to use, but I want a less restrictive query in place to fill in the results in case the first query doesn't yield ten results.

我需要返回正好十个记录以在视图中使用。我想使用一个限制性很强的查询，但我想要一个限制性较低的查询来填充结果，以防第一个查询没有产生 10 个结果。

Just playing around for a few minutes, and this is what I came up with, but it doesn't work. I think it doesn't work because merge is meant for combining queries on different models, but I could be wrong.

只是玩了几分钟，这就是我想出来的，但它不起作用。我认为它不起作用，因为合并是为了在不同模型上组合查询，但我可能是错的。

class Article < ActiveRecord::Base
...
  def self.listed_articles
    Article.published.order('created_at DESC').limit(25).where('listed = ?', true)
  end

  def self.rescue_articles
    Article.published.order('created_at DESC').where('listed != ?', true).limit(10)
  end

  def self.current
    Article.rescue_articles.merge(Article.listed_articles).limit(10)
  end
...
end

Looking in console, this forces the restrictions in listed_articles on the query in rescue_articles, showing something like:

在控制台中查看，这会强制对rescue_articles 中的查询施加listed_articles 中的限制，显示如下内容：

Article Load (0.2ms)  SELECT `articles`.* FROM `articles` WHERE (published = 1) AND (listed = 1) AND (listed != 1) ORDER BY created_at DESC LIMIT 4
Article Load (0.2ms)  SELECT `articles`.* FROM `articles` WHERE (published = 1) AND (listed = 1) AND (listed != 1) ORDER BY created_at DESC LIMIT 6 OFFSET 4

I'm sure there's some ridiculously easy method I'm missing in the documentation, but I haven't found it yet.

我确定文档中缺少一些非常简单的方法，但我还没有找到。

EDIT: What I want to do is return all the articles where listed is true out of the twenty-five most recent articles. If that doesn't get me ten articles, I'd like to add enough articles from the most recent articles where listed is not true to get my full ten articles.

编辑：我想要做的是返回最近 25 篇文章中列出的所有文章。如果这没有给我十篇文章，我想从最近列出的文章中添加足够多的文章，其中列出的文章不真实，以获得我的完整十篇文章。

EDIT #2: In other words, the merge method seems to string the queries together to make one long query instead of merging the results. I need the top ten results of the two queries (prioritizing listed articles), not one long query.

编辑 #2：换句话说，merge 方法似乎将查询串在一起以进行一个长查询，而不是合并结果。我需要两个查询的前十个结果（优先列出列出的文章），而不是一个长查询。

回答by Yule

with your initial code:

使用您的初始代码：

You can join two arrays using + then get first 10 results:

您可以使用 + 连接两个数组，然后获得前 10 个结果：

  def self.current
    (Article.listed_articles  +  Article.rescue_articles)[0..9]
  end

I suppose a really dirty way of doing it would be:

我想一种非常肮脏的做法是：

  def self.current
      oldest_accepted = Article.published.order('created_at DESC').limit(25).last
      Artcile.published.where(['created_at > ?', oldest_accepted.created_at]).order('listed DESC').limit(10)
  end

回答by Julián Esteban Salomón Torres

If you want an ActiveRecord::Relationobject instead of an Array, you can use:

如果你想要一个ActiveRecord::Relation对象而不是一个数组，你可以使用：

• ActiveRecordUnion gem.

  Install gem: gem install active_record_unionand use:

  def self.current
    Article.rescue_articles.union(Article.listed_articles).limit(10)
  end
  

• UnionScope module.

  Create module UnionScope (lib/active_record/union_scope.rb).

  module ActiveRecord::UnionScope
    def self.included(base)
      base.send :extend, ClassMethods
    end
  
    module ClassMethods
      def union_scope(*scopes)
        id_column = "#{table_name}.id"
         if (sub_query = scopes.reject { |sc| sc.count == 0 }.map { |s|   "(#{s.select(id_column).to_sql})" }.join(" UNION ")).present?
          where "#{id_column} IN (#{sub_query})"
        else
          none
        end
      end
    end
  end
  

  Then call it in your Article model.

  class Article < ActiveRecord::Base
    include ActiveRecord::UnionScope
    ...
    def self.current
      union_scope(Article.rescue_articles, Article.listed_articles).limit(10)
    end
    ...
  end
  

• ActiveRecordUnion 宝石。

  安装 gem:gem install active_record_union并使用：

  def self.current
    Article.rescue_articles.union(Article.listed_articles).limit(10)
  end
  

• UnionScope 模块。

  创建模块 UnionScope (lib/active_record/union_scope.rb)。

  module ActiveRecord::UnionScope
    def self.included(base)
      base.send :extend, ClassMethods
    end
  
    module ClassMethods
      def union_scope(*scopes)
        id_column = "#{table_name}.id"
         if (sub_query = scopes.reject { |sc| sc.count == 0 }.map { |s|   "(#{s.select(id_column).to_sql})" }.join(" UNION ")).present?
          where "#{id_column} IN (#{sub_query})"
        else
          none
        end
      end
    end
  end
  

  然后在您的文章模型中调用它。

  class Article < ActiveRecord::Base
    include ActiveRecord::UnionScope
    ...
    def self.current
      union_scope(Article.rescue_articles, Article.listed_articles).limit(10)
    end
    ...
  end
  

回答by Jyothu

All you need to do is sum the queries:

您需要做的就是对查询求和：

result1 = Model.where(condition)
result2 = Model.where(another_condition)

# your final result
result = result1 + result2

回答by Brian Glick

I think you can do all of this in one query:

我认为您可以在一个查询中完成所有这些操作：

 Article.published.order('listed ASC, created_at DESC').limit(10)

I may have the sort order wrong on the listed column, but in essence this should work. You'll get any listed items first, sorted by created_at DESC, then non-listed items.

我可能在列出的列上的排序顺序错误，但本质上这应该有效。您将首先获得任何列出的项目，按 created_at DESC 排序，然后是未列出的项目。