SQL from Hell, my left...

[elfs]

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!)!

Comments

[elfs.livejournal.com]

I had to read that five times before I figured out where the 'with' semantics broke.

Okay, that's an approach. I'll make a note of it. This is, however, working for the current implementation, and I need to move on to the templating engine next. Both have different intake filters (whitelists, using HTML5Lib) for their own purposes, and integrating them has proven to be a pain in the neck.

Thanks, though. I'm keeping this, and plugging it into pgadmin3 when I get the chance.

Sadly, this may actually never see the light of day. I may have to port this to *gack* MySQL, since the server I'm running may not offer Postgres.