Complex Queries

[noisyscott]

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:

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;

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

[Conor]

Thank you for sharing that complex query, it's quite an interesting way to discover your collection. What you have created could be done in Cocoa, but it would need to be hard coded and programmed it's so complex. Creating an interface to express that cross reference of actors from the movies with these attributes is quite complex.

I have been filing a bug report (also the method for enhancement requests) with Apple about a method for direct SQL access in Core Data for special occasions. This would maybe allow for a way for users to create smart collections with seriously complex queries. But Core Data tries to keep from having to deal with such low level issues.