SQLite queries

Dev Community Dev/API Corner
1

Hello,

I have created SQLite queries to get a list of my movies and TV shows.
Has I tooked me a while to understand the database schema, the SQLite SQL and get this queries to work, I decided to share this and I hope I’m in the correct place.

These queries are adapted to my database (library ID for example), so you’ll need to adapt them to your database.

Movie List
SELECT film.title as Titre, group_concat(tags.tag, ', ') AS Tags, librairie.name, media.duration/60000 AS duration, media.width, media.height, printf("%.4f",media.frames_per_second) AS framerate, media.bitrate/1000 AS birate, media.video_codec, media.size/1048576 AS SIZE, film.summary FROM metadata_items film JOIN media_items media ON media.metadata_item_id = film.id JOIN library_sections librairie ON librairie.id = film.library_section_id JOIN taggings ON taggings.metadata_item_id = film.id JOIN tags on tags.id = taggings.tag_id WHERE film.metadata_type = 1 AND librairie.name IN ("Animation","Concerts","Documentaires","Films","Spectacles","Théatre") AND tags.tag_type = 1 GROUP BY film.id ORDER BY librairie.name, film.title

TV Show Episode List
SELECT serie.title as Série, group_concat(tags.tag, ', ') AS Tags, saison.[index] as Saison, episode.[index] as Episode, episode.title as Titre, librairie.name, media.duration/60000 AS duration, media.width, media.height, printf("%.4f",media.frames_per_second) AS framerate, media.bitrate/1000 AS birate, media.video_codec, media.size/1048576 AS SIZE, serie.summary FROM metadata_items episode JOIN metadata_items saison ON saison.id = episode.parent_id JOIN metadata_items serie ON serie.id = saison.parent_id JOIN media_items media ON media.metadata_item_id = episode.id JOIN library_sections librairie ON librairie.id = episode.library_section_id JOIN taggings ON taggings.metadata_item_id = serie.id JOIN tags on tags.id = taggings.tag_id WHERE episode.metadata_type = 4 AND librairie.id = 2 AND tags.tag_type = 1 GROUP BY episode.id ORDER BY serie.title, saison.[index], episode.[index]

Please feel free to share corrections or improvments !

2

Very cool! I know a little sql but apparently not enough to add the tags_collection to this. I guess a join drops columns not referenced? Ideally I want to list TV shows that do not have a Collection tag assigned…

3

OK, I finally figured it out:

List all shows:
select title from metadata_items where metadata_items.parent_id is NULL and library_section_id=7 order by title;

List shows with collection tag(s) and what the tags are:
select title, tag from metadata_items join taggings taggings join tags where taggings.metadata_item_id = metadata_items.id AND metadata_items.parent_id is NULL and library_section_id=7 AND taggings.tag_id=tags.id AND tag_type=2 order by title, tag;

Compare those to list shows with no collection tags:
SELECT title FROM metadata_items WHERE title NOT IN (select distinct title from metadata_items join taggings taggings join tags where taggings.metadata_item_id = metadata_items.id and library_section_id=7 AND taggings.tag_id=tags.id AND tag_type=2) AND metadata_items.parent_id is NULL and library_section_id=7;

You’ll need to use whatever library_section_id number corresponds to shows on your plex box, of course.