Random SQL Review: DH.N Photo Collection Icons

I mentioned last month that I’d been sharing “interesting” SQL queries from my own code with a coworker as a way to spread some SQL knowledge that I think sometimes gets overlooked.  I’m still not sure that I want to do a whole series of blog posts around that but I have another query that I’ve already shared so I might as well write something up about it.

This one comes from DetroitHockey.Net’s photo gallery.  The entire thing is a custom-built system.  Each photo belongs to at least one “collection” and each collection has an icon made up of three of the most recent photos in that collection.

The caveat is that some collections might be marked as hidden.  This is so that, for instance, if I’m adding photos from a single game to the site, I can create a collection for that game and mark it as hidden until all of the photos have been added, than mark it as visible and all of the new photos will appear at once.  And photos from hidden collections should not be used in a collection icon.

One other bit of context is that all photos are stored in DH.N’s asset management system so when we’re pulling photos to use in creating a new collection icon, we need to tie into the asset management system.

None of this is particularly intensive but it results in a handful of nested queries and a cross-schema join so let’s take a look

$query = '
	SELECT asset_path
	FROM (
		SELECT photo.photo_id
		FROM photo_collection
			NATURAL JOIN photo
		WHERE ((collection_id = :collection_id) AND (collection_type = :collection_type) AND (photo.photo_id NOT IN (
			SELECT photo.photo_id
			FROM photo
				INNER JOIN photo_collection t ON ((photo.photo_id = t.photo_id) AND (t.collection_type = :collection_type_general))
				INNER JOIN collection ON t.collection_id = collection.collection_id
			WHERE collection_hide_photo = 1)))
		ORDER BY photo_date DESC, photo_id DESC
		LIMIT 20) photo_set
			INNER JOIN photo ON photo_set.photo_id = photo.photo_id
			INNER JOIN ' . $this->pdo->names->getCore() . '.asset ON photo.asset_id_photo = asset.asset_id
	ORDER BY RAND()
	LIMIT 3';

First off, I’m showing some PHP here to highlight the cross-schema join.  This query is run against the “photo” schema but I need to get the asset data from the “core” schema.  I can do that via my custom PDO class so I concatenate in $this->pdo->names->getCore() to handle that.

Looking at it in this context, I see that the indenting is a little bit off, so that’s something for me to go back and fix later.  I don’t think it impacts explaining what it does, though.

We want to end up with three random asset paths, so that’s what the outermost layer of the query does.  We’ll be getting a set of photo IDs from the inner layers of the query and from there we can do that cross-schema join to get the asset paths.  Then we randomize the result and limit it to three (on the PHP side we handle cases where fewer than three photos are available).

At the second layer we’re selecting the twenty most-recent photos for this specific collection and collection type.  However, as mentioned, we need to account for hidden collections.  We do that by getting the ID of every photo in a hidden collection and saying that our photo IDs can not be in that set.

As I said, it’s nothing particularly intense, but it’s three layers deep and it’s confusing enough that my indenting isn’t right even in production, so I think there’s something that can be learned from it.