MySQL | Recursieve / hierarchische query met _juiste_ sorter

[Thijsmans]

Okee, ik kom er even niet meer uit en ChatGPT weet het ook even niet meer Ik voel me in elk geval niet alleen in het probleem.

Ik heb twee tabellen in een MySQL-database (of: mariadb): b_taxonomy en b_taxonomy_terms. Zoals gebruikelijk bij een taxonomy dient die ertoe om een hierarchische structuur te herbergen. De tabel b_taxonomy bevat wat gegevens als "naam" etc, maar is voor het probleem niet van belang. De tabel b_taxonomy_terms ziet er zo uit:

code:

id  taxonomy_id  parent_id  machine_name term_full term_short  
1   7            NULL       zwart        zwart     zwart
3   7            1          wit          wit       wit        
4   7            1          paars        paars     paars      
2   7            3          grijs        grijs     grijs

Ik wil de taxonomy op een website op hierarchische wijze weergeven. Maar het kunnen flinke lijsten worden, met tot enkele tienduizenden geneste termen. Er moet dus een pager in komen. Bij voorkeur haal ik om te beginnen al een beperkte set op uit de database. Daarvoor heb ik de volgende query bedacht:

SQL:

WITH RECURSIVE tree AS 
(
    SELECT * FROM b_taxonomy_terms WHERE taxonomy_id = 7 AND parent_id = 0

    UNION ALL

    SELECT t.* FROM b_taxonomy_terms AS t, tree WHERE tree.id = t.parent_id
)

SELECT * FROM tree

Die werkt, in zoverre dat 'ie de tabel weergeeft als hierboven. Maar de sortering is daarbij dus onjuist, want niet-hierarchisch. De laatste rij (id=2) had een rij hoger moeten staan, want is een child van parent_id 3. Hier bereikt mijn hobby-sql zijn grenzen; daarom zocht ik samen met ChatGPT naar een oplossing. ChatGPT stelde voor:

Om de uitvoer te sorteren op basis van de hiërarchische structuur, kunt u de volgende query gebruiken:

SQL:

WITH RECURSIVE taxonomy_tree AS (
  SELECT id, taxonomy_id, parent_id, machine_name, term_full, term_short, created, updated, 1 AS level
  FROM b_taxonomy_terms
  WHERE parent_id IS NULL
  UNION ALL
  SELECT t.id, t.taxonomy_id, t.parent_id, t.machine_name, t.term_full, t.term_short, t.created, t.updated, tt.level + 1
  FROM b_taxonomy_terms t
  INNER JOIN taxonomy_tree tt ON t.parent_id = tt.id
)
SELECT * FROM taxonomy_tree
ORDER BY COALESCE(parent_id, id), level, id;

[...]

Nu staan de rijen in de juiste volgorde, waarbij de rij met id "2" tussen de rijen met id "3" en "4" in staat, omdat deze als kind van rij "3" wordt beschouwd en een niveau dieper in de hiërarchie staat.

Dat zou inderdaad juist zijn, maar de rij met id "2" staat als laatste.

Het spijt me dat de aangepaste query nog niet de gewenste resultaten oplevert. In dat geval kunnen we proberen een andere aanpak te gebruiken, namelijk door gebruik te maken van een LEFT JOIN om de ouderrijen aan de kindrijen te koppelen. Dit zou er als volgt uit kunnen zien:

SQL:

WITH RECURSIVE taxonomy_tree AS (
  SELECT id, taxonomy_id, parent_id, machine_name, term_full, term_short, created, updated, 1 AS level
  FROM b_taxonomy_terms
  WHERE parent_id IS NULL
  UNION ALL
  SELECT t.id, t.taxonomy_id, t.parent_id, t.machine_name, t.term_full, t.term_short, t.created, t.updated, tt.level + 1
  FROM b_taxonomy_terms t
  LEFT JOIN taxonomy_tree tt ON t.parent_id = tt.id
)
SELECT * FROM taxonomy_tree
ORDER BY
  COALESCE(parent_id, id), -- sorteer eerst op de bovenliggende rij of de huidige rij indien het een hoofdrij is
  level, -- sorteer vervolgens op het niveau in de hiërarchie
  id; -- tenslotte sorteer op de id om te voorkomen dat er een willekeurige volgorde ontstaat

[...]

Deze query zou de rijen in de juiste volgorde moeten ophalen, waarbij de rij met id "2" tussen de rijen met id "3" en "4" in staat, omdat deze als kind van rij "3" wordt beschouwd en een niveau dieper in de hiërarchie staat.

Rij 2 staat nog steeds onderaan.

Dat is vervelend om te horen. Ik zie echter geen enkele reden waarom de query die ik heb gegeven de rijen niet in de juiste volgorde zou moeten ophalen. Ik vermoed dat er ergens anders in de code of in de database iets aan de hand is waardoor de query niet het gewenste resultaat oplevert.

In ieder geval zou je kunnen proberen om de volgende aanpak te gebruiken om de rijen in de juiste volgorde op te halen. Deze aanpak maakt gebruik van de MySQL-specifieke GROUP_CONCAT-functie om de volledige hiërarchie van elke rij op te halen en vervolgens de rijen te sorteren op basis van die hiërarchie. Hier is de query:

code:

SELECT t1.*,
  GROUP_CONCAT(t2.id ORDER BY t2.parent_id SEPARATOR '/') AS hierarchy
FROM b_taxonomy_terms AS t1
LEFT JOIN b_taxonomy_terms AS t2 ON t1.parent_id = t2.id
GROUP BY t1.id
ORDER BY hierarchy;

Met deze query zou de uitvoer als volgt moeten zijn:

Python:

id  taxonomy_id  parent_id  machine_name  term_full  term_short  hierarchy
1   7            NULL       zwart        zwart     zwart      1
3   7            1          wit          wit       wit        1/3
4   7            1          paars        paars     paars      1/4
2   7            3          grijs        grijs     grijs      1/3/2

In dit resultaat is te zien dat de rijen nu gesorteerd zijn op basis van hun hiërarchie, zoals aangegeven in de hierarchy-kolom. Dit zou de rij met id "2" tussen de rijen met id "3" en "4" in moeten plaatsen, omdat deze als kind van rij "3" wordt beschouwd en een niveau dieper in de hiërarchie staat.
Hopelijk helpt dit je om de rijen in de juiste volgorde op te halen. Als dit niet het geval is, laat het me dan gerust weten, dan kunnen we verder kijken wat er misgaat.

Ook hier staat id 2 onderaan. Schrijf een PHP-functie die de rijen ophaalt en in hierarchische volgorde plaatst.

Vervolgens werd een brakke PHP-functie uitgespuugd die evenmin werkte

Goed om te weten dat ik niet de grootste n00b-fout ooit heb gemaakt: kennelijk is het best een lastige vraag. Heeft iemand een idee?

[RobIII]

Thijsmans schreef op vrijdag 10 maart 2023 @ 14:09:
Maar het kunnen flinke lijsten worden, met tot enkele tienduizenden geneste termen. Er moet dus een pager in komen.

Dit is typisch een probleem dat je:
a) niet in SQL moet (willen) oplossen maar in je applicatielaag *
b) misschien niet eens een RDBMS (zoals MySQL) voor moet gebruiken maar mogelijk een ander soort "document DB" of "NoSQL" ding of whatever; iets dat met grafen overweg kan. De vraag is dan: hoe vast zit je aan MySQL?

* Voor de duidelijkheid: JA het kan (meestal wel) in een RDBMS en zélfs "enkele tienduizenden geneste termen" is vast wel prima mogelijk. Je moet je echter afvragen of 't "de right tool for the job" is.

[ Voor 38% gewijzigd door RobIII op 10-03-2023 14:19 ]

[Thijsmans]

De mysql-database is een gegeven; het is deel van een meer omvattend systeem. Natuurlijk is het mogelijk om gewoon alle rijen op te halen en die in PHP te doorlopen en sorteren etc, maar dan zit je volgens mij iets in harde code op te lossen omdat je het niet juist uit een database weet te krijgen. Dat is niet echt een oplossing maar verplaatsing van het probleem

[RobIII]

Thijsmans schreef op vrijdag 10 maart 2023 @ 14:27:
maar dan zit je volgens mij iets in harde code op te lossen

Nou, juist dit soort dingen zijn meestal vele malen beter in je applicatielaag op te lossen dan in SQL (zoals je zelf al merkt ). Begrijp me niet verkeerd; zolang je RDBMS het 'werk' kan doen moet je je RDBMS dat vooral laten doen. Maar zodra je niet meer met "platte data" werkt maar hiërarchische data dan is dat al heel gauw buiten de 'specialiteit' van een RDBMS. Zoals gezegd, JA, de meeste RDBMS'en kunnen er wel iets mee meestal, maar het is wel vaak een 'afterthought'. Eh hoezo "harde code"? Als er iets flexibel is (t.o.v. een query al helemaal) dan is 't wel code. Niks hards aan. Hurhur.

Neemt niet weg dat 't vast ook wel in een query op te lossen is; de vraag is hoe leesbaar dat ding dan is, hoeveel kunst-en-vliegwerk er aan te pas komt. Maar laat je vooral niet door mij tegen houden 't trachten op te lossen in SQL hoor

[ Voor 27% gewijzigd door RobIII op 16-03-2023 10:22 ]

[kutagh]

Al eens gekeken naar de Nested Set Model? Hiermee kan je die hierarchie "platslaan" en in de database opslaan, met die platgeslagen hierarchie is het ook meteen sorteerbaar.

[RobIII]

kutagh schreef op vrijdag 10 maart 2023 @ 15:22:
Al eens gekeken naar de Nested Set Model? Hiermee kan je die hierarchie "platslaan" en in de database opslaan, met die platgeslagen hierarchie is het ook meteen sorteerbaar.

@Thijsmans Neem dan in ieder geval ook even goed de drawbacks door Ik heb 't wel eens gebruikt maar halverwege 't project toch gekozen voor iets anders

[Verwijderd]

Hierarchy, nested objects kun je heel mooi in json kwijt

[Thijsmans]

Uiteindelijk de saaie weg gekozen: in code in plaats van in query

PHP:

public static function getHierarchy ( $taxonomy_id )
{
    $db = static::getDB();

    $q = $db->prepare("SELECT * FROM b_taxonomy_terms WHERE taxonomy_id=:id ORDER BY machine_name");
    $q->bindParam(':id', $taxonomy_id, PDO::PARAM_INT);
    $q->setFetchMode( PDO::FETCH_CLASS, get_called_class() );
    $q->execute();

    $terms = $q->fetchAll();
    $out = [];

    static::getHierarchyChildren( $terms, 0, 0, $out );

    return $out;
}

public static function getHierarchyChildren ( &$terms, $from_parent_id, $level, &$out )
{
    foreach( $terms AS $i => $term )
    {
        if( $term->parent_id == $from_parent_id )
        {
            $term->level = $level;
            $out[] = $term;
            static::getHierarchyChildren ( $terms, $term->id, $level+1, $out );
        }
    }
}

De volgorde klopt nu, en het niveau wordt berekend wat fijn is voor het grafische aspect (indentation).

code:

id          tax_id      parent_id   term_full   level
1           7           0           100         0
4           7           1           110         1
5           7           4           111         2
3           7           1           120         1
2           7           3           121         2
6           7           0           200         0

Wat het qua performance doet, kan ik bij gebrek aan veel data nog niet zeggen. Zou het nuttig zijn om voor regel # 26 hierboven een unset($terms[ $i ]) te doen?

[Webgnome]

Doe is lief en gebruikt een fatsoenlijke return value in plaats van een parameter die je als output behandeld.. .

Maar vind je zelf niet dat dit veel leesbaarder is dan een moeilijke query?

[ Voor 27% gewijzigd door Webgnome op 10-03-2023 22:17 ]

[Thijsmans]

Webgnome schreef op vrijdag 10 maart 2023 @ 22:12:
Maar vind je zelf niet dat dit veel leesbaarder is dan een moeilijke query?

Dat zullen we dus nooit weten, bij gebrek aan werkende query