Práca s prepojenými tabuľkami v databáze

Na záver poslednej prednášky sme si vytvorili tabuľku user_phone_numbers pre uchovávanie telefónnych čísel. Aby sme si mohli vysvetliť v praxi prácu s prepojenými tabuľkami, potrebujeme si do tejto tabuľky pridať nejaké dáta. V reálnom svete by sme na to mali na stránke samostatnú sekciu. Podobne, ako vkladáme nového používateľa do tabuľky users cez formulár na domovskej stránke, tak by sme mali sekciu pre vkladanie telefónneho čísla pre používateľa.

Mohlo by to byť spravené napríklad tak, že v poslednom stĺpci tabuľky zobrazujúcej používateľov by bol odkaz (podobne ako vymazať/editovať záznam) na pridanie telefónneho čísla danému používateľovi. Pri prekliku na sekciu by sa cez url nutne posielalo parametrom ID používateľa, na ktorého sa kliklo (aby sme vedeli, komu ideme pridať tel. číslo) a toto id by sa následne aj s telefónnym číslom zadaným cez formulár uložilo do tejto tabuľky. Toto by ste teoreticky mohli zvládnuť aj sami, nateraz sa tým ale nejdeme zdržiavať a údaje si do tabuľky vložíme ručne cez phpMyAdmin.

Predpokladáme, že v tabuľke users máme záznamy s id 1-5. Pre tieto záznamy si vložíme nejaké dáta do tabuľky user_phone_numbers vykonaním tohto príkazu:

INSERT INTO user_phone_numbers (id_user, phone_number) VALUES 
(1, '0949 111 222'),
(2, '0918 222 123'),
(4, '0911 444 999'),
(5, '0911 555 666');

Teraz sa nám v tejto tabuľke nachádzajú dáta, ktoré súvisia s dátami v tabuľke users. Povedzme, že by sme tieto čísla (ak existujú), chceli zobrazovať v tabuľke na úvodnej strane v novom stĺpci. Predtým by sme tieto dáta potrebovali vytiahnuť z databázy. Môžeme to urobiť dvomi spôsobmi. Prvý (taký drevorubačský) spôsob (v podstate jediný spôsob, ktorý by ste teraz zvládli) by bol, že by sme si najprv vytiahli dáta z tabuľky users a následne by sme si vytiahli dáta z tabuky user_phone_numbers. Následne by sme museli tieto dáta prepojiť, čiže by sme si museli cyklom prechádzať všetkých používateľov a následne v každom kroku tohto cyklu prechádzať všetky telefónne čísla a pýtať sa, či sa tam náhodou nenachádza nejaké číslo, ktoré nesie id aktuálneho používateľa. Ak áno, tak by sme si toto číslo nejako priradili k tomuto používateľovi do novej property pre čísla (pole čísel – stringov).

Fungovalo by to, ale nie je to samozrejme optimálne a je to aj časovo náročné. Najmä pri vyššom počte userov a čísiel by to dosť dlho trvalo, v každom kroku používateľa prechádzať všetky čísla... Druhý spôsob, veľmi elegantný a šikovný, je použiť práve na to určenú vymoženosť jazyka SQL a to príkaz JOIN (spojiť). Asi tušíte, čo to spraví a tušíte správne, pomocou tohto príkazu vieme naraz vytiahnuť dáta z viacerých tabuliek. Pri použití tohto príkazu definujeme smer prepojenia tabuliek (vysvetlíme si neskôr) ale najmä kľúč – podmienku, podľa ktorej sa riadky týchto dvoch tabuliek majú párovať, čiže podľa čoho máme priradiť (spojiť) riadok jednej tabuľky k riadku druhej tabuľky.

Momentálne máme prípad, že sa v tabuľke pre čísla nenachádza pre jedného používateľa viacej ako jedno číslo. Táto info je teraz dôležitá, neskôr si vysvetlíme prečo... Vráťme sa ale k príkazu JOIN, ktorému musíme nadefinovať podmienku prepojenia riadkov dvoch tabuliek. Podmienka sa zadáva tak, že sa určuje stĺpec z jednej a druhej tabuľky, ktorého hodnota by sa mala rovnať. V našom prípade to bude stĺpec id z tabuľky user a stĺpec id_user z tabuľky user_phone_numbers.

Ako sme povedali, smerov prepojenia je viac (zľava doprava, sprava doľava atď.), my na to použijeme LEFT JOIN (ľavé spojenie). Toto prepojenie sa vyznačuje tým, že nám vráti všetky riadky z ľavej tabuľky (v našom prípade users), čiže toľko záznamov, koľko sa v nej nachádza a z pravej nám vráti iba tie, ktoré tam majú hodnotu, v opačnom prípade nám vráti z pravej NULL. Syntax príkazu vyzerá nasledovne. Začíname štandardne príkazom SELECT s definovaním stĺpcov, ktoré vyťahujeme, za ktorým ide príkaz FROM s názvom tabuľky, z ktorej prioritne (naľavo) ťaháme dáta. Potom nasleduje príkaz LEFT JOIN, za ktorým nasleduje názov tabuľky, ktorú lepíme k našej prvej. Potom nasleduje kľúčové slovo ON (na – spojenie na), za ktorým nasleduje podmienka. Tá sa skladá z názvu tabuľky na ľavej strane, za ktorým nasleduje bodka spolu s názvom stĺpca, podľa ktorého lepíme. Ďalej znamienko rovná sa a podobne zápis stĺpca z pravej strany, s ktorým lepíme, čiže názov tabuľky, bodka a názov stĺpca. Náš príkaz pre vytiahnutie všetkých dát z oboch tabuliek by vyzeral takto:

SELECT users.id,users.user_name,users.user_surname,users.age,users.role,user_phone_numbers.phone_number FROM users
LEFT JOIN user_phone_numbers ON users.id=user_phone_numbers.id_user

Ako vidíte v ukážke, pri výpise stĺpcov, ktoré chceme vytiahnuť sme pre každý stĺpec definovali aj názov tabuľky, z ktorej stĺpec pochádza. Ak by sme totižto tento príkaz spustili bez tohto, len s vymenovaním stĺpcov, dostali by sme error, ktorý by hovoril, že stĺpec ID nie je jednoznačný. Tento error by sme dostali preto, pretože názov stĺpca ID sa nachádza aj v jednej aj v druhej tabuľke. Databáza by tým pádom nevedela, z ktorej tabuľky chceme daný stĺpec načítať, takže to musíme takto špecifikovať.

Po spustení tohto kódu dostaneme výpis prvej tabuľky tak ako predtým, ale k tomuto výsledku sa zobrazí aj stĺpec phone_number, ktorý nesie hodnoty z druhej tabuľky. Takto sme jednoducho naraz načítali dáta z dvoch tabuliek naraz, čiže riadky z prvej tabuľky (ľavej) sme spojili s riadkami druhej tabuľky podľa kľúča. Kľúč, ako sme si povedali, je že sa hodnota stĺpca id z prvej tabuľky musí rovnať hodnote stĺpca id_user z druhej tabuľky.

Čisto korektné a praktické použitie príkazu JOIN je možné iba v prípade, že obdive tabuľky obsahujú pre hlavný objekt (v našom prípade user) iba jeden riadok. Ak chceme vo výsledku, aby jeden riadok predstavoval iba jedného používateľa, o ktorom by boli dáta zaradom usporiadané podľa stĺpcov z obidvoch tabuliek, je to možné iba v takomto prípade. Ak by totižto v tabuľke s číslami bolo viacej záznamov pre jedného používateľa, dáta by sa nám rozvetvili podľa toho, koľko čísel sa nachádza pre každého používateľa. Aby sme pochopili, ako to funguje, pridajme si ešte nejaké záznamy do tabuľky pre tel. čísla:

INSERT INTO user_phone_numbers (id_user, phone_number) VALUES 
(1, '0949 111 234'),
(1, '0918 111 123'),
(2, '0911 222 555'),
(5, '0911 555 876');

Po vykonaní tohto príkazu sa v tabuľke user_phone_numbers nachádza pre jeden záznam z tabuľky users viacero záznamov (riadkov) – s tým istým id_user. To spôsobí to, že ak zavoláme select join teraz, tak vo výsledku nebude platiť pravidlo, že jeden riadok rovná sa jeden používateľ, pretože ako by to mala databáza spojiť do jedného riadku, keď v pravej tabuľke (čísla) sa nachádzajú záznamy napríklad pre id_user 1 tri. Databáza musí vrátiť všetky možné rôzne záznamy (spojenia), ktoré napárovala. Spustie si predšlý príkaz SELECT s JOINom, aby sme videli, o čom sa bavíme.

Ako vidíte, user s id 1 (Charlie Harper) sa nachádza za sebou trikrát s tými istými údajmi z prvej tabuľky ale s rôznymi z druhej. Dalo by sa to matematicky vyjadriť, že výsledný počet tohto selectu bude súčet násobení userov s ich rôznymi telefónnymi číslami, takže 1x3 (id 1) + 1x2 (id 2) + 1x1 (id 3) atď. Nie je technicky možné, aby tento select vrátil pre jeden riadok jedného používateľa tak, aby sme zachovali všetky dáta, pretože ako by mal tri rôzne hodnoty (tel. čísla) spojiť do jedného?

Aby sme tento prípad vyriešili čo sa týka nášho informačného systému, čiže aby sme mohli vytiahnuť tieto dáta a zobraziť ich v tabuľke, nie je to možné pomocou jedného SELECTu. Vyriešilo by sa to tak, že v momente, kedy dáta z tabuľky users prechádzame cyklom a vytvárame objekty triedy User, tak by sme pre každého používateľa museli volať select na tabuľku user_phone_numbers, kde by sme podľa id_usera vyberali iba jeho záznamy (podmienka vo WHERE klauzule). Práve pre toto je dôležité uvedomiť si pred vytváraním tabuľky a stĺpcov, koľko hodnôt môže čo obsahovať a takisto ako tieto dáta v informačnom systéme získame (či naraz JOINom alebo viacerými SELECTami v cykle).