CODEWITHSUNDEEP
mysqlsqlruby-on-railspostgresqlrails-activerecord
Daniel Kim
Daniel Kim

Reputation: 65

How do you combine multiple Rails SQL queries into a single query in 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:

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.

Upvotes: 3

Views: 620

Answers (2)

steakchaser
steakchaser

Reputation: 5249

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:

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:

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

Upvotes: 0

PinnyM
PinnyM

Reputation: 35533

Use order for the sorting logic, not where:

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)

Upvotes: 4

Related Questions