How to sort forum's users by "total_content_length" and then by most recent post?

Question

I would like to sort forum's users by total_content_length. To get the top n writers in the forum I do:

User.order("total_content_length DESC").limit(n)

Now, the problem is when there are two (or more) users with the same total_content_length. In this case, I would like to give preference to the user that created a post most recently.

Post has publisher_id field (which is a user id).

How would you do this ?

bor1s · Accepted Answer · 2011-05-22T14:14:43.183

1

Try to use 2 order statements:

User.order("total_content_length DESC").order("created_at DESC").limit(n)

Try this:

class User < ActiveRecord::Base
  scope :your_scope_name, joins(:posts).order("posts.created_at DESC")
end

then you can use this scope in conjunction with other statements

edited May 22 '11 at 14:14

answered May 22 '11 at 11:47

bor1s

4,081
18
25

User's `created_at` is not relevant here. It should be related somehow to `Post`'s `created_at`. – Misha Moroshko May 22 '11 at 11:55
so you can create some scope for User where you will find his posts and order them by created_at, then you can replace .order("created_at DESC") with your new scope – bor1s May 22 '11 at 12:07
How would you write this scope for user? – Misha Moroshko May 22 '11 at 13:04
you can use joins in the scope – bor1s May 22 '11 at 13:51
@bor1s: I will appreciate a more detailed answer :) – Misha Moroshko May 22 '11 at 13:56
I'he edited my post, take a look, I hope it will help you, also if you see that it is not quite correct you can modify it as you wish – bor1s May 22 '11 at 14:15
Thanks, but it's not really helpful :( When you use `joins` the result set is usually bigger. For example, if there are 3 users, and each user has 2 posts, `joins` will return 6 entries, rather than 3. – Misha Moroshko May 22 '11 at 14:24
use uniq for resulted collection to avoid duplicating records – bor1s May 22 '11 at 14:45

amit_saxena · Answer 2 · 2011-05-22T15:26:43.407

0

Define a method in your User model that gives the date of the latest post (assuming you have a posts association):

def date_of_last_post  
  posts.order('created_at DESC').limit(1)[0].created_at  
end

Then you can get the result as:

top_posters = User.order("total_content_length DESC").limit(n)
# write a conditional sorting algo that swaps values only 
# if a.total_content_length == b.total_content_length based on 'date_of_last_post'

edited May 22 '11 at 15:26

answered May 22 '11 at 13:28

amit_saxena

7,450
5
49
64

That's not right, because when you do the `sort!` you "forget" about the previous `order` sort... – Misha Moroshko May 22 '11 at 13:57
Edited my answer. It is not the best approach, but can get you going for the time being. You can use any standard sorting algo. – amit_saxena May 22 '11 at 15:28

How to sort forum's users by "total_content_length" and then by most recent post?

2 Answers2