# sqlite3 access to "watched" status

**URL:** <https://forums.plex.tv/t/sqlite3-access-to-watched-status/81143>\
**Category:** Dev/API Corner\
**Tags:** other-dev\
**Created:** [October 28, 2014, 1:59am UTC](https://forums.plex.tv/t/sqlite3-access-to-watched-status/81143 "2014-10-28T01:59:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![envoy510](https://avatars.discourse-cdn.com/v4/letter/e/ad7895/32.png) [@envoy510](https://forums.plex.tv/u/envoy510)\
**Post date:** [October 28, 2014, 1:59am UTC](https://forums.plex.tv/t/sqlite3-access-to-watched-status/81143/1 "2014-10-28T01:59:00Z")

</div>

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).

&nbsp;

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.

&nbsp;

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".&nbsp; I have found this to be reliable except for items that have had their "watched" status changed in a client.&nbsp; Btw, the clients do properly show the "watched" state.

&nbsp;

Anyone know what I can do to fix this?

&nbsp;

Thanks.

&nbsp;

---

<div class="post-metadata">

**Author:** ![anon18523487](https://avatars.discourse-cdn.com/v4/letter/a/49beb7/32.png) [@anon18523487](https://forums.plex.tv/u/anon18523487)\
**Post date:** [October 28, 2014, 3:59am UTC](https://forums.plex.tv/t/sqlite3-access-to-watched-status/81143/2 "2014-10-28T03:59:00Z")

</div>

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. &nbsp;When you change a show to unwatched, it changes this value down to 0. &nbsp;So you need to add this table and check for a view\_count \> 0.

---

<div class="post-metadata">

**Author:** ![anon18523487](https://avatars.discourse-cdn.com/v4/letter/a/49beb7/32.png) [@anon18523487](https://forums.plex.tv/u/anon18523487)\
**Post date:** [October 28, 2014, 3:14pm UTC](https://forums.plex.tv/t/sqlite3-access-to-watched-status/81143/3 "2014-10-28T15:14:04Z")

</div>

On further review, you do not even need to check metadata\_item\_views. &nbsp;Every thing in there should also be in metadata\_item\_settings.

---

<div class="post-metadata">

**Author:** ![envoy510](https://avatars.discourse-cdn.com/v4/letter/e/ad7895/32.png) [@envoy510](https://forums.plex.tv/u/envoy510)\
**Post date:** [October 29, 2014, 12:55am UTC](https://forums.plex.tv/t/sqlite3-access-to-watched-status/81143/4 "2014-10-29T00:55:58Z")

</div>

> On further review, you do not even need to check metadata\_item\_views. &nbsp;Every thing in there should also be in metadata\_item\_settings.

I was thinking that, too, but I&nbsp; noticed something odd about metadata\_item\_settings: there are multiple rows for a given item that have item\_count \> 0.&nbsp; 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.&nbsp; I may be OK.&nbsp; Let me do some experimentation.

Thanks for the help.&nbsp; I appreciate it.

---

<div class="post-metadata">

**Author:** ![envoy510](https://avatars.discourse-cdn.com/v4/letter/e/ad7895/32.png) [@envoy510](https://forums.plex.tv/u/envoy510)\
**Post date:** [October 29, 2014, 1:09am UTC](https://forums.plex.tv/t/sqlite3-access-to-watched-status/81143/5 "2014-10-29T01:09:00Z")

</div>

> On further review, you do not even need to check metadata\_item\_views. &nbsp;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'
      )
    )
  );
```

Thanks.

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/plex/original/3X/2/a/2acb9765406f63293d357b4ec509ec39aa28f2ad.png) [@system](https://forums.plex.tv/u/system)\
**Post date:** [December 21, 2019, 3:24am UTC](https://forums.plex.tv/t/sqlite3-access-to-watched-status/81143/6 "2019-12-21T03:24:30Z")

</div>

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