[SQL] array aggregation; vermijden van veel queries.

[Verwijderd]

stel:

code:

bedrijf
===
bid KEY -- bedrijf id
naam -- naam bedrijf

klant
===
kid KEY -- klant id
naam -- naam klant

-- klanten hebben meerdere bedrijven en bedrijven meerdere klanten:

bedrijf_klant  
===
bid INT -- een bedrijf
kid INT -- een klant

Nu wil ik graag een lijstje met alle bedrijven en per bedrijf alle klanten van dat bedrijf.
dit kost mij dus per bedrijf een query voor zijn klanten:

PHP:

$q = "SELECT naam, bid FROM bedrijf";
$r = sql_array($db, $q)
while ($a = sql_fetch($r)) {
    echo "<div>$a[naam]</div><div>";

    //stomme sub query vanuit php
    $qq = "SELECT naam FROM klant, bedrijf_klant WHERE klant.kid=bedrijf_klant.kid AND bid=$a[bid]";
    $rr = sql_array($db, $qq);
    while ($aa = sql_fetch($rr)) {
        echo "<span>$aa[naam]</span>";
    }
    echo "</div>";
}

Kan dat ook anders? bijvoorbeeld:

PHP:

$q = "
    SELECT bedrijf.naam, ARRAY(klant.naam) as klanten FROM klant, bedrijf, bedrijf_klant WHERE
    bedrijf_klant.bid=bedrijf.bid AND bedrijf_klant.kid=klant.kid GROUP BY bedrijf.bid;
"; // das dus een JOIN, maar dit is lekker helder

while ($a = sql_fetch($db, $q) {
    echo "<div>$a[naam]</div><div>";

    // uberdebuub barba truuc! weg met die sub query
    foreach ($a['klanten'] as $klant) echo "<span>$klant</span>";
    echo "</div>";
}

Die ARRAY vervangen door CONCAT (welke wel bestaat in sommige SQL implementaties) kan wel.

Maar dit is een heel veel voorkomend probleem. Die eerste methode is lelijk en ik zou graag iets beters zien. Waarom in PHP de Data bij elkaar schrapen, de SQL server is daar veel beter in. Maar hoe precies. Iemand met hints?

-Ozy

[Olaf van der Spek]

Lees de documentatie over JOINs eens.

[Michali]

PHP:

$query = "
    SELECT
        b.bid,
        b.naam AS bedrijfs_naam,
        k.naam AS klant_naam
    FROM
        bedrijf b,
        klant k,
        bedrijf_klant bk
    WHERE
        b.bid=bk.bid
    AND
        k.kid=bk.kid
";

$result = mysql_query($query) or die(mysql_error());

$last = null;

while ( $rs = mysql_fetch_assoc($result) )
{
    if ( $rs['bid'] != $last );
    {
        if ( !is_null($last) )
        {
            echo "</div>";
        }
        echo "<div>" . $rs['bedrijfs_naam'] . "</div><div>";
        $last = $rs['bid'];
    }

    echo "<span>" . $rs['klant_naam'] . "</span>";
}

Niet echt getest hoor, maar mischien geeft het je een idee van hoe het ook kan.

[ Voor 15% gewijzigd door Michali op 19-07-2004 17:33 ]

[Verwijderd]

oke, oke, bedankt jongens, maar ik wil per bedrijf alle klanten, dus niet eentje. Check mijn eerste php stukje maar, regels 9-11. Een loop en dan daar binnenin een tweede loop die alle klanten op een rijtje zet van een bedrijf. De uitkomst is een HTML pagina met alle bedrijven, en per bedrijf al zijn klanten.

Dus stel je hebt 100 bedrijven en 100 klanten, en gemiddeld hebben bedrijven 4 klanten (en klanten misschien ook 4 bedrijven, maar das irrelevant voor nu). Dat levert een pagina op met 100 bedrijven en 400 klanten (elke klant 4x genoemd).

probleem met de bestaande oplossingen is dat het je 101 queries kost: dat is dus ook 101 keer SQL parsen, context switchen, etc. das niet efficient. Ook al zijn SQL servers wel gebouwd om dit efficient te doen. (En ja je kunt ook binden enzovoorts. maar das hier niet het punt.)

Met een ARRAY oplossing, voor zover deze dus bestaat kost het je altijd maar 1 SQL query. Nogmaals er bestaat een CONCATENATE aggregate voor ORACLE maar dat levert een string op zonder scheidingstekens of wat dan ook.

[Michali]

Dat is toch precies wat mijn code doet of ben ik nou dom . Probeer het eens uit te voeren zou ik zeggen.

edit:
Net een klein foutje in de code gewijzigd. Mischien dat ie het nu wel doet

[ Voor 30% gewijzigd door Michali op 19-07-2004 17:34 ]

[Verwijderd]

of ben ik nou dom

nee, je hebt helemaal gelijk. Jouw code zou uitstekend werken. Behalve dat je wel een 'ORDER BY bedrijf.bid' zou moeten doen! Anders heb je geen garanties over de volgorde.

Maar helemaal netjes is het nogsteeds niet. Bovendien, wat als er ook nog een ander soort klanten zijn? klanten2 en een tabel bedrijf_klanten2. Een soort van

code:

'SELECT bedrijf.naam, ARRAY(klanten.naam), ARRAY(klanten2.naam) ... GROUP BY bedrijf.kid'

zou wel werken, maar jouw trucje niet meer ...

[Verwijderd]

code:

SELECT bedrijf.naam, GROUP_CONCAT(klanten.naam SEPERATOR "</span><span>")
FROM
...
GROUP BY bedrijf.kid;

in mysql bijvoorbeeld

nog niet zalig makend, maar het doet tenminste wat ... snap niet waarom er geen uitgebreidere functies zijn, zodat het niet van string weer terug moet naar iets anders.

Bovendien, als ik de mysql syntax goed lees mag dit ook:

code:

SELECT bedrijf.naam, GROUP_CONCAT(klanten.naam, klanten.kid SEPERATOR ",")
FROM
...
GROUP BY bedrijf.kid;

levert:

code:

bedrijf1, "klant1,192,klant2,143,klant3,132,klant4,32"
bedrijf2, "klant5,14,klant6,13"
...

een "</span><span>" daar gebruiken heeft geen zin meer.
-ozy

[Verwijderd]

verkeert gepost

[ Voor 98% gewijzigd door Verwijderd op 19-07-2004 20:27 . Reden: verkeerd gepost ]

[Michali]

Mischien een ideetje om dan dit te doen?

PHP:

<?php

$string = "klant1,192,klant2,143,klant3,132,klant4,32";

$klant_data = explode(",", $string);

for ( $id = 1, $naam = 0; $id < count($klant_data); $id += 2, $naam += 2 )
{
    print "<span><b>ID:</b> " . $klant_data[$id] . ", <b>Naam:</b> " . $klant_data[$naam] . "</span><br/>";
}

?>

[ Voor 20% gewijzigd door Michali op 19-07-2004 20:51 ]

[Verwijderd]

PHP:

// ARRAY aggregate function
function array_step(&$context, $element) {
                if (!is_array($context)) $context = array();

                if (isset($element)) {
                        $e = sqlite_udf_decode_binary($element);
                        if (!in_array($e, $context)) {
                                $context[] = $e;
                        }
                }
}
// ARRAY aggregate function
function array_finalize(&$context) {
        if (isset($context)) {
                        return serialize($context);
        }

        return serialize(array());
}
// register ARRAY aggregate function
sqlite_create_aggregate($db, 'ARRAY', 'array_step', 'array_finalize');

$q = " 
    SELECT bedrijf.naam, ARRAY(klant.naam) as klanten FROM klant, bedrijf, bedrijf_klant WHERE 
    bedrijf_klant.bid=bedrijf.bid AND bedrijf_klant.kid=klant.kid GROUP BY bedrijf.bid; 
"; // das dus een JOIN, maar dit is lekker helder 

while ($a = sql_fetch($db, $q) { 
    echo "<div>$a[naam]</div><div>"; 

    // uberdebuub barba truuc! weg met die sub query 
    foreach (unserialize($a['klanten']) as $klant) echo "<span>$klant</span>"; 
    echo "</div>"; 
}

OKE, das een aardige bult code, maar het werk! helaas is die serialize en unserialize nodig, maar ik heb php een bugreport gestuurd hierover, aangezien het manual beloofd dat je elk php type mag doorgeven ...

Wat is dit nu? In php5 en SQLite een eigengebakken aggregate functie die inderdaad een array verzameld van elementen. Hij doet dit UNIQUE alhoewel dit laatste is niet super efficient is, is het prima voor wanneer de array klein blijft, anders de array naar een hashtable omzetten.

Maar de code werkt super. Dat dit geen SQL native iets is snap ik niet ...

@Michali bedankt voor je input

-ozy