开发者

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

开发者 https://www.devze.com 2023-02-19 02:48 出处:网络
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

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.

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:

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.

EDIT #2: In other words, the merge method seems to string the queri开发者_JS百科es 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.


with your initial code:

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

  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


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

  • ActiveRecordUnion gem.

    Install gem: gem install active_record_union and 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
    


All you need to do is sum the queries:

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

# your final result
result = result1 + result2


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号