Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
access the individual user's progress through a list of lists via polymorphic join table
select lesson from (
with
-- root collection is a collection of collections (playlist of playlists) which use a polymorphic join table (tracklists) so we want to snag the ids of the sub-collection.
root_collection_ids as ( select tracklists.tracklistable_id id from playlists inner join tracklists on playlists.id = tracklists.playlist_id where playlists.slug = 'some-playlist-id' and tracklists.tracklistable_type = 'Playlist'),
-- the next layer are video resources (lessons) that are the tracklists of the tracklists
lesson_ids as (select tracklists.tracklistable_id lesson_id from root_collection_ids inner join tracklists on root_collection_ids.id = tracklists.playlist_id where tracklists.tracklistable_type = 'Lesson' )
-- we only care about completions so we can get the slug id for all of the video resources (lessons) the user has completed and sort display on the client accordingly
select lessons.slug lesson from lesson_views
inner join lessons on lesson_views.lesson_id = lessons.id
inner join lesson_ids on lesson_ids.lesson_id = lessons.id
where lesson_views.user_id = 1 and lesson_views.did_complete = true
) x
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment