CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-3.2rails-activerecord
djoll
djoll

Reputation: 1229

Chain an additional order on a Rails activerecord query?

Activerecord seems to ignore my second order in a chained AR query.

What I'm trying to do is retrieve the top 5 most-read articles within the top 25 highest-scored (eg. rated by users) articles.

Article has both read_count (integer) and score (float) columns that I order by.

My (naive?) attempt was this query:

Article.order("score DESC").limit(25).order("read_count DESC").limit(5)

What this returns is simply the first 5 highest-scored articles, rather than the 5 most-read articles within the first 25 highest-scored.

Can I do this in a single query? Thanks for any help!

(I'm on Rails 3.2 and Postgres 9.2)

Addendum

Thank you to brad and Philip Hallstrom for two solutions to this. Either would have been acceptable, although I went with brad's database-only solution as explained in my comment on his answer.

Profiling each query on my local development machine (MBA) showed the subquery solution to be 33% faster than the Ruby-loop solution. Profiling the queries on my production machine (Heroku + Crane production database) showed the subquery solution to be 95% faster! (this is across a database of 40,000 articles).

While the actual time taken is pretty insignificant, 95% faster and 50% fewer lines of code is a pretty good decider.

Ruby-loop solution: (Philip)
   dev: 24 msec
  prod: 16 msec

Sub-query solution: (brad)
   dev: 22 msec
  prod: 0.9 msec

Thanks to both Philip and brad for their answers!

Upvotes: 3

Views: 2583

Answers (3)

brad
brad

Reputation: 9773

Just putting this out there without testing it, but how about doing it with a subquery?

Article.where(id: Article.order("score DESC").limit(25)).order("read_count DESC").limit(5)

Upvotes: 4

strivedi183
strivedi183

Reputation: 4831

I don't think you can do it in a single query

You could do something like

result = Article.order("score DESC").limit(25)
result = result.order("read_count DESC").limit(5)

similar to this SO answer Rails: method chaining in model

Upvotes: 0

Philip Hallstrom
Philip Hallstrom

Reputation: 19879

I don't think you can do it in a single query. But 25 results isn't many to loop through in Ruby land.

Article.order("score DESC").limit(25).
  sort{|a,b| b.read_count <=> a.read_count }.
  slice(0,5)

You could do a subquery or similar, but I doubt it would be any faster.

Upvotes: 1

Related Questions