14Jun

SQL from Hell, my left…

Posted by Elf Sternberg as Uncategorized

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

Comment Form

Subscribe to Feed

Categories

Calendar

June 2013
M T W T F S S
« May   Jul »
 12
3456789
10111213141516
17181920212223
24252627282930