Using SQLite3 to List Collections & Collection Movie Titles

[BrainStrokeGuy]

I’m looking to do two things here with SQLite3 and the PLEX database:

1. List all the Collections.
2. List all the movies in each Collection.

Searching yielded zero forum/Internet results in regards to those two tasks. However, I found this wonderful forum post from Nov 2011 that had some hints:

HOWTO: query data from PLEX on a Mac

Kudos to SolarPlex for that excellent tutorial. Obviously, since he posted that 9 years ago, some things have likely changed.

So, it appears I first need to determine what library number my Movies are in.

sqlite> SELECT name,id FROM library_sections;

Which yields this:

name = Movies
id = 1

It was the first Library I created, so no surprise here.

Let’s look at the PLEX database Tables available for querying:

sqlite> .tables

sqlite> .tables
accounts metadata_item_accounts
blobs metadata_item_clusterings
cloudsync_files metadata_item_clusters
devices metadata_item_settings
directories metadata_item_views
external_metadata_items metadata_items
external_metadata_sources metadata_relations

There are lots more, but I’m interested in the metadata_items for the moment. So now I can see what Collections I have by running this:

sqlite> SELECT tags_collection FROM metadata_items WHERE library_section_id=1 ORDER BY tags_collection;

You see one line for every Movie in the Library, here is a tiny snippet of the output:

The Mummy
The Mummy
The Mummy
The Mummy
The Thing|Awesome Movie Remakes
The Thing|Awesome Movie Remakes
The Thing|The 10 Best 80s Sci-Fi Movies
The Top 10 Westerns of All Time
The Top 10 Westerns of All Time
The Top 10 Westerns of All Time
The Top 10 Westerns of All Time
The Top 10 Westerns of All Time
The Top 10 Westerns of All Time|The 73 best action movies of all time
The Top 10 Westerns of All Time|The 73 best action movies of all time
The Top 10 Westerns of All Time|The 73 best action movies of all time
The Top 10 Westerns of All Time|The Coolest Clint Eastwood Movies Ever
The Top 40 Sci-Fi Movies of the 21st Century
The Top 40 Sci-Fi Movies of the 21st Century

None of these Collections are IMDB related, nor is that turned on for ANY of my Libraries.

Note that each Movie in my Library can be:

1. part of no Collection.
2. part of just one Collection.
3. part of two (or more) Collections.

You can see the pipe symbol in the above output delimiting multiple Collection names.

Some of my more popular Movies are in upwards of 7 Collections.

If I do this:

sqlite> SELECT tags_collection FROM metadata_items WHERE library_section_id=1 ORDER BY title;

The output shows Movies with no Collection (blank lines) too:

James Bond

The 32 Best Thriller Films Ever Made

The 50 Best Sci-Fi Movies Of The 2010’s
Old School Sci-Fi/Horror/Action
The 18 Greatest Spy Films Of All Time
The 25 Greatest Thrillers of All-Time

2016 Top 20 Grossing Movies at the Domestic Box Office
Old School (Pre-2000) Movies|20 War Movies You Must See Before You Die

Not quite what I want, but we’re just poking around so far.

Now, let’s try looking for a single specific Collection:

sqlite> SELECT title,year,tags_collection FROM metadata_items WHERE library_section_id=1 AND tags_collection=“The 18 Greatest Spy Films Of All Time” ORDER BY title;

I also added the Title and Year to the output as well:

Bridge of Spies|2015|The 18 Greatest Spy Films Of All Time
Burn After Reading|2008|The 18 Greatest Spy Films Of All Time
Enemy of the State|1998|The 18 Greatest Spy Films Of All Time
No Way Out|1987|The 18 Greatest Spy Films Of All Time
Notorious|1946|The 18 Greatest Spy Films Of All Time
Spy Kids|2001|The 18 Greatest Spy Films Of All Time
The Ipcress File|1965|The 18 Greatest Spy Films Of All Time
The Lives of Others|2005|The 18 Greatest Spy Films Of All Time
Tinker Tailor Soldier Spy|2011|The 18 Greatest Spy Films Of All Time
Zero Dark Thirty|2012|The 18 Greatest Spy Films Of All Time
sqlite>

Great! Except that Collection actually has 18 Movies in it, not the 10 shown in the output above. Upon further poking around, I see that each of those Movies are in a single Collection. The one I am looking for though.

And why only 10? Likely because I used ‘=’ in the last SQL statement. Let’s change that to a LIKE instead:

SELECT title,year,tags_collection FROM metadata_items WHERE library_section_id=1 AND tags_collection LIKE ‘%The 18 Greatest Spy Films Of All Time%’ ORDER BY title;

Argo|2012|The 25 Greatest Thrillers of All-Time|The 18 Greatest Spy Films Of All Time
Bridge of Spies|2015|The 18 Greatest Spy Films Of All Time
Burn After Reading|2008|The 18 Greatest Spy Films Of All Time
Enemy of the State|1998|The 18 Greatest Spy Films Of All Time
No Way Out|1987|The 18 Greatest Spy Films Of All Time
North by Northwest|1959|The 18 Greatest Spy Films Of All Time|The 32 Best Thriller Films Ever Made
Notorious|1946|The 18 Greatest Spy Films Of All Time
Spy Kids|2001|The 18 Greatest Spy Films Of All Time
The 39 Steps|1935|The 73 best action movies of all time|The 18 Greatest Spy Films Of All Time
The Ipcress File|1965|The 18 Greatest Spy Films Of All Time
The Lives of Others|2005|The 18 Greatest Spy Films Of All Time
Three Days of the Condor|1975|Mom’s Favorites|The 18 Greatest Spy Films Of All Time
Tinker Tailor Soldier Spy|2011|The 18 Greatest Spy Films Of All Time
Zero Dark Thirty|2012|The 18 Greatest Spy Films Of All Time
sqlite>

Now we are showing 14 of the 18 Movies. One more step forward. But why is it still missing four Movies? After further research, it seems the 4 Movies missing from the query results belong to 3 or more Collections, and the one I am querying for is not the first or second in the Collection list in each of the four Movies.

So the question is why is this not working.

I feel I am close to goal #2. Probably my lack of expert SQL knowledge OR PLEX has that info yet somewhere else.

Can anyone help?

[anon18523487]

Your SQL looks fine. It should match the collection regardless of how many collections there are. Are you sure those other movies are in Library 1?

[BrainStrokeGuy]

@anon18523487

Thanks for giving it the once over.

Yes, all 18 movies are in the same Library. Here is a GUI view of the four outliers:

Note that each Movie belongs to 3 Collections AND the Collection I am querying for is at the end of the Collections list in each.

So either the SQLite3 “LIKE” command stops matching after two entries in that field (not likely) or the those additional Collection names (after the first two) are stored somewhere else.

I decided to further test, by editing this movie and moving the Collection up to the top as follows:

I export the database from PLEX again (to get a fresh copy to play with) and run the same SQL statement:

Argo|2012|The 25 Greatest Thrillers of All-Time|The 18 Greatest Spy Films Of All Time
Bridge of Spies|2015|The 18 Greatest Spy Films Of All Time
Burn After Reading|2008|The 18 Greatest Spy Films Of All Time
Enemy of the State|1998|The 18 Greatest Spy Films Of All Time
No Way Out|1987|The 18 Greatest Spy Films Of All Time
North by Northwest|1959|The 18 Greatest Spy Films Of All Time|The 32 Best Thriller Films Ever Made
Notorious|1946|The 18 Greatest Spy Films Of All Time
Spy Kids|2001|The 18 Greatest Spy Films Of All Time
The 39 Steps|1935|The 73 best action movies of all time|The 18 Greatest Spy Films Of All Time
The Ipcress File|1965|The 18 Greatest Spy Films Of All Time
The Lives of Others|2005|The 18 Greatest Spy Films Of All Time
The Third Man|1949|The 18 Greatest Spy Films Of All Time|The 25 Greatest Thrillers of All-Time
Three Days of the Condor|1975|Mom’s Favorites|The 18 Greatest Spy Films Of All Time
Tinker Tailor Soldier Spy|2011|The 18 Greatest Spy Films Of All Time
Zero Dark Thirty|2012|The 18 Greatest Spy Films Of All Time
sqlite>

And now it shows up. I reconfirmed the Library info and tried the same test with another of the four outlier Movies. Same result.

It just occurred to me that perhaps my process of getting the database is flawed. In the web GUI I click on Download Database:

To get this:

Then I extract the resulting file:

Which I then rename to plex.db so I’m not typing a hugely long filename each time.

Is that the correct manner to do this? I assume so. But please correct me if I am wrong.

Back to the SQL statement itself, I check here:

SQL LIKE Operator

And it seems my use of the ‘%’ at the beginning and end of the search string (Collection name) is ok. Nevertheless, I tried with a leading ‘%’ only, a trailing only and no ‘%’ in the LIKE. Didn’t make any real difference.

It does appear that my SQL statement was correct:

Any ideas?

[anon18523487]

You may be looking in the wrong place. To be honest, I’m not totally sure what the tags_collection field does. It may just be a summary of the collections info. I know it’s not how collections are actually stored.

Maybe we should start over.

Collections are actually stored in a table called “tags” with the tag_type of 4. Then there is a table called “taggings” which identifies the collections for a specific metadata_item_id. You can then look up the title in the table “metadata_items”. Hopefully that made sense and leads you to the right track.

[BrainStrokeGuy]

@anon18523487

Thanks for the info.

I’ll start taking a look in that direction now.

I’m using:

DbVisualizer

to view the database but it’s quite unwieldy given the size of the my PLEX database. I think I’ll create another PMS instance with only 50 Movies and create 10 small Collections of varying sizes.

Then I can more easily see the data in DbVisualizer.

I’ll post back when I find more.

[BrainStrokeGuy]

@anon18523487

Though you could make it easier for everyone by implementing this feature request:

Just saying …

[anon18523487]

Just back up the entire database. Backing up the collection info only may not help if your metadata changes.

[BrainStrokeGuy]

ok.

So I went ahead and installed a test PMS instance on my desktop and populated it with only 50 Movies. Then I created a few Collections of various sizes to test with. Such a small library allows me to more easily examine the PLEX database in DbVisualizer.

Here are the test Collections:

Anywhere from 2 to 12 Movies in each Collection.

Please note that for the Collections in the tag table:

tag_type is DATA_TYPE = 4, but the actual value that makes it a Collection is ‘2’. See below:

Note these all match the test Collections too. And I tried it on my actual PLEX database, and it worked fine. Kudos to @anon18523487 for the push in the right direction.

Here is the SQL command that takes care of Goal #1 in my original post:

sqlite> select tag from tags where tag_type=2 order by tag;

And the sorted output:

Animated Movies
Back to the Future
Batman Movies
Disney
Planet of the Apes
Police Movies
Sci-Fi Movies
sqlite>

I’ll touch up the output later, like adding the number of Movies in each Collection, after I tackle Goal #2.

So far so good!

[anon18523487]

Good job. Let me know if you get stuck anywhere.