I need to access the watched status of files in my library (I'm writing a script that will remove files which are not seeding and were watched more than N days ago).
My problem is that when I use the Plex web ui to mark something as unwatched, the data in the database doesn't change like I think it should.
Here's what I'm doing to determine if a particular file (in the filesystem) has been watched:
select viewed_at from metadata_item_views where id in
(select id from metadata_item_views where guid in
(select guid from metadata_items where id in
(select metadata_item_id from media_items where id in
(select media_item_id from media_parts
where file='$file'
)
)
)
);
The above will return something like "2014-08-08 22:09:32". I have found this to be reliable except for items that have had their "watched" status changed in a client. Btw, the clients do properly show the "watched" state.
There is another table you need to look into. metadata_item_settings
When an item is watched, it also adds and entry here and increments the "view_count" column. When you change a show to unwatched, it changes this value down to 0. So you need to add this table and check for a view_count > 0.
On further review, you do not even need to check metadata_item_views. Every thing in there should also be in metadata_item_settings.
I was thinking that, too, but I noticed something odd about metadata_item_settings: there are multiple rows for a given item that have item_count > 0. How should I deal with that?
The rows item_count > 0 seem to have the viewed_at I need, so I guess I could return all of them, convert the dates and take the most recent.
EDIT: correction on the multiple rows: there are rows with the same guid, but they're for the parents. I may be OK. Let me do some experimentation.
On further review, you do not even need to check metadata_item_views. Every thing in there should also be in metadata_item_settings.
Btw, this seems to do the trick just fine:
select last_viewed_at from metadata_item_settings
where view_count > 0 AND guid in
(select guid from metadata_items where id in
(select metadata_item_id from media_items where id in
(select media_item_id from media_parts
where file='$file'
)
)
);
