I have a script that I run that returns the 5 most recent movies in my Plex server, and I have tried to do the same thing to give me a list of the most recent TV episodes, but am unable to get ti to work.
Basically, I want an sqlite3 command that I can executer at the shell that returns something like “Modern Family, The magicians, You’re the worst, The Daily Show, The Goldbergs” when I ask for the 5 newest episodes. I don’t want the episode titles just the series name, but I cannot figure out how to get it.
I thought something like this
SELECT * FROM metadata_items WHERE library_section_id=1 and title like "%You Owe Me a Unicorn%";
would at least get me started, but that doesn’t show a series name at all (It’s The Passage).
If I do this instead
SELECT * FROM metadata_items join media_items WHERE metadata_items.library_section_id=1 and title like "%You Owe Me a Unicorn%";
I do get results that list "The%20Passage, but I get results that include every single episode of every single series with the info for that episode prepended to each line.
select episodes.id,shows.title,episodes.title,seasons.`index`,episodes.`index` from metadata_items as episodes join metadata_items as seasons on seasons.id=episodes.parent_id join metadata_items as shows on shows.id=seasons.parent_id where episodes.library_section_id=2 and episodes.metadata_type=4 order by episodes.added_at desc limit 10;
Wow. Well yes, that works (mostly, it lists the Daily show twice because there were two new episodes) but how would I have gotten there? Can you explain what the index does and what metadata_type=4 does?
