[MySQL] parent en children in 1 query selecteren

[Verwijderd]

Ik heb een tabel 'tblcat' met daarin een aantal records. Er is ook een veld 'parent' in die tabel, die 0 kan zijn, of kan verwijzen naar het veld 'id' van diezelfde tabel. Ik wil hierbij 2 niveaus diep kunnen gaan.

(Eigenlijk precies hetzelfde als hier: https://stackoverflow.com...ldren-one-query-selection en dan 2 niveaus diep. Maar met dat voorbeeld krijg ik niet alle parent resultaten eruit.)

Mijn tabel 'tblcat':

id    naam        parent
 1    kleding        0
 2    t-shirts       1
 3    broeken        1
 4    truien         1
 5    sweats         4
 6    hoodies        4
 7    tassen         0
 8    schoenen       0
 9    sportschoenen  8
10    nette schoenen 8

PHP:

$test = mysqli_query($db, " 
SELECT
    p.id AS parent_id,
    p.naam AS parent_name,
    c1.id AS child_id,
    c1.naam AS child_name,
        c2.id as child_id_2,
    c2.naam as child_name_2
FROM 
    tblcat p
LEFT JOIN tblcat c1
    ON c1.parent = p.id
LEFT JOIN tblcat c2
    ON c2.parent = c1.id        
WHERE
    p.parent=0
");

while($row = mysqli_fetch_array($test)) {
   echo $row['parent_id'] . ' ' . $row['parent_name'] . ' - ' . $row['child_id'] . ' ';
   echo $row['child_name'] . ' - ' . $row['child_id_2'] . ' ' . $row['child_name_2'] . '<br>';
}

Mijn output is nu:
1 kleding - 2 t-shirts -
1 kleding - 3 broeken -
1 kleding - 4 truien - 5 sweats
1 kleding - 4 truien - 6 hoodies
7 tassen - -
8 schoenen - 9 sportschoenen -
8 schoenen - 10 nette schoenen -

Ik mis in bovenstaande output de parents indien er (sub)children in zitten:
1 kleding - -
1 kleding - 4 truien -
8 schoenen - -

Wat te doen om die ook mee te krijgen?

[ Voor 93% gewijzigd door Verwijderd op 04-02-2018 10:45 ]

[DJMaze]

Je moet ook helemaal niet stackoverlfow kopiëren, je moet jouw versie van de query posten

[Verwijderd]

@DJMaze Okee, ja da's beter, ik heb dat gedaan nu.

[Muuh87]

Volgens mij voldoet een recursive CTE hier aan.

https://dev.mysql.com/doc/refman/8.0/en/with.html

[Verwijderd]

Muuh87 schreef op zondag 4 februari 2018 @ 11:39:
Volgens mij voldoet een recursive CTE hier aan.

https://dev.mysql.com/doc/refman/8.0/en/with.html

Ja, dat begin ziet er al goed uit met
WITH
... AS (SELECT ... FROM ...),
... AS (SELECT ... FROM ...)
SELECT ....

maar ik vrees dat het niet gaat lukken, want ik heb MySQL 5.5 op de gedeelde server die ik gebruik en die kan ik niet upgraden.

Misschien kan het op een andere manier met 2 'sub' selects in de query.
SELECT ... FROM ... WHERE ... IN (SELECT ...FROM ...WHERE ...= ...)

Ik ga nog even proberen...

// Update:
Met wat PHP code kan ik toch die ontbrekende resultaten erbij toveren:

PHP:

$hoofdcat = 0;
$subcat = 0;

while($row = mysqli_fetch_array($test)) {
    if ( isset($row['child_id']) ) {
        if ( $row['parent_id'] <> $hoofdcat ) {  // eenmalig hoofcatnaam tonen
            echo $row['parent_name'] . '<br>';
            $hoofdcat = $row['parent_id'];
        }
    }
    if ( isset($row['child_id_2']) ) {
        if ( $row['child_id'] <> $subcat ) {  // eenmalig hoofcatnaam en subcatnaam tonen
            echo $row['parent_name'] . ' - ' . $row['child_name'] . '<br>';
            $subcat = $row['child_id'];
        }
    }
    echo $row['parent_name'] . ' - ' . $row['child_name'] . ' - ' . $row['child_name_2'] . '<br>';
}

De output is nu:
kleding
kleding - broeken -
kleding - t-shirts -
kleding - truien
kleding - truien - hoodies
kleding - truien - sweats
schoenen
schoenen - nette schoenen -
schoenen - sportschoenen -
tassen - -

Ik heb nu nog 1 vraag. Aan mijn tabel heb ik nog een veldje 'user_idfk' gehangen. Stel dat ik aan de query nog een WHERE user_idfk = 2 wil toevoegen, dan pakt dat niet. Want als ik p.user_idfk specificeer, dan komt door de JOIN toch ook bijvoorbeeld een user met id=3 mee. Dit wordt lastig denk ik... Als iemand nog tips heeft?

[ Voor 50% gewijzigd door Verwijderd op 04-02-2018 18:33 ]

[Muuh87]

Zou je in je join kunnen oplossen met on x= iets and useridfk =2

Dan ziet hij het als voorwaarde van je join ipv voorwaarde voor je totale resultset

[Verwijderd]

@Muuh87 Oja, natuurlijk, bedankt! Ik geloof dat ik er hiermee ben:

PHP:

SELECT
    p.id AS parent_id,
    p.naam AS parent_name,
    c1.id AS child_id,
    c1.naam AS child_name,
        c2.id as child_id_2,
    c2.naam as child_name_2
FROM 
    tblcat p
LEFT JOIN tblcat c1
    ON c1.parent = p.id AND c1.user_idfk=2
LEFT JOIN tblcat c2
    ON c2.parent = c1.id AND c2.user_idfk=2
WHERE
    p.parent=0 AND p.user_idfk=2
ORDER BY p.naam, c1.naam, c2.naam

Althans, het lijkt te werken nu.

Op zich alleen nog een beetje jammer dat ik nog die IF'jes truc moet uithalen om de parents te tonen indien er (sub)children in zitten. Wellicht kan dat gewoon nog via de query (met een Distinct erbij lijkt me), maar ik zou nu niet meer weten hoe. Extra tips hierover altijd welkom, dit is steeds erg leerzaam allemaal.

[ Voor 4% gewijzigd door Verwijderd op 05-02-2018 12:40 ]

[Muuh87]

Je zou voor de recursive CTE alsnog kunnen kijken naar deze URL:
http://guilhembichot.blog...-recursive-and-mysql.html


Hier hebben ze een voorbeeld voor oude MySQL omgevingen.