Complex Queries
Posted: Sat May 05, 2012 5:23 pm
Hello Nora and Conor,
Thanks as always for the pedias.
I would love it if we could build more complex data searches to mine our databases with more flexibility and power. I know from a previous request that exposing direct SQLite queries is non-trival so I am just encouraging Bruji to consider some alternatives. The following is a real-life scenario that illustrates my most recent use-case and I think is an example of how rich and interesting data relationships are and how useful this type of feature would be inside the pedias.
I recently had a notion to build a Smart Collection of all movies that starred actors who had appeared in movies rated at 4 stars or higher. From what I could gather, it was an impossible task in the Smart Collection editor so I set about figuring out how to get the raw data from the SQLite database. The difficult bit was that this query needed to first filter, then compare two separate tables (ZENTRY and ZCREDIT). I had a heck of a time getting the relationship of the zcredit.zentry number to return results only from rated movies in zentry. A secondary goal was to add a count to the number of 4 Star or higher movies each actor was in, thus giving me some kind of crude "Heat Map" for actors. Anyway, here is the result of a few hours of internet searching and trial and error:
For anyone who wants to do these types of queries themselves, this page was invaluable for helping me understand and structure the above query: http://www.techrepublic.com/article/use ... es/1045787
This query returns exactly the info I was looking for, but sadly I still cannot create a Smart Collection from it.
Regards,
Scott
Thanks as always for the pedias.
I would love it if we could build more complex data searches to mine our databases with more flexibility and power. I know from a previous request that exposing direct SQLite queries is non-trival so I am just encouraging Bruji to consider some alternatives. The following is a real-life scenario that illustrates my most recent use-case and I think is an example of how rich and interesting data relationships are and how useful this type of feature would be inside the pedias.
I recently had a notion to build a Smart Collection of all movies that starred actors who had appeared in movies rated at 4 stars or higher. From what I could gather, it was an impossible task in the Smart Collection editor so I set about figuring out how to get the raw data from the SQLite database. The difficult bit was that this query needed to first filter, then compare two separate tables (ZENTRY and ZCREDIT). I had a heck of a time getting the relationship of the zcredit.zentry number to return results only from rated movies in zentry. A secondary goal was to add a count to the number of 4 Star or higher movies each actor was in, thus giving me some kind of crude "Heat Map" for actors. Anyway, here is the result of a few hours of internet searching and trial and error:
Code: Select all
SELECT zname, COUNT(*) AS actorcount FROM zcredit WHERE zentry IN (SELECT z_pk FROM zentry WHERE zmyrating > 7 ) GROUP BY zname ORDER BY actorcount DESC;This query returns exactly the info I was looking for, but sadly I still cannot create a Smart Collection from it.
Regards,
Scott