Getting the IMDB ID from Plex via sqlite3

General Discussions
1

This common used to give me the UID for the movie, but now gives me a new form of UID that I cannot translate into an IMDB link:

sqlite3 /volume1/Plex/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases/com.plexapp.plugins.library.db "SELECT metadata_items.title, metadata_items.added_at, cast(ROUND(metadata_items.rating) as integer), cast(ROUND(metadata_items.audience_rating) as integer), metadata_items.year, guid, metadata_items.originally_available_at, height, video_codec, media_parts.size FROM metadata_items JOIN media_parts JOIN media_items WHERE metadata_items.library_section_id=$LIBRARY AND metadata_items.id=media_items.metadata_item_id AND media_items.id=media_parts.media_item_id and media_parts.size>'200000000' ${UHDQ} ORDER BY metadata_items.added_at;”

which now returns

Kiki's Delivery Service|2021-01-09 06:03:08|10|9|1989|plex://movie/5d77683fe6d55c002040ecbd|1989-07-29 00:00:00|1024|h264|2062134742

no part of that data is the imdb ID tt0097814, and as far as I can tell digging through the database, the strings tt0097814 and 0097814 do not appear anywhere.

So how do I get the IMDB id now so I can create a link?

(I don’ t know when this changed as this is not something I do very often).

2

is there a better place to post this?

3

This changed a while ago when the Plex Movie agent was introduced.

Do you do this manually? There are other ways to get the data that might be easier.

But If you want to look in the database …

In the tags table, items with tag_type = 314 are the foreign IDs. The taggings table associates tags with metadata_items.

sqlite3 com.plexapp.plugins.library.db <<EOF
    SELECT
        metadata_items.title,
        metadata_items.added_at,
        cast(ROUND(metadata_items.rating) as integer),
        cast(ROUND(metadata_items.audience_rating) as integer),
        metadata_items.year,
        tags.tag,
        metadata_items.originally_available_at,
        media_items.height,
        media_items.video_codec,
        media_parts.size
    FROM
        metadata_items
    JOIN
        media_items,
        media_parts,
        taggings,
        tags
    WHERE
        metadata_items.library_section_id=${LIBRARY}
        AND metadata_items.id=media_items.metadata_item_id
        AND media_items.id=media_parts.media_item_id
        and media_parts.size>'200000000'
        and metadata_items.id = taggings.metadata_item_id
        and taggings.tag_id = tags.id
        and tags.tag_type='314'
        and tags.tag like 'imdb%'
        ${UHDQ}
    ORDER BY
        metadata_items.added_at;
EOF

What is UHDQ?

1 Like
4

UHDQ is a variable the I pass in to the querry to extract (or not) UHD videos.

Thanks, this works.

Basically, to diff this, I have to add a join for tagging and tags and then

        and taggings.tag_id = tags.id
        and tags.tag_type='314'
        and tags.tag like 'imdb%'

I would never have figured this out on my own. Thanks again.

5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.