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