?

Log in

No account? Create an account
entries friends calendar profile Elf Sternberg's Pendorwright Projects Previous Previous Next Next
SQL from Hell, my left... - Elf M. Sternberg
elfs
elfs
SQL from Hell, my left...

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

2 comments or Leave a comment
Comments
zanfur From: zanfur Date: June 15th, 2013 06:02 am (UTC) (Link)
Hmm. I just wrote a fairly technical comment, and it was marked as spam. I have no idea why. Can you still see it, or did it just hit the ether bucket? I can see it when I view the page, but it's dimmed.

EDIT: It occurs to me that it's reasonable to assume that SQL in a comment would be something malicious...let's see if an edit of a non-spam-marked post gets through.

... nope. Short version: I recommend replacing the children table subselect with a left join of the stories table with a copy of itself where the first matches a parent_id row and the second matches a child_id row, and using a null check for the parent_id field. It's scads faster than checking against each child to ensure you're not in the list of children, basically just short-circuiting whenever it finds that there's a child at all and skipping to the next one. In general, avoid "not in" subqueries, because they're generally the slowest way to go about things.
elfs From: elfs Date: June 24th, 2013 03:14 am (UTC) (Link)
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.
2 comments or Leave a comment