Sorteren met JOIN tijdens UPDATE

Inleiding

Een paar weken geleden besloot ik mijn productie web- en databaseserver opnieuw in te richten. Ik vond dat een goede gelegenheid om dan ook maar meteen over te stappen van het door Oracle commerciëel ontwikkelde MySQL naar het volledig open source en vrije MariaDB. In een eerder artikel schreef ik over de installatie en het debuggen daarvan. Deze week stelde ik vast dat een bepaalde functie in één van mijn web-apps niet meer naar behoren functioneerde. Wat was hier aan de hand?

Sorteren

De web-applicatie bevat muzikale uitwerkingen met tekst en akkoorden in ChordPro-formaat (Songs). Deze worden gegroepeerd in setlijsten (Sets). Achter de schermen gebeurt dit met een koppeltabel (Song2Set). Om een set alfabetisch te kunnen sorteren, moest ik de song2set_sortorder kunnen bepalen op basis van de song_title en song_artist uit de gekoppelde Song-tabel.

Omdat het UPDATE-statement van MySQL geen ORDER BY ondersteunt van een andere tabel dan diegene die geüpdate wordt, moest ik op zoek naar een oplossing. Met behulp van StackOverflow kwam daar het volgende uit:

DROP PROCEDURE IF EXISTS `set_alfasort`;
CREATE PROCEDURE `set_alfasort`(
    IN `SETID` BIGINT
)
BEGIN
    SET @DUMMY = -1;

    UPDATE `Song2Set` AS S2S
    JOIN
    (
        SELECT `song_id`
        FROM `Song` AS S
        ORDER BY
            S.`song_title` ASC,
            S.`song_artist` ASC
    ) AS SO
    ON S2S.`song_id` = SO.`song_id`
    SET
        `song2set_sortorder` = @DUMMY := @DUMMY + 1
    WHERE
        `set_id` = SETID;
END;

Hiermee wordt in één UPDATE-statement bij elke Song in dat Set de sorteervolgorde ingesteld op basis van een alfabetische sortering van de titel en artiest. Dit werkte lange tijd naar wens.

Tot ik merkte dat er iets niet klopte in de sortering; ik maakte de volgende procedure, die eveneens een tijd lang naar behoren functioneerde:

DROP PROCEDURE IF EXISTS `set_alfasort`;
CREATE PROCEDURE `set_alfasort`(
    IN `SETID` BIGINT
)
BEGIN
    SET @rownr = -1;

    UPDATE
        `Song2Set` AS S2S
    SET
        `song2set_sortorder` = (
            SELECT DUMMY.`rowNumber` FROM (
                SELECT
                    @rownr:=@rownr+1 as rowNumber,
                    S.`song_id`
                FROM (
                    SELECT
                    S2.`song_id`,
                    S2.`song_title`
                FROM
                    Song as S2
                    RIGHT JOIN `Song2Set` AS S2S
                    ON S2.`song_id` = S2S.`song_id`
                WHERE
                    S2S.`set_id` = SETID
                ORDER BY
                    S2.`song_title` ASC,
                    S2.`song_artist` ASC
                ) AS S
            ) as DUMMY
            WHERE
                DUMMY.`song_id` = S2S.`song_id`
        )
        WHERE
            S2S.`set_id` = SETID;
END;

Tot ik afgelopen week merkte dat de setlijsten niet volledig alfabetisch werden gesorteerd; alsof er een soort GROUP BY actief was, een extra niveau tijdens het sorteren. Ik bedacht dat misschien de overstap van MySQL naar MariaDB de oorzaak kon zijn. Om dat uit te sluiten richtte ik met Docker een MySQL-server in op een Raspberry Pi. Ik vulde de database en voerde de procedure uit…

Jammer, maar helaas. Ook nu werden de songs niet allemaal goed gesorteerd. Zowel MySQL als MariaDB vertoonden hetzelfde gedrag met zowel de oude als de nieuwe procedure. Ik moest wederom op zoek naar een alternatief.

Alternarief

Ik ben geen held in SQL; hoe eenvoudiger, hoe liever. Ik probeerde tot nu toe het UPDATE-statement in één query samen te vatten. Wat zou er gebeuren als ik het sorteren in meerdere stappen zou doen? De procedure zou misschien langer, maar beslist ook begrijpelijker worden. Toen bedacht ik het volgende:

DROP PROCEDURE IF EXISTS `set_alfasort`;
CREATE PROCEDURE `set_alfasort`(
    IN `SETID` BIGINT
)
BEGIN
    CREATE TEMPORARY TABLE tmptbl (
        `id` BIGINT AUTO_INCREMENT,
        `song_id` BIGINT,
        PRIMARY KEY (`id`)
    ) AS (
        SELECT
            S.`song_id`
        FROM
            `Song` AS S JOIN
            `Song2Set` AS S2S ON
                (S2S.`song_id` = S.`song_id` AND S2S.`set_id` = SETID)
        ORDER BY
            S.`song_title` ASC,
            S.`song_artist` ASC
    );
    UPDATE
        `Song2Set` AS S2S
        INNER JOIN tmptbl AS DUMMY ON DUMMY.`song_id` = S2S.`song_id`
    SET
        S2S.`song2set_sortorder` = DUMMY.`id`
    WHERE
        S2S.`set_id` = SETID;

    DROP TABLE tmptbl;
END;

Dit stuk code doet precies wat ik wil en is zelfs door mij te begrijpen :-). Op naar de volgende bug!