That horrible SQL statement I posted a few days ago? Ignore it completely. A little learning is a dangerous thing, as evidenced here by the realization that I didn't need to mix in the narrator_children query except to exclude those things that had parents, giving me a perfect query to find parentless (root) objects. My entire query boils down to this:
WITH RECURSIVE get_all (series_id, title, slug, path, url) AS ( SELECT narrator_series.id, title, slug, TO_CHAR(narrator_series.id * 32, '0000000') AS path, CONCAT('', slug) AS url FROM narrator_series JOIN narrator_slugs ON narrator_series.slug_id = narrator_slugs.id WHERE narrator_series.user_id = 1 AND narrator_slugs.slug = 'aimee' AND narrator_series.id NOT IN ( SELECT child_id FROM narrator_children WHERE user_id = 1 and type = 'series') UNION SELECT narrator_series.id, narrator_series.title, narrator_slugs.slug, CONCAT(get_all.path, '/', TO_CHAR(narrator_children.position, '0000000')) AS path, CONCAT(get_all.slug, '/', narrator_slugs.slug) AS url FROM narrator_series JOIN narrator_slugs ON narrator_series.slug_id = narrator_slugs.id JOIN narrator_children ON narrator_series.id = narrator_children.child_id JOIN get_all ON narrator_children.series_id = get_all.series_id WHERE narrator_children.type = 'series' ) (SELECT series_id, title, slug, path, url, 'series' AS type from get_all UNION SELECT narrator_stories.id, narrator_stories.title, narrator_slugs.slug, CONCAT(get_all.path, '/', TO_CHAR(narrator_children.position, '0000000')) AS path, CONCAT(get_all.url, '/', narrator_slugs.slug) AS url, 'story' AS type FROM narrator_stories JOIN narrator_slugs ON narrator_stories.slug_id = narrator_slugs.id JOIN narrator_children ON narrator_children.child_id = narrator_stories.id JOIN get_all on get_all.series_id = narrator_children.series_id WHERE narrator_children.type = 'story') ORDER BY path;
The only problem with this query is that it assumes that the series being asked for is a root series. I want to be able to start from an arbitrary point in the tree, with ascent and decent as needed, but that should come eventually. But grief, that is so much shorter and more readable (and it supplies the URLS!)!