09Jun

Avert your eyes! Protect the children! A SQL Command From Hell!

Posted by Elf Sternberg as Uncategorized

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 series_paths (series_id, child_id, type, path) AS (
    WITH RECURSIVE breadcrumb(series_id, child_id, type, path) AS (
        SELECT narrator_children.series_id, child_id, type,
               CONCAT('/',TO_CHAR(position, '0000000')) AS path
        FROM narrator_children
            JOIN narrator_series ON narrator_series.id = narrator_children.series_id
            JOIN narrator_slugs ON narrator_series.slug_id = narrator_slugs.id
        WHERE slug = $1 AND narrator_series.user_id = $2 and narrator_children.type = 'series'

                UNION

                SELECT nc.series_id, nc.child_id, nc.type, 
                       CONCAT(breadcrumb.path,'/',TO_CHAR(position, '0000000')) AS path
                FROM narrator_children nc, breadcrumb
                WHERE breadcrumb.child_id = nc.series_id AND nc.type = 'series')

            SELECT * from breadcrumb

            UNION

            SELECT 0, narrator_series.id, 'series', ''
            FROM narrator_series
            JOIN narrator_slugs ON narrator_series.slug_id = narrator_slugs.id
            WHERE slug = $1 AND narrator_series.user_id = $2)

    (SELECT narrator_series.id AS id, title, slug, 'series' AS type, series_paths.path AS path,
            narrator_children.series_id AS parent_id

    FROM narrator_series
    JOIN narrator_slugs ON narrator_slugs.id = narrator_series.slug_id
    JOIN narrator_children ON narrator_series.id = narrator_children.child_id
    JOIN series_paths ON narrator_children.child_id = series_paths.child_id
    WHERE narrator_children.type = 'series' AND narrator_children.child_id IN (SELECT child_id FROM series_paths)

    UNION

    SELECT narrator_stories.id AS id, title, slug, 'stories' AS type,
           CONCAT(series_paths.path, '/', TO_CHAR(position, '0000000')) AS path,
            narrator_children.series_id AS parent_id
    FROM narrator_stories
    JOIN narrator_slugs ON narrator_slugs.id = narrator_stories.slug_id
    JOIN narrator_children ON narrator_stories.id = narrator_children.child_id
    JOIN series_paths ON narrator_children.series_id = series_paths.child_id
    WHERE narrator_children.type = 'story' AND narrator_children.series_id IN (SELECT child_id FROM series_paths)

    UNION

    SELECT narrator_series.id AS id, title, slug, 'series' AS type, '' AS path, 0 AS parent_id
    FROM narrator_series
    JOIN narrator_slugs ON narrator_slugs.id = narrator_series.slug_id
    WHERE slug = $1 AND narrator_series.user_id = $2)

ORDER BY path;

Try doing that with MySQL or SQLite!

[EDIT] I had to play with this a couple of times. The first time through, I had a serious recursion bug because the RECURSE couldn’t tell the difference between a story and a series. Now it only works with series, and the generation of final story paths in an artifice imposed in main body.

Comment Form

Subscribe to Feed

Categories

Calendar

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