Advanced ordering of an attribute in an associated model

Question

I have a set of users. They have many requests.

# user.rb
has_many :requests

now I'd like to sort them by the last request created_at timestamp.

So if that a user has the most recent request (the one where created_at is closest to the current time) they're #1 etc.

Any ideas?

I've tried

User.includes(:requests).order('requests.created_at')

however that sorts them by the first request created_at timestamp which is obviously not what I want. I wish to only retrieve their last request. Then sort the users last requests by their created at date and order the users by that.

Update: Also if the user doesn't have any requests, they should be at the bottom. Now they get placed on top if they don't have any.

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

User.joins(:requests).includes(:requests).order('requests.created_at desc')

You need to joins it so it will sort on the join table.

includes will just preload your requests for you, it won't help you to sort the requests.

UPDATE

Borrowing from SQL how to make null values

To incorporate into this case, it probably looks something like this

User.joins(:requests).includes(:requests).order('CASE WHEN requests.created_at IS NULL THEN 1 ELSE 0 END asc, requests.created_at desc')

First order would put the null ones at the bottom, then order by date desc

edited May 23 '17 at 12:33

Community

1
1

answered Jun 27 '16 at 17:04

lusketeer

1,890
1
12
29

Any idea on how I can sort it so that also user whom don't have any requests come last? Currently they're first. – Sebastian Jennings Almnes Jun 27 '16 at 17:27

score 1 · Answer 2 · answered Jun 27 '16 at 16:53

1

You have to sort in descending order. Try

User.includes(:requests).order('requests.created_at desc').first()

answered Jun 27 '16 at 16:53

avinoth

412
2
6
22

Advanced ordering of an attribute in an associated model

2 Answers2