access the individual user's progress through a list of lists via polymorphic join table

progress.sql

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