[MySQL 5] Child / Parent / Sibling query

[BikkelZ]

Ik heb de volgende query. Met deze query vind ik in een boomstructuur welke items er een parent, child of sibling zijn van een bepaalde node. Dit werkt perfect. Maar hoe kom ik nou te weten of het een child, parent of een brother was?

Het liefste zou ik dit resultaat ook nog in de vorm:

type [STRING] | parent [BOOL] | child [BOOL] | sibling [BOOL]

....terug krijgen, dus dat ik bij het uitlezen alleen maar hoef te kijken of het 0 of 1 is.

SQL:

SELECT TYPE FROM item_type
WHERE id
IN (

SELECT child_item_id
FROM item_child_restriction
WHERE parent_item_id =35
)
OR id
IN (

SELECT parent_item_id
FROM item_child_restriction
WHERE child_item_id =35
)
OR id
IN (

SELECT child_item_id
FROM item_child_restriction
WHERE parent_item_id = (
SELECT parent_item_id
FROM item_child_restriction
WHERE child_item_id =35 )
AND child_item_id != 35
)

Ik merk dat type een reserved keyword is, maar dat gaat nu iig wel goed nog......

[ Voor 5% gewijzigd door BikkelZ op 29-11-2006 10:45 ]

[DexterDee]

De enige makkelijke manier om dit te bereiken lijkt me om het nesting level ook in elk record mee te inserten, dus 0 voor het hoogste niveau en bijv. 3 voor 3 subniveau's daaronder.

gelijke nesting_levels zijn dus siblings, grotere nesting zijn children en kleinere parents

[BikkelZ]

DexterDee schreef op woensdag 29 november 2006 @ 10:57:
De enige makkelijke manier om dit te bereiken lijkt me om het nesting level ook in elk record mee te inserten, dus 0 voor het hoogste niveau en bijv. 3 voor 3 subniveau's daaronder.

gelijke nesting_levels zijn dus siblings, grotere nesting zijn children en kleinere parents

Ja, maar er is geen vaste hierarchie. Dus item type 35 kan bijvoorbeeld zowel item type 33 bevatten alswel dat tegelijkertijd ook item type 33 een item type 35 mag bevatten. Het is een beetje "wat mag wat bevatten, en wat mag de parent bevatten (en is dus ook een mogelijke sibling)".

Voorbeeld:

Je kunt een doos in een kist doen, en een kist in een doos, maar je kunt een kist en een doos niet inblikken terwijl blikken wel in een doos en een kist kunnen, terwijl doperwten overal in kunnen maar niets in een doperwt kan, en alles in een magazijn kan maar een magazijn nooit ergens in gedaan kan worden. En dozen en kisten kun je rustig naast elkaar opslaan in een magazijn, maar je moet geen water en doperwten los in dezelfde kist doen.

Het enige wat dus werkt is om te achterhalen of hij in de verzameling van subquery1, subquery2 of subquery3 zat.

[ Voor 22% gewijzigd door BikkelZ op 29-11-2006 11:07 ]

[Verwijderd]

Per subquery haal je dus childs, parent of brothers op? Als de query verder niet variabel is, kan je deze info gewoon meegeven, zodat je het later weer kan uitlezen in een gelijke resultset. bijv.:

SELECT TYPE, 'child' as soort FROM item_type
WHERE id
IN (

SELECT child_item_id
FROM item_child_restriction
WHERE parent_item_id =35

of, zoals jij het wilt:

SELECT TYPE, '1' as child, '0' as parent, '0' as brother FROM item_type
WHERE id
IN (

SELECT child_item_id
FROM item_child_restriction
WHERE parent_item_id =35

etc.

[BikkelZ]

Dat resulteert in een lange lijst met enen bij child, niet helemaal wat ik zocht, aangezien in het gevalletje 35 er echt maar een mogelijke child is.

[crowke]

Het liefste zou ik dit resultaat ook nog in de vorm:

type [STRING] | parent [BOOL] | child [BOOL] | sibling [BOOL]

....terug krijgen, dus dat ik bij het uitlezen alleen maar hoef te kijken of het 0 of 1 is.

Ik weet niet hoe groot de table is en waar er indexen zijn, maar verwacht je in ieder geval reeds aan een performance-drop

SQL:

SELECT TYPE, 0,1,0 
FROM item_type
WHERE id
IN (
SELECT child_item_id
FROM item_child_restriction
WHERE parent_item_id =35
)

UNION

SELECT TYPE, 1,0,0 
FROM item_type
WHERE id
IN (
SELECT parent_item_id
FROM item_child_restriction
WHERE child_item_id =35
)

UNION

SELECT TYPE, 0,0,1 
FROM item_type
WHERE id
IN (
SELECT child_item_id
FROM item_child_restriction
WHERE parent_item_id = (
SELECT parent_item_id
FROM item_child_restriction
WHERE child_item_id =35 )
AND child_item_id != 35
)

[frickY]

Kun je de subquery ook niet in je SELECT opnemen?
Een subquery zonder results zal een NULL geven, wat je kunt gebruik om te bepalen van welk niveau `type` is.

[BikkelZ]

frickY schreef op woensdag 29 november 2006 @ 11:34:
Kun je de subquery ook niet in je SELECT opnemen?
Een subquery zonder results zal een NULL geven, wat je kunt gebruik om te bepalen van welk niveau `type` is.

Ja daar zat ik ook al aan te denken, gewoon SELECT (subquery), (subquery), (subquery) doen.

Maar hoe houd ik die drie verschillende SELECT's gerelateerd aan elkaar? Dat wel dezelfde item_type id's in een lijn liggen?

[crowke]

Ik kan het hier niet direct testen, maar dit lijkt een verkorte versie van dezelfde query.
Als je kijkt naar de waardes van child en parent kan je afleiden over wat voor node het gaat.

SQL:

SELECT it.TYPE, icr.child_item_id, icr.parent_item_id
  FROM item_type it
     , item_child_restriction icr
 WHERE (it.id=icr.child_item_id and parent_item_id=35)
    OR (it.id=icr.parent_item_id and child_item_id=35)
    OR (it.id=icr.child_item_id and child_item_id!=35 and parent_item_id=(SELECT parent_item_id FROM item_child_restriction WHERE child_item_id =35))

[BikkelZ]

crowke schreef op woensdag 29 november 2006 @ 11:34:
[...]


Ik weet niet hoe groot de table is en waar er indexen zijn, maar verwacht je in ieder geval reeds aan een performance-drop

SQL:

SELECT TYPE, 0,1,0 
FROM item_type
WHERE id
IN (
SELECT child_item_id
FROM item_child_restriction
WHERE parent_item_id =35
)

UNION

SELECT TYPE, 1,0,0 
FROM item_type
WHERE id
IN (
SELECT parent_item_id
FROM item_child_restriction
WHERE child_item_id =35
)

UNION

SELECT TYPE, 0,0,1 
FROM item_type
WHERE id
IN (
SELECT child_item_id
FROM item_child_restriction
WHERE parent_item_id = (
SELECT parent_item_id
FROM item_child_restriction
WHERE child_item_id =35 )
AND child_item_id != 35
)

Dit werkt een beetje. Hiermee krijg ik maar in een kolom een 1 te zien volgens mij. Terwijl een item best wel tegelijkertijd mogelijk een sibling, child en parent kan zijn. Een baksteen kan bijvoorbeeld ook best een baksteen boven zich, onder zich, en links en rechts van zich hebben.

Index zit op child_item_id, parent_item_id en die twee kolommen nog eens samen omdat die de primaray key zijn.

[BikkelZ]

crowke schreef op woensdag 29 november 2006 @ 11:50:
Ik kan het hier niet direct testen, maar dit lijkt een verkorte versie van dezelfde query.
Als je kijkt naar de waardes van child en parent kan je afleiden over wat voor node het gaat.

SQL:

SELECT it.TYPE, icr.child_item_id, icr.parent_item_id
  FROM item_type it
     , item_child_restriction icr
 WHERE (it.id=icr.child_item_id and parent_item_id=35)
    OR (it.id=icr.parent_item_id and child_item_id=35)
    OR (it.id=icr.child_item_id and child_item_id!=35 and parent_item_id=(SELECT parent_item_id FROM item_child_restriction WHERE child_item_id =35))

Deze werkt wel goed, als het id in de kolom child_item_id matched met het id wat ik gebruik (in dit geval 35), dan is het dus een parent, en als het id matched in de kolom parent_item_id is het een child, en als hij helemaal niet matched dan is het een sibling.

Je krijgt alleen als probleem dat als er een combinatie plaatsvindt van mogelijke sibling, child of parent (dus parent en sibling, of sibling en child, etc.) dat ik dan twee rijen terug krijg. Maar dat is op zich nog wel af te vangen met wat code als ik het uitlees, want het wordt wel gesoort op item_type type.

[crowke]

SQL:

SELECT it.TYPE
     , max(if(it.id=icr.child_item_id and parent_item_id=35,1,0))
     , max(if(it.id=icr.parent_item_id and child_item_id=35,1,0))
     , max(if(it.id=icr.child_item_id and parent_item_id!=35,1,0))
  FROM item_type it
     , item_child_restriction icr
 WHERE (it.id=icr.child_item_id and parent_item_id=35)
    OR (it.id=icr.parent_item_id and child_item_id=35)
    OR (it.id=icr.child_item_id and child_item_id!=35 and parent_item_id=(SELECT parent_item_id FROM item_child_restriction WHERE child_item_id =35))
 GROUP BY it.type

is dezelfde query als ervoor, maar geeft elk it.type slechts één keer weer en toont of het is gevonden als child, parent en/of sibling

[BikkelZ]

Deze ga ik uitprinten en inlijsten, wat een beauty


Hij werkt uiteraard als een tierelier

[ Voor 28% gewijzigd door BikkelZ op 29-11-2006 12:24 ]

[BikkelZ]

Hmm het is nog wel zo dat nu als er al een child-parent relatie is dat de child nog wel aangeeft dat het in een bepaald parentobject ingevoegd kan worden terwijl de huidige parent dat object niet als een child toestaat.

[BikkelZ]

SQL:

SELECT  it.id,
    it.type,
    max( if( it.id = icr.child_item_id AND  parent_item_id = 60, 1, 0 ) ) AS `is parent`,
    max( if( it.id = icr.parent_item_id AND  child_item_id = 60, 1, 0 ) ) AS `is child`,
    max( if( it.id = icr.child_item_id AND parent_item_id !=15, 1, 0 ) ) AS `is sibling`
FROM item_type it, item_child_restriction icr
WHERE
(
    it.id = icr.child_item_id
    AND  parent_item_id = 60
)
OR
(
    (
    child_item_id IN
        (
            SELECT child_item_id
            FROM item_child_restriction
            WHERE 
            parent_item_id IN
            (
                SELECT parent.item_type_id
                FROM `page_item` AS parent, page_item AS child
                WHERE  child.id = 68
                AND parent.order_left < child.order_left
                AND parent.order_right > child.order_right
                AND parent.order_right - child.order_right < ANY
                (
                    SELECT parent.order_right - child.order_right
                    FROM `page_item` AS parent, page_item AS child
                    WHERE  child.id = 68
                    AND parent.order_left < child.order_left
                    AND parent.order_right > child.order_right
                )
            )
        )
    )
    AND
    (
        (
            it.id = icr.parent_item_id
            AND  child_item_id = 60
        )
        OR
        (
            it.id = icr.child_item_id
            AND parent_item_id !=15
            AND parent_item_id IN
            (
                SELECT parent_item_id
                FROM item_child_restriction
                WHERE
                (
                     child_item_id = 60
                )
            )
        )
    )
)
GROUP BY it.type

Het natuurlijk wel, maar snel is euh........anders.....

Volgens EXPLAIN heeft hij over parent in die subquery geen enkele key, terwijl er op de kolommen order_right, order_left en id wel degelijk een index is gelegd.

code:

1   PRIMARY     it      index   PRIMARY     type    22      NULL    78      Using index; Using temporary; Using filesort
1   PRIMARY     icr     index   PRIMARY,parent_item_id,child_item_id    parent_item_id  4   NULL    1723    Using where; Using index
5   DEPENDENT SUBQUERY  item_child_restriction  unique_subquery     PRIMARY,parent_item_id,child_item_id    PRIMARY     8   func,const  1   Using index; Using where
2   DEPENDENT SUBQUERY  item_child_restriction  index_subquery  child_item_id   child_item_id   4   func    10  Using index; Using where
3   DEPENDENT SUBQUERY  child   const   PRIMARY,order_left,order_right  PRIMARY     4   const   1    
3   DEPENDENT SUBQUERY  parent  range   order_left,order_right  order_right     4   NULL    2   Using where
4   SUBQUERY    child   const   PRIMARY,order_left,order_right  PRIMARY     4   const   1    
4   SUBQUERY    parent  range   order_left,order_right  order_right     4   NULL    2   Using where

[ Voor 21% gewijzigd door BikkelZ op 05-12-2006 14:00 ]