Avert your eyes! Protect the children! A SQL Command From Hell!

Seriously, I’m only half-kidding here. This has to be pretty much the most complex SQL command of my career. No, wait, I take that back, I did one even more bizarre for Scilla at IndieFlix once.

This is for Narrator 4.0. It’s for Postgres, which supports all sorts of nifty things you won’t find in lesser databases (*Cough*MySQL*Cough), and this is one of the niftiest. I have four tables in play here: The series table, the stories table, the slugs table (which keeps track of all the URL components that go into crafting the full URL to a story or series), and the children table, which keeps the relationships between series, sub-series, and stories. This query gets all the relationships, then with them builds a list of series titles and stories titles, along with all the necessary slugs.

WITH RECURSIVE breadcrumb(id, child_id, path, type) AS (
	SELECT id, child_id, concat(id,'/',child_id) AS path, type 
	FROM narrator_children WHERE id = 2
	UNION
		SELECT nc.id, nc.child_id, CONCAT(breadcrumb.path,'/',nc.child_id) AS path, nc.type 
		FROM narrator_children nc, breadcrumb
		WHERE breadcrumb.child_id = nc.id)
(SELECT title, slug, path, narrator_slugs.id sid 
	FROM narrator_stories 
	JOIN narrator_slugs 
	ON narrator_stories.slug_id = narrator_slugs.id
	JOIN breadcrumb
	ON narrator_stories.id = breadcrumb.child_id
	WHERE breadcrumb.type = 'story' 
UNION 
	SELECT title, slug, path, narrator_slugs.id sid 
	FROM narrator_series 
	JOIN narrator_slugs 
	ON narrator_series.slug_id = narrator_slugs.id
	JOIN breadcrumb 
	ON narrator_series.id = breadcrumb.child_id
	WHERE breadcrumb.type = 'series'
	) ORDER BY path;

Try doing that with MySQL or SQLite!