Výpis dat ze závislých tabulek

Školení, která pořádám

Řekněme, že máme výrobky zařazené do skupin, každý výrobek ale může být ve více skupinách. Struktura tabulek je potom vyrobky (id, …), vyrobky_skupiny (vyrobek, skupina) a skupiny (id, …). Co když teď chceme vypsat seznam výrobků (třeba omezený nějakou podmínkou) se všemi skupinami, do kterých patří? Možnosti jsou v zásadě tři:

  1. Projít seznam všech výrobků a pro každý výrobek zjistit jeho skupiny. Řešení je sice přímočaré, ale kvůli nekonstantnímu počtu dotazů do databáze také pomalé.
  2. Zvlášť zjistit požadovaný seznam výrobků a zvlášť skupiny, do kterých patří. Řešení nás bude stát jen dva dotazy do databáze, ale mezivýsledky si budeme muset uložit do paměti.
  3. Vše vyřešit jedním složitějším dotazem.

O jednodušší variantě tohoto problému jsem už psal, takže první dva způsoby se s menšími úpravami dají vyřešit stejně. Třetí způsob je složitější:

<?php
$result = mysql_query("
    SELECT vyrobky.id, vyrobky.nazev, skupiny.nazev AS skupina_nazev
    FROM vyrobky
    LEFT JOIN vyrobky_skupiny ON vyrobky.id = vyrobky_skupiny.vyrobek
    LEFT JOIN skupiny ON vyrobky_skupiny.skupina = skupiny.id
");
if (mysql_num_rows($result)) {
    echo "<ul>\n";
    $prev_id = 0;
    while ($row = mysql_fetch_assoc($result)) {
        if ($prev_id != $row["id"]) {
            echo ($prev_id ? "</li>\n" : "") . "<li>" . htmlspecialchars($row["nazev"]) . (isset($row["skupina_nazev"]) ? " - " . htmlspecialchars($row["skupina_nazev"]) : "");
            $prev_id = $row["id"];
        } else {
            echo ", " . htmlspecialchars($row["skupina_nazev"]);
        }
    }
    echo "</li>\n";
    echo "</ul>\n";
}
mysql_free_result($result);
?>

SQL příkaz vrátí pro každý výrobek tolik řádků, v kolika je skupinách (díky LEFT JOIN ale vždy alespoň jeden). Pomocí proměnné $prev_id zjišťujeme, zda se jedná o další skupinu toho stejného výrobku nebo o další výrobek. Za cenu jednoho SQL dotazu vznikl ale myslím poměrně nepřehledný skript, který navíc z databáze přenáší podstatně víc dat, než je nezbytně nutné (údaje týkající se výrobku se přenášejí pro každou skupinu znovu, i když je už k ničemu nepotřebujeme). Jistého zpřehlednění by se dalo dosáhnout odstraněním proměnné $prev_id a rozdělením kódu do dvou částí – naplnění dat do struktury array($id => array("nazev" => $nazev, $skupiny => array()), …) a jejich následný výpis.

Elegantní řešení tohoto problému od MySQL 4.1 nabízí funkce GROUP_CONCAT. Ta podobně jako ostatní agregační funkce vrátí pro každou skupinu vždy jen jednu hodnotu, konkrétně zřetězení jednotlivých prvků skupiny.

<?php
$result = mysql_query("
    SELECT vyrobky.id, vyrobky.nazev, GROUP_CONCAT(skupiny.nazev) AS skupiny
    FROM vyrobky
    LEFT JOIN vyrobky_skupiny ON vyrobky.id = vyrobky_skupiny.vyrobek
    LEFT JOIN skupiny ON vyrobky_skupiny.skupina = skupiny.id
    GROUP BY vyrobky.id
");
if (mysql_num_rows($result)) {
    echo "<ul>\n";
    while ($row = mysql_fetch_assoc($result)) {
        echo "<li>" . htmlspecialchars($row["nazev"]) . (isset($row["skupiny"]) ? " - " . htmlspecialchars($row["skupiny"]) : "") . "</li>\n";
    }
    echo "</ul>\n";
}
mysql_free_result($result);
?>

Skript se podstatně zjednodušil, zpřehlednil a v určitých případech také zrychlil. Výpis skupin je funkcí GROUP_CONCAT možné formátovat téměř libovolně, např. výrazem GROUP_CONCAT('<a href="skupina.php?select=', skupiny.id, '">', skupiny.nazev, '</a>' SEPARATOR ', ') je možné skupiny vypsat i s odkazy (v tom případě je nutné z výpisu odstranit funkci htmlspecialchars a název skupiny případně ošetřit přímo v SQL), vkládání HTML kódu do SQL příkazu ale příliš míchá aplikační a prezentační logiku, proto bych takovéto složitější formátování ve většině případů spíše nedoporučoval.

Přijďte si o tomto tématu popovídat na školení Návrh a používání MySQL databáze.

Jakub Vrána, Řešení problému, 31.3.2006, diskuse: 21 (nové: 0)

Diskuse

johno:

Ejha. Tlačiť HTML do PHP je bad smell, ale HTML do SQL? Nová doba! Toto je asi nová forma miešania logiky a prezentácie čo? Elegatné mi to teda nepripadá ani náhodou.
31.3.2006 03:24:43

ikona dgx:

No...

Tento blog respektuji jako velmi přínosný a čtu jej rád. Občas však také narazím na některé obraty, jaký zmiňuje třeba Johno, které mě trošku zarazí.

Na jedné straně chápu, že jde o určitou zkratku, aby příklady nebyly příliš komplikované (třeba občasné užívání $_GET['xxx'] přímo v SQL), také si cením pečlivosti a korektnosti (například zde zmíněná nutnost ošetřit název skupiny, nebo informace v patičce), nicméně ve výsledku to fakt může vést ke špatným návykům, zvláště u začínajících programátorů.

Nechci se tě Jakube nijak dotkout, ale jen mě to už párkrát napadlo.
31.3.2006 05:20:31

ikona Jakub Vrána OpenID:

Já se u takovýchto prasárniček obvykle snažím zdvihnout varovný prst, teď jsem na to zapomněl. Díky za upozornění, varování jsem doplnil.
31.3.2006 13:42:45

loyza:

Toto bude trochu Off Topic, ale chtel bych se zeptat v cem je uvedeny dotaz lepsi nez pouzit: SELECT .... WHERE (vyrobky.id = vyrobky_skupiny.vyrobek) AND (vyrobky_skupiny.skupina = skupiny.id)
Zas az tak moc tomu SQL nerozumim, ale tohle mi prijde z hlediska pochopeni jednodussi.
31.3.2006 08:57:50

johno:

LEFT JOIN ti vráti NULL v stĺpcoch, ak neexistuje riadok v tabuľke, kde by vyrobky_skupiny.skupina = skupiny.id. Ten tvoj INNER JOIN by takýto riadok ignoroval.

http://www.tizag.com/sqlTutorial/sqljoin.php
31.3.2006 12:56:15

ikona Petr Václavek:

A me zase prijde, ze tento blog neni az tak pro uplne zacatecniky, takze jeho ctenari uz o PHP neco vedi :)
Kazdopadne vyse uvedeny problem jsem pred pul rokem resil a porad jsem nemohl prijit na nejake elegantnejsi reseni nez je (2). Tenkrate jsem o funkci GROUP_CONCAT nemel tuchy, ale priste uz ji urcite vyuziji.
31.3.2006 12:27:44

PHX:

Z osobni zkusenosti vim, ze kdyz je treba zaradit neco do vice skupin tak se u jednoduzsich aplikaci vyplati pouzit napr varchar() kam se da napr ;1;5;7;15; (proste ID skupiny oddelene strednikem) a pak v dotazu pouzit % jako zastupce cehokoliv. U jednoduzsich aplikaci se pak lze lepe orientovat v DB.
Netusim ale jak na tom je rychlost. A uznavam, ze bude kapku slozitejsi tvorba dotazu.
Sam jsem toto vyuzil u fotogalerie, kde jsem mel stromovou strukturu skupin a pri vlozeni fotky jsem do skupiny napsal vsechny ID i nadrazenych skupin. (1 fotka sla i do vice kupin) a pote pri vypisu jsem pouzil jen WHERE skupina LIKE '%;10;%' V tomto pripade mi to prislo jednodussi nez dalsi tabulka. Co vy na to?
31.3.2006 18:22:57

ikona Jakub Vrána OpenID:

Podle mě to je cesta do pekel. Dotazy můžou být o něco jednodušší (nepřipojuje se vazební tabulka), ale při šikovném databázovém serveru budou pomalejší. Navíc to naráží na problém jednotného jazyka, který čas od času zmiňuji - u vazební tabulky je každému na první pohled jasné, k čemu je. Při použití takovéhoto zkratkového zápisu si nejprve musím přečíst dokumentaci nebo to odhadnout dat (jakou mám ale záruku, že nějaký další speciální znak nebude plnit další účel?).

Navíc pochopitelně nejdou použít pokročilejší funkce databází jako např. smazání vazeb na skupinu při smazání skupiny.

Také jsem pro co největší jednotnost. Pokud bych se rozhodl k vazbě přidat nějakou další informaci (třeba pořadí ve skupině), musel bych tento koncept celý předělat.
31.3.2006 18:37:09

johno:

Chcel by som vidieť ako na niečo také spravíš indexy. Navyše varchar asi stačiť nebude. Je to denormalizovaná tabuľka a to uznávam len ak sú na to naozaj vážne performance dôvody.
(reakce na) 31.3.2006 21:50:26

MTZ:

Celkovo tento pristup odporuje teorii databaz a to ze informacie atributu maju byt atomicke t.j. dalej nedelitelne
(reakce na) 2.4.2006 00:11:37

JKa:

Funkci GROUP_CONCAT jsem jednou použil, ale musel jsem ji nakonec vyhodit, protože má omezenou délku výsledného řetězce a to vadilo. Pro takovéhle krátké spojování je to ale příjemná funkce.
31.3.2006 19:17:07

Hds:

Tento článek přišel jako na zavolanou, právě si nad něčím podobným lámu hlavu :) Díky
1.4.2006 01:22:19

Frodik:

Neni v těch příkladech chybička ?

Nemělo by místo tohoto:

SELECT vyrobky.id, vyrobky.nazev, skupiny.nazev AS skupina_nazev
    FROM vyrobky
    LEFT JOIN vyrobky_skupiny ON vyrobky.id = vyrobky_skupiny.vyrobek
    LEFT JOIN skupiny ON vyrobky_skupiny.skupina = skupiny.id

být toto:

SELECT vyrobky.id, vyrobky.nazev, skupiny.nazev AS skupina_nazev
    FROM vyrobky, skupiny
    LEFT JOIN vyrobky_skupiny ON vyrobky.id = vyrobky_skupiny.vyrobek
    LEFT JOIN skupiny ON vyrobky_skupiny.skupina = skupiny.id

Kdyz to zkousim, tak bez uvedeni druhe tabulky napise MySQL Error: Neznama tabulka 'skupiny'
27.6.2006 12:30:32

ikona Jakub Vrána OpenID:

Dotaz je v pořádku, tabulka skupiny je připojená správně. Problém bude někde jinde (třeba v prostém překlepu).
27.6.2006 14:10:19

Frodik:

Ano máte pravdu, problém nebyl v překlepu, ale v něčem podobném. Můžete tedy prosím tento můj příspěvek a ten minulý klidně smazat. Omlouvám se.
27.6.2006 17:25:33

Basti:

Moc mi to pomohlo, ale pokud potřebuju k tabulce výrobky přiřadit 2 různé sloupce z tabulky skupiny? Jak by vypadal výsledný dotaz? Zatím jsem rozkódoval jak funguje LEFT JOIN ale není mi jasné kam přiřadím dotaz na druhý sloupec z tabulky skupiny. Díky:)
30.1.2007 01:17:41

ikona Jakub Vrána OpenID:

Dotaz by vypadal stejně, jen by se do sloupců vyjmenovaných za klíčovým slovem SELECT přidal ten druhý sloupec.
30.1.2007 11:12:40

Basti:

Díky:) Nakonec jsem potřeboval něco trochu jiného: "... GROUP_CONCAT(autor.jmeno,' ',autor.prijmeni)..."

Pokud bych přidal sloupec autor.prijmeni za SELECT, tak by to vrátilo příjmení jen prvního z autorů. A navíc to přidá další sloupec, takže práce navíc s výpisem výstupu v PHP.
30.1.2007 11:47:32

Michal:

Ahoj,
prikaz Group_concat je to co hledam, ale jak mam pouzit dva nejednou?Nejak se v tom ztracim.
Mam tabulku tym, hrac, a trener. tym ma vic hracu i vic treneru. Mam tedy spojovaci tabulky tym_hrac a tym_trener.
Dotaz na spojeni si vymyslim, takze dostanu mrte radku pro kazdy tym a pro kazdeho hrace vypis vsech treneru...
Netusim jak udelat ten group abych ve vysledku dostal treba tabulku id tymu, nazev tymu, id hracu, jmena hracu, id treneru, jmena treneru...
diky za kazdej napad a radu...
8.12.2007 22:42:24

pojízdná kočka:

Je GROUP_CONCAT standardizovaná? (myslím, podle SQL'92, SQL'99)
3.2.2009 09:04:23

ikona Jakub Vrána OpenID:

Ne, je to MySQL extenze.
3.2.2009 09:38:42

Diskuse je zrušena z důvodu spamu.

avatar © 2005-2024 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.