How to query db for tags, favorites and or album relationships

[Muddywaters]

First post to the forum and not sure this is the optimal group but here goes:
I am a fairly new Plex user with decent linux and sql skills. One of the use cases I am experimenting with is allowing friends to see photo libraries which are proofs from photo shoots and click the heart and add it to favorites or perhaps create an album and add the photos they like. My plan was to use this to identify the images they like and want me to clean up and or print them.

My pilot, with a brand new plex user added as a friend has hit a wall. The user is not really technically savvy though they were able to create an album and add some images to it. The trouble is I can’t find a way to see what is in the album. I don’t see anything in the UI that allows a public or shared album so I thought I would be able to query the db and extract the list of images that way.

I copied the sqlite3 db from my synology NAS that is my server to my linux desktop, installed sqlitebrowser and have been poking around looking at the data in the various tables. I found the album in a couple of places but nothing that looks like a simple junction table representing what I would call an associative entity type.

Can someone help? I exported the schema but don’t see any foreign keys defined anywhere and while the columns are named pretty well I kept getting lost trying to track it all down.

I am running the latest plex server I am pretty sure. The Synology Package center shows installed version is 1.16.3.1402-22929c8a2 and from the Plex web interface I see WebClient version 3.104.2.

Thanks in advance and if this would be better posted somewhere else please let me know.

[Muddywaters]

I found a post that said Photo Albums are playlists and that has gotten me closer.

Looking at the data in metadata_items I found the name of the photo album and from there managed to get to the names of the files and the folder they are in.

select
ML.id as listid
, A.name
, ML.title as listtitle
, MI.id as itemid
, MI.title as itemtitle
, MP.title as folder
from
metadata_items ML
, play_queue_generators QG
, metadata_items MI
, metadata_items MP
, metadata_item_accounts MA
, accounts A
where
ML.metadata_type = 15
and ML.id = QG.playlist_id
and QG.playlist_id = 699
and QG.metadata_item_id = MI.id
and MI.parent_id = MP.id
and ML.id = MA.metadata_item_id
and A.id = MA.account_id;

Commenting out the QG.playlist_id = 699 condition should return a list of all albums and images in an album with the folder and name of the album they are in. At least when the library is set up by person with individual folders per shoot.

Not sure this is the best path through the data or how it will break with different setups but at least this shows a few of the relationships. I thought I could get there via play_queues where I can see that the user has 2 created one called favorites and one named as they named it but can’t find the link between the play_queue, play_queue_generators and metadata_item.
Favorites are evidently done differently and I have not found that yet but wanted to document what I found so far.