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!