I am setting up some automation for both plex and kodi to help me keep my library organized. I have kodi 100% working (it also uses a SQLite DB) but I’m having trouble getting the equivalent query working in Plex server for Windows.
For plex I know I must use the special : ‘Plex SQLite.exe’ executable, but I am hoping an expert here could help me with two things: the query and running it as a one-liner in a batfile. For KODI the query is as follows:
SELECT c00 as ‘Title’,
strPath AS ‘Path’,
strfilename AS ‘File’
FROM movie_view a
JOIN (
SELECT uniqueid_value
FROM movie_view
GROUP BY uniqueid_value
HAVING COUNT(*) > 1) b ON a.uniqueid_value = b.uniqueid_value
ORDER BY strfilename ASC;
.exit
’
Below is an example of the Windows BAT file I am using for Kodi, it was important for me to have a one-liner that launches SQLite, give it the DB and query to use all at one time. Not included here but there is also a powershell component to this - it scans the created CSV and takes action depending on how many dupes are found via SQL. I can include my powershell if anyone has any interest.
I did a simple ‘.tables’ command in the SQL file. It spit out a CSV with correct data… Now I just need the SQL query figured out. Unlike in KODI there are no views in the Plex DB. All media seem to be in one big table and that’s tripping me up.
Plex’s unique identifier would be the guid in the metadata_items table, and if you’re only interested in movies, you can filter to metadata_type=1. File paths are stored in media_parts.file, and to match that with metadata_items, you need to join media_items on metadata_items.id=media_items.metadata_item_id, then media_parts on media_parts.media_item_id=media_items.id.
The following should get you close to what your Kodi query outputs (though Plex doesn’t split the path from the file name, so there will only be a single column there):
SELECT metadata.title AS Title, parts.file AS Path FROM media_parts parts
INNER JOIN media_items items ON items.id=parts.media_item_id
INNER JOIN metadata_items metadata ON metadata.id=items.metadata_item_id
INNER JOIN (
SELECT guid FROM metadata_items
WHERE metadata_type=1
GROUP BY guid
HAVING COUNT(*) > 1
) duplicates ON duplicates.guid=metadata.guid
ORDER BY title, metadata.guid;
Actually, looking again, there’s a flaw in my query. I think what mine does will only show you either movies that are duplicated across multiple libraries, or items that were manually split apart and not rematched. Instead, if you wanted to find items that Plex’s Duplicates filter would show, you would want to find media_items that have the same metadata_item_id. Something like this:
SELECT metadata.title AS Title, parts.file AS Path FROM media_parts parts
INNER JOIN media_items items ON items.id=parts.media_item_id
INNER JOIN metadata_items metadata ON metadata.id=items.metadata_item_id
INNER JOIN (
SELECT metadata_item_id FROM media_items
GROUP BY metadata_item_id
HAVING COUNT(*) > 1
) duplicates ON duplicates.metadata_item_id=metadata.id
WHERE metadata.metadata_type=1
ORDER BY title, metadata.guid;
@DTR I appreciate you providing a revised script, but now I’m really torn. Each version has returned different results and I think for my purposes I had better consider both sets of results as valid duplicates.
I think I will try to combine both into one and see how it goes.
I’m an SQL noob. Is there a guide anywhere on how to access the database via SQL? Where is the DB file located (what’s the name of it?). I’m trying to modify media timestamps so I can organize recently added items and make tv shows stack instead of showing 10 different individual episodes under Recently Added section.
@MovieFan.Plex Thanks!
Do you know what attributes need to be modified in order to control the order of Recently Added items? What attribute is it looking at to know to show up in this space?
It’s based on the added_at date with the most recent at the front.
For grouping if the date are close to each other, they get grouped into a season. I forget the exact length but I think it is 6 days. So episodes added weekly, will list each episode, but if you bulk add episodes, they get grouped.
