CODEWITHSUNDEEP
ruby-on-railsactiverecord
JimmyW
JimmyW

Reputation: 39

ActiveRecord order by columns from multiple tables

Lets say I have a User model

class User < ActiveRecord::Base
  has_many :posts
  has_many :comments
  ...
end

and both Post and Comment have a created_at column.

How can I order Users based on the latest created_at column of both posts and comments combined using ActiveRecord?

I know I can use the Ruby's sort method but that is too slow.

Upvotes: 0

Views: 205

Answers (1)

Ilya Konyukhov
Ilya Konyukhov

Reputation: 2791

Assuming you use PostgreSQL:

# Users with most recently created posts/comments go first
User.left_outer_joins(:posts, :comments)
    .group('users.id')
    .order('GREATEST(MAX(posts.created_at), MAX(comments.created_at)) DESC')

# .. and go last
User.left_outer_joins(:posts, :comments)
    .group('users.id')
    .order('GREATEST(MAX(posts.created_at), MAX(comments.created_at)) ASC')

For MySQL the code seems to be the same.

Upvotes: 2

Related Questions