Rails 3 complex query with sort

Question

I have a User model and an Event model. User has_many events. What I want to be able to do is find the User that has the most events created in the last 24 hours.

Ideally, the output format would have be an array of hashes [{User => [Event, Event, ...]}], which would be sorted by User's with the highest events.count

Thanks

possible duplicate of [Rails order by results count of has\_many association](http://stackoverflow.com/questions/16996618/rails-order-by-results-count-of-has-many-association) — infused, Jul 10 '14 at 18:39

Damien Roche · Accepted Answer · 2014-07-10T18:25:24.013

This works for me, but might require tweaking depending on your database.

class User
  scope :by_most_events, -> { 
    joins(:events)
    .select("users.*, count(events.id) as event_count")
    .where(events: { created_at: 24.hours.ago..Time.now })
    .group("users.id, events.id") # this is for postgres (required group for aggregate)
    .order("event_count desc")
  }
end

### usage

User.by_most_events.limit(1).first.event_count
# => 123

As Sixty4Bit mentioned, you should use counter_cache, but it will not suffice in this situation because you need to query for events created in the last 24 hours.

Rails 3 complex query with sort

1 Answers1