Collections: Batch assigning collection via command line or DB

[chrischarles2002]

First off - HUGE fan of PLEX!

I love getting everyone I know on the bandwagon.

 

I would like to know if anyone has any ideas on how to batch assign items to a Plex "Collection" either by some command line tool, or directly in the SQLite database.

 

Until Plex adds the functionality to have "sub-libraries" or "sub-folder" management to group like titles together, we will use the Collection feature.

 

As some know, in Windows we like to group our movies together in one folder, like "Star Trek" or "Rocky Movies" where we will group all sequels in one sub-folder underneath the main Plex Library folder.

 

My immediate project, is that I would like to group all Stand Up Comedy together, without creating a new Library just for that category.

So I will create a Collection named "Stand Up Comedy" under the Library "Comedy".

 

I've got a couple of hundred titles in its own Windows folder specifically named "Stand Up Comedy".

 

Is there a way where I can batch assign the entire structure of this folder to the Collection "Stand Up Comedy"?

 

I haven't been able to find a command line tool to do this, but what I have found in SQLite is the following:

select * from tags
where tag_type=2

I believe that "tag_type=2" represents "Collections"

 

This will give me the tag id number for the Collection "Stand Up Comedy" that I'm interested in.

Let's call it tag id = 19113

 

Then I am also able to run this query:

select * from section_locations
where library_section_id=6

This gives me the specific ID of the Windows path where "Stand Up Comedy" is stored.

Let's call it section_location ID = 49

 

I then run this query:

SELECT FROM `media_items` 
WHERE  `section_location_id` ='49'

This will give me a list of all the items stored in Windows under my "Stand Up Comedy" folder.

 

 

 

So what I'm missing is this:  Is there a way in the DB (or command line tool) that I can assign everything that has a this specific path (ID=49) to a specific Collection (ID=19113)?

 

 

I realize this may be a little to involved, and I will just have to do it manually at the end of the day, but if anyone has any advice on how to do this, it would be very much appreciated.

[justin_jalcorn.net]

A little more investigation (5 years later), although I’m not ready to start hacking at this:

sqlite> select * from tags where id = 71734;
71734||Men In Black|2||||2019-09-10 18:22:00|2019-09-10 18:22:00||||
sqlite> .schema taggings
CREATE TABLE IF NOT EXISTS "taggings" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "metadata_item_id" integer, "tag_id" integer, "index" integer, "text" varchar(255), "time_offset" integer, "end_time_offset" integer, "thumb_url" varchar(255), "created_at" datetime, 'extra_data' varchar(255));
CREATE INDEX "index_taggings_on_metadata_item_id" ON "taggings" ("metadata_item_id" );
CREATE INDEX "index_taggings_on_tag_id" ON "taggings" ("tag_id" );
sqlite> select * from taggings where tag_id = 71734;
1240534|71279|71734|0|||||2019-09-10 18:22:00|
1250905|69315|71734|0|||||2019-09-13 17:23:07|
1257398|72591|71734|0|||||2019-09-20 07:48:50|
1257497|72641|71734|0|||||2019-09-20 08:25:14|
sqlite> select * from media_items where metadata_item_id  = 72591;
201079|1|4|72591||1280|694|1373284081|6350434|1730003|mp4|h264|aac|1.84615385532379|23.9759998321533|6|||name=Men%20in%20Black%203&source=bluray&year=2012|0||2019-09-20 05:01:26|2019-09-20 05:01:07|0||6||ma%3AaudioProfile=lc&ma%3Ahas64bitOffsets=0&ma%3AvideoProfile=high||||

So insert a row into taggings. Not sure what the ‘index’ column does, and it appears that you’re responsible for filling in created_at.

be sure to back up your database before you try anything.

EDIT: I created a collection with one video, then added all the other videos I wanted in the collection with the script below, passing in the name of the tag. It worked, the collection is correct and usable. It’s careful not to create a duplicate tag. But no warranty is expressed or implied - use at your own risk.

#!/bin/bash


DB="/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db"
CREATEDAT=$(date +"%F %T")
C=$(echo "SELECT COUNT(id) FROM tags WHERE tag='${1}' AND tag_type = 2;" | sudo sqlite3 "${DB}")
if [[ $C -eq 0 ]];then
        echo "TAG ${1} Not Found"
        exit
elif [[ $C -gt 1 ]];then
        echo "TAG ${1} Matched ${C} tags"
        exit
fi
TAGID=$(echo "SELECT id FROM tags WHERE tag='${1}' AND tag_type = 2;" | sudo sqlite3 "${DB}")
tty=$(tty)
for i in $(echo "select metadata_item_id from media_items where hints like 'name=BSidescleveland2017%' AND metadata_item_id NOT IN (SELECT metadata_item_id FROM taggings WHERE tag_id = ${TAGID});" | sudo sqlite3 "$DB");
do
        echo "INSERT INTO taggings('metadata_item_id','tag_id','index','created_at') VALUES (${i},${TAGID},0,'${CREATEDAT}');" | tee $tty | sudo sqlite3 "${DB}"
done

[M8urn]

You might want to take a look at this.