如何将多个Rails SQL查询合并到Rails中的单个查询?

In my rails code, I need to run a query on a table based on a combination of the date of the records and votes that the record has received. I accomplish it like the following in rails:

在我的rails代码中，我需要基于记录收到的日期和投票的组合在表上运行查询。我在rails中完成如下:

if params[:sort_by] == "default"
    objs1 = class_name.where("created_at between '#{Date.today - 7}' and '#{Date.today}' and net_votes > 0").order("net_votes DESC")
    objs2 = class_name.where("created_at between '#{Date.today - 30}' and '#{Date.today - 7}' and net_votes > 0").order("net_votes DESC")
    objs3 = class_name.where("created_at between '#{Date.today - 90}' and '#{Date.today - 30}' and net_votes > 0").order("net_votes DESC")
    objs4 = class_name.where("created_at < '#{Date.today - 90}' and net_votes > 0").order("net_votes DESC")
    objs = objs1 + objs2 + objs3 + objs4

Efficiencies aside, I can't use pagination on the combined query result not to mention that the code is very ugly. what would be the right way to do this?

撇开效率不谈，我不能对组合查询结果使用分页，更不用说代码非常糟糕。这样做的正确方法是什么?

Thanks in advance.

提前谢谢。

2 个解决方案

#1

Use order for the sorting logic, not where:

排序逻辑使用order，而不是在以下地方:

order_by_sql = <<-SQL
CASE WHEN created_at between '#{Date.today - 7}' and '#{Date.today}' THEN 1
     WHEN created_at between '#{Date.today - 30}' and '#{Date.today - 7}' THEN 2
     WHEN created_at between '#{Date.today - 90}' and '#{Date.today - 30}' THEN 3
     ELSE 4
END
SQL

objs = class_name.where('net_votes > 0').order(order_by_sql)

#2

There's a couple of things you can do to make this more elegant and perform better:

有一些事情你可以做，使这更优雅和表现更好:

1) Encapsulate each of the conditions into a scope. For example, net_vtoes > 0 is reusable:

1)将每个条件封装到一个范围中。例如，net_vtoes > 0是可重用的:

def self.has_votes
  where("net_votes > 0")
end

def self.ordered 
  order("net_votes DESC")
end

def self.last_week
  where("created_at between '#{Date.today - 7}' and '#{Date.today}'")
end

2) Create a scope operator as suggested by Ryan Bates in this RailsCast to allow you to combine the where conditions in an OR fashion: http://railscasts.com/episodes/355-hacking-with-arel?view=asciicast. This will then let you build a statement like this:

2)按照Ryan Bates在本RailsCast中建议的那样创建一个范围操作符，以允许您以一种或方式组合where条件:http://railscasts.com/episode des/355-hacking- arel?这样你就可以建立这样的声明:

(MyClass.last_week | MyClass.last_month).has_votes.ordered

#1