CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecord
D-Nice
D-Nice

Reputation: 4870

Rails 3 complex query with sort

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

Upvotes: 1

Views: 57

Answers (1)

user419017
user419017

Reputation:

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.

Upvotes: 1

Related Questions