CODEWITHSUNDEEP
mysqlsqlruby-on-railsactiverecordgreatest-n-per-group
Math is Hard
Math is Hard

Reputation: 942

Greatest-n-per-group rails ActiveRecord SQL

I have a ActiveRecord model Post that contains the fields created_at and score. I want to order the Posts by score in the last 50 ones created. So basically the SQL would be

SELECT * FROM
(SELECT * FROM Posts ORDER BY created_at DESC LIMIT 50) Sorted_Posts
ORDER BY score DESC

Unfortunately I'm not sure how to do this in Rails. I prefer not to use raw SQL to accomplish this since there's many filters other stuff in the query not shown that would really complicate the raw SQL. I looked into perhaps using the to_sql and strip the SELECT * FROM POSTS prefix out then put it in a where or find_by_sql function but I feel like this could be hacky and potentially lead to SQL injections?

Thanks.

Upvotes: 2

Views: 664

Answers (3)

Andrius Buivydas
Andrius Buivydas

Reputation: 271

Maybe something like this:

Order.where(id: Order.order(:created_at => :desc).limit(50).ids).order(:score => :desc)

Upvotes: 3

teubanks
teubanks

Reputation: 710

Not sure if this would work for your use case, but it's one way to accomplish what you're asking

Post.from("(#{Post.order('created_at DESC').limit(50).to_sql}) posts").order('score DESC')

Essentially, you're building the inner query then converting it into an SQL literal and using interpolation to get that into the from portion of the outer query.

Upvotes: 1

Prima
Prima

Reputation: 11

Try this:

Post.order('created_at DESC').limit(50).order('score DESC')

Upvotes: 1

Related Questions