CODEWITHSUNDEEP
ruby-on-rails
Augusto
Augusto

Reputation: 1150

Sorting a query on the sum of 2 columns

In rails 2.3.8 I'm trying to order a query having first the post that has the most comments AND votes.

I've tried to add a new method to the Post model as:

def interestingness
  self.comments_count + self.votes_count
end  

post_of_the_moment = find(:all,   :conditions => ["submitted_at BETWEEN ? and ?", from, to],
                          :order      => :interestingness,
                          :limit      => 10
                           )

but this code gives me a Unknown column error.

I also tried this

post_of_the_moment = find(:all,   :conditions => ["submitted_at BETWEEN ? and ?", from, to],
                          :order      => "SUM(comments_count+votes_count) DESC",
                          :limit      => 10
                           )

this doesn't give me errors but puts as result only 1 row that has 0 comments and 0 votes.

What am I doing wrong?

Thanks, Augusto

Upvotes: 1

Views: 656

Answers (2)

Dan Hill
Dan Hill

Reputation: 46

Try this:

  post_of_the_moment = find(:all, :select => '*, comments_count + votes_count AS total', :conditions => ["submitted_at BETWEEN ? and ?", from, to], :order  => "total DESC", :limit => 10)

I'd also see if you can optimize it be replacing the * above with only the fields you actually need. Also check your MySQL indexes are ok, as you want to avoid a full table scan etc. to sum the counts.

Upvotes: 2

Augusto
Augusto

Reputation: 1150

Figured out the error I was doing: the SUM() in the order was grouping the result set.

This works:

post_of_the_moment = find(:all,   :conditions => ["submitted_at BETWEEN ? and ?", from, to],
                      :order      => "(comments_count+votes_count) DESC",
                      :limit      => 10
                       )

Still don't know why I cannot use as a sort field the interestingness method I created.

Upvotes: 0

Related Questions