Hi all,
I would like to query the DB in order to retrive a the list of the watched movies togeter with my personal rating I’ve assigned to them (I am talking about the five star rating system).
My question are:
_in which table can I find the “personal rating” column?
_there exists a sort of documentation about the DB?
Below you can find my “raw” query… having several issue:
_metadata_items.rating is not the rating I am looking for (it is the RT rating, in my case)
_it not clear to me how can I properly filter the movies, I guess “container in (‘avi’, ‘mkv’)” is not the best option
SELECT metadata_items.title, metadata_items.rating
FROM media_items
INNER JOIN metadata_items
ON media_items.metadata_item_id=metadata_items.id
where container in ('avi', 'mkv')
Thank you for your help!
There is no official documentation about the DB structure. Only what you can find occasionally in these forums.
Maybe you can achieve your goal with a slightly different approach:
All those rows are exported when you use ExportTools and set a high “detail level”.
Thank you very much, I’ve never really studied ExportTool. It does have the feature I need:
Anyway, I am still interested in learning something mober about the DB structure.
Any link?
FWIW, I recently found personal ratings in the metadata_item_settings table, column rating. Joining to metadata_items on the guid column in both tables seemed to work.
metadata_item_settings contains ratings from all of your users, so join to the accounts table via account_id to filter by the account name.
Personal ratings are stored as numbers 0-10 where 1 star = 2.0, 2 stars = 4.0, etc.
I was surprised to discover that the iOS app lets you rate in increments of 1/2 stars if you slide your finger left and right across the stars (so 1-1/2 stars = 3.0 in the rating column). I hadn’t noticed that on any other platform I use.
