Find the Most Recent Item By Group in Rails

Suppose you’re building a Rails app for Users who attend many Events, such that a User has_many :events. Now, suppose you want to query for all the Users who have attended an Event that occurred within the last week.

One simple way would be to fetch all the Events and, through those, grab all the Users that attended them, but this wouldn’t work well if we wanted to associate more complex filtering with Users, or use more complex joins.

When you’ve got a problem like that, this article by Richard Jones is phenomenal! I’ve needed to do this at two jobs and have a hard time dredging up this article, so I’m linking it here.

The gist is to use scenic to create and manage database views:

CREATE VIEW newest_events AS
  SELECT DISTINCT ON (user_id) *
  FROM events
  ORDER BY user_id, created_at DESC