PHP okénko: Spojování tabulek

Důležitým obratem při práci s databází je spojování tabulek. Jak už název napovídá, spojování tabulek slouží ke spojení několika tabulek, které jsou v nějakém vztahu (např. master-detail), podle zadaného kritéria.

Dejme tomu, že máme tabulku s výrobky a tabulku se skupinami, do kterých tyto výrobky patří. A dejme tomu, že chceme vypsat přehled všech výrobků spolu se skupinou, do které patří (např. jako výsledek vyhledávání). Na vyřešení tohoto úkolu se ideálně hodí spojování tabulek.

<?php
// odstrašující řešení
$result = mysql_query("SELECT id, skupina, nazev FROM vyrobky WHERE podminka");
while ($row = mysql_fetch_assoc($result)) {
    $skupina = mysql_result(mysql_query("SELECT nazev FROM skupiny WHERE id = $row[skupina]"), 0);
    echo "<a href='?id=$row[id]'>$row[nazev]</a> ($skupina)<br />\n";
}
mysql_free_result($result);

// za určitých okolností přijatelné řešení
$skupiny = array(); // array(id => nazev)
$result = mysql_query("SELECT id, nazev FROM skupiny");
while ($row = mysql_fetch_assoc($result)) {
    $skupiny[$row["id"]] = $row["nazev"];
}
mysql_free_result($result);
$result = mysql_query("SELECT id, skupina, nazev FROM vyrobky WHERE podminka");
while ($row = mysql_fetch_assoc($result)) {
    echo "<a href='?id=$row[id]'>$row[nazev]</a> (" . $skupiny[$row["skupina"]] . ")<br />\n";
}
mysql_free_result($result);

// správné řešení
$result = mysql_query("
    SELECT vyrobky.id, vyrobky.nazev, skupiny.nazev AS skupina_nazev
    FROM vyrobky
    INNER JOIN skupiny ON vyrobky.skupina = skupiny.id
    WHERE podminka
");
while ($row = mysql_fetch_assoc($result)) {
    echo "<a href='?id=$row[id]'>$row[nazev]</a> ($row[skupina_nazev])<br />\n";
}
mysql_free_result($result);
?> 

První řešení je odstrašující proto, že v každém průchodu cyklem se znovu klade dotaz do databáze, což je drahá (pomalá) operace. Druhé řešení je ideální v případě, kdy se proměnná $skupiny využije i k jinému účelu (např. k výpisu všech skupin v navigaci).

Při spojování tabulek se vyplatí dodržovat několik zásad:

1. Všechny sloupce by měly být pomocí tečkové notace uvozeny tabulkou, ke které se vztahují. Předejde se tím problémům při přidání stejnojmenného sloupce do druhé tabulky.
2. Ze stejného důvodu je lepší se vyhnout zápisu SELECT *. Pokud chcete získat většinu sloupců z jedné tabulky, můžete použít  SELECT tabulka.*.
3. Pro spojení tabulek se dá používat i zápis FROM vyrobky, skupiny WHERE vyrobky.skupina = skupiny.id. Tento zápis osobně nemám rád, protože dochází ke smíchání spojovacích a ostatních podmínek, což je nepřehledné obzvláště při větším množství spojovaných tabulek.
4. Existuje i typ spojení NATURAL JOIN, který spojí tabulky podle shodných hodnot stejnojmenných sloupců, ten ale vyžaduje volit pojmenování sloupců tak, aby vzájemně nekolidovaly (tedy např. vyrobky(id_vyrobek, id_skupina, v_nazev), skupiny(id_skupina, s_nazev)), což názvy sloupců zbytečně komplikuje.

Kromě INNER JOIN existuje ještě typ spojení LEFT JOIN, které se liší tím, že pokud v pravé tabulce není nalezen žádný odpovídající řádek, dosadí se místo něj hodnoty NULL. Tento typ spojení se dá využít v situaci, kdy nemáme jistotu, že v pravé tabulce bude existovat alespoň jeden odpovídající záznam, tedy např. pokud výrobek nemusí mít nutně přiřazenou nějakou skupinu.

<?php
$result = mysql_query("
    SELECT vyrobky.id, vyrobky.nazev, skupiny.nazev AS skupina_nazev
    FROM vyrobky
    LEFT JOIN skupiny ON vyrobky.skupina = skupiny.id
    WHERE podminka
");
while ($row = mysql_fetch_assoc($result)) {
    echo "<a href='?id=$row[id]'>$row[nazev]</a> (" . (isset($row["skupina_nazev"]) ? $row["skupina_nazev"] : "bez skupiny") . ")<br />\n";
}
mysql_free_result($result);
?> 

Spojování tabulek se dá využít i ve složitějších případech. Řekněme, že chceme vypsat postupně všechny skupiny se všemi jejich výrobky.

<?php
// přehledné, ale neefektivní
$result = mysql_query("SELECT * FROM skupiny ORDER BY nazev");
while ($row = mysql_fetch_assoc($result)) {
    echo "<h3>$row[nazev]</h3>\n";
    $result1 = mysql_query("SELECT * FROM vyrobky WHERE skupina = '$row[id]'");
    while ($row1 = mysql_fetch_assoc($result1)) {
        echo "<a href='?id=$row1[id]'>$row1[nazev]</a><br />\n";
    }
    mysql_free_result($result1);
}
mysql_free_result($result);

// náročné na paměť
$vyrobky = array(); // array(skupina => array($row, ...), ...)
$result = mysql_query("SELECT id, skupina, nazev FROM vyrobky ORDER BY skupina");
while ($row = mysql_fetch_assoc($result)) {
    $vyrobky[$row["skupina"]][] = $row;
}
mysql_free_result($result);
$result = mysql_query("SELECT * FROM skupiny ORDER BY nazev");
while ($row = mysql_fetch_assoc($result)) {
    echo "<h3>$row[nazev]</h3>\n";
    if (isset($vyrobky[$row["id"]])) {
        foreach ($vyrobky[$row["id"]] as $row1) {
            echo "<a href='?id=$row1[id]'>$row1[nazev]</a><br />\n";
        }
    }
}
mysql_free_result($result);

// méně přehledné, ale efektivnější
$skupina_prev = 0;
$result = mysql_query("
    SELECT skupiny.*, vyrobky.id AS vyrobky_id, vyrobky.nazev AS vyrobky_nazev
    FROM skupiny
    LEFT JOIN vyrobky ON skupiny.id = vyrobky.skupina
    ORDER BY skupiny.nazev, skupiny.id
");
while ($row = mysql_fetch_assoc($result)) {
    if ($skupina_prev != $row["id"]) { // začíná nová skupina, vypíšeme nadpis
        echo "<h3>$row[nazev]</h3>\n";
        $skupina_prev = $row["id"];
    }
    if (isset($row["vyrobky_id"])) { // byl nalezen odpovídající výrobek
        echo "<a href='?id=$row[vyrobky_id]'>$row[vyrobky_nazev]</a><br />\n";
    }
}
mysql_free_result($result);
?> 

Vzhledem k tomu, že u třetího způsobu není na první pohled vůbec patrné, co kód dělá, zvolil bych ve většině případů spíše první nebo druhý způsob (první, pokud je málo skupin a hodně výrobků, druhý v opačném případě). Třetí případ bych tedy zvolil asi jen v situaci, kdy databázový server běží na jiném stroji než webový server a cena každého dotazu do databáze je opravdu vysoká.

Podobně laděné texty můžete najít i na autorově weblogu PHP triky.