How do you combine multiple Rails SQL queries into a single query in rails?

Question

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.

PinnyM · Accepted Answer · 2014-01-10 05:04:48Z

4

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)

answered Jan 10, 2014 at 5:04

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Kim Over a year ago

Awesome, this resulted in 50% performance gain for the query. thanks

steakchaser · Accepted Answer · 2014-01-10 05:11:32Z

0

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

answered Jan 10, 2014 at 5:11

steakchaser

5,2491 gold badge28 silver badges35 bronze badges

3 Comments

PinnyM Over a year ago

Since you aren't instantiating the models until after the query is built, this is not going to result in the desired sort order...

steakchaser Over a year ago

Why? ordered is an active record scope and the | is a new operator that combines scopes with a SQL OR statement. Maybe you are confusing this with the standard Array union operator?

PinnyM Over a year ago

Yes, but the ordered scope doesn't force the same ordering as desired. The OP's code resulted in the records ordered by created in the past week, then past month, then 90 days, then everything else. Your scope orders all of them just by the vote count (not sure where you took that from).

Collectives™ on Stack Overflow

How do you combine multiple Rails SQL queries into a single query in rails?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related