Rad sa bazom podataka u Drupal 7 - lekcija 4 - Dinamički upiti (SELECT)

Verovatno smo stigli do najzanimljivijeg dela Drupal Database API-ja: dinamički upiti. Dinamički upiti se tako zovu jer Drupal dinamički dodaje delove upita. Svi INSERT, UPDATE, DELETE ili MERGE upiti mogu biti dinamički. SELECT upiti mogu biti i dinamički i statički. Ipak, preporučuje se korišćenje dinamičkih upita i za SELECT upite.

Svi dinamički upiti se kreiraju kao objekat-upit koji se izvršava kada je potrebno. Kao i kod statičkih upita, većina upita koristi proceduralne omotače (procedural wrapper). Svi naredni parametri upita mogu se dodavati u objekat-upit.

db_select() SELECT upiti

Dinamički SELECT upiti započinju funkcijom db_select(), na primer:

<?php
$query = db_select('node', 'n', $options);
?>

U ovom primeru "node" je tabela na koju se referencira, što u SQL-u odgovara delu posle FROM. Imajte na umu da ime tabele nije stavljeno u viticaste zagrade kao ranije. Konstruktor upita automatski će obaviti to umotavanje. Drugi parametar je alias (pseudonim) tabele. Ako nije naveden, koristiće se ime tabele. Niz $options je opcionalan i isti je kao i u statičkim upitima.

Dinamički SELECT upiti mogu biti vrlo jednostavni ili veoma složeni. Mi ćemo proučiti osnovne principe rada sa njima, ali postoji mnogo za naučiti o njima.

Još jedan primer db_select()

Ovde je još jedan relativno jednostavan SELECT upit iz tabele korisnika. Kasnije ćemo proučiti dodatne karakteristike SELECT upita i složenije upite sa JOIN-ovima.

<?php
$query = db_select('users', 'u');

$query
  ->condition('u.uid', 0, '<>')
  ->fields('u', array('uid', 'name', 'status', 'created', 'access'))
  ->range(0, 50);

$result = $query->execute();
?>

Primer iznad je ekvivalentan sledećem SQL upitu:

$result = db_query("SELECT uid, name, status, created, access FROM {users} u WHERE uid <> 0 LIMIT 50 OFFSET 0");

Ovo je pojednostavljena verzija upita korišćenog na stranici za administraciju korisnika, dobar primer za dalje proučavanje.

JOIN-ovi (spajanja)

Za spajanje sa drugom tabelom koriste se metode join(), innerJoin(), leftJoin() ili rightJoin(), kao na primeru:

<?php
$table_alias = $query->join('user', 'u', 'n.uid = u.uid AND u.uid = :uid', array(':uid' => 5));
?>

U ovom primeru dodajemo INNER JOIN (podrazumevani tip join-a) sa tabelom "user" sa aliasom "u". Uslov spajanja (ON) je "n.uid = u.uid AND u.uid = :uid" gde :uid ima vrednost 5. Koriste se zamenjivači (:uid) da bi se izbegle SQL injekcije. Nikada nemojte direktno ubacivati promenljive u upit, isto kao i kod statičkih upita. Metode innerJoin(), leftJoin() i rightJoin() rade odgovarajuće tipove JOIN-ova.

Metoda join vraća alias tabele na koju je vezan. Ako je alias već korišćen (retko), Drupal će dodeliti drugi alias.

Imajte na umu da se ime tabele kao "user" navodi u prvom argumentu svih join-ova.

<?php
$myselect = db_select('mytable')
  ->fields('mytable')
  ->condition('myfield', 'myvalue');
$alias = $query->join($myselect, 'myalias', 'n.nid = myalias.nid');
?>

Polja (Fields, addField)

Za dodavanje polja u SELECT upit koristite metodu addField():

<?php
$title_field = $query->addField('n', 'title', 'my_title');
?>

Ovaj kod dodaje polje "title" iz tabele sa aliasom "n" i dodeljuje polju alias "my_title". Ako alias nije naveden, automatski će se generisati. U većini slučajeva, generisani alias je samo ime polja (ovde "title"). Ako alias već postoji, koristiće se kombinacija imena tabele i polja (npr. "n_title"). Ako ni to postoji, dodaće se broj na kraj (npr. "n_title_2").

Ako kreirate upit i ne definišete alias, a standardni nije dostupan, znači da postoji greška u kodu. Ako pravite implementaciju hook_query_alter() i niste sigurni u alias, koristite standardno ime aliasa.

Da biste dodali više polja odjednom, koristite metod fields():

<?php
$query->fields('n', array('nid', 'title', 'created', 'uid'));
?>

Ovaj poziv je ekvivalentan četiri poziva addField() po jednom za svako polje. Međutim, fields() ne podržava alias-e polja. Takođe vraća objekat upita, pa će se svi aliasi generisati automatski. Ako vam trebaju aliasi, koristite addField().

Pozivanje fields() bez drugog argumenta znači "SELECT *".

<?php
$query->fields('n');
?>

Ovo će uključiti "n.*" u listu polja. Imajte na umu da nije kreiran alias. Ako tabela u SELECT * ima polje koje se pojavljuje i u drugoj tabeli, može doći do sukoba. U tom slučaju rezultat može sadržati samo jedno od tih polja, pa je preporučljivo izbegavati SELECT *.

DISTINCT

Neki SQL upiti mogu vraćati duplikate. Da biste ih uklonili, u statičkim upitima koristite DISTINCT, a u dinamičkim metod distinct():

<?php
// Uklanja duplikate iz rezultata.
$query->distinct();
?>

Zapamtite da DISTINCT može biti skup za performanse, pa ga nemojte koristiti bez potrebe.

Expressions (izrazi, addExpression)

Konstruktor SELECT upita podržava dodavanje izraza u listu polja. Primeri su "dvostruko polje starosti", "brojanje imena" ili podstring naslova. Mnoge SQL funkcije nisu standardne za sve baze, pa programer modula treba da se pobrine za kompatibilnost izraza.

Za dodavanje izraza koristite addExpression():

<?php
$count_alias = $query->addExpression('COUNT(uid)', 'uid_count');
$count_alias = $query->addExpression('created - :offset', 'uid_count', array(':offset' => 3600));
?>

Prvi parametar je SQL izraz, drugi alias polja. Ako alias već postoji, biće generisan novi alias. Ako nije naveden, generisaće se "expression" ili "expression_2", itd.

Treći opcioni parametar je asocijativni niz zamenjivača za vrednosti unutar izraza. Neki izrazi mogu biti beskorisni ako se koristi GROUP BY.

ORDER BY (Sortiranje, orderBy)

Za dodavanje sortiranja u upit koristite orderBy():

<?php
$query->orderBy('title', 'DESC');
?>

Ovaj primer sortira po polju "title" u opadajućem redosledu. Drugi parametar može biti i ASC (rastući), što je i podrazumevano. Ime polja treba da bude alias koji ste prethodno definisali. Za sortiranje po više polja, pozovite orderBy() više puta.

Nasumičan redosled (orderRandom)

Nasumični redosled zavisi od baze i sintakse, pa se najbolje formira dinamički.

Da biste zahtevali nasumičan redosled, pozovite orderRandom():

<?php
$query->orderRandom();
?>

orderRandom() može raditi zajedno sa orderBy(), kao u sledećem primeru:

<?php
$query->orderBy('term')->orderRandom()->execute();
?>

Prvo se vrši sortiranje po "term", zatim nasumično.

GROUP BY (Grupisanje, groupBy)

Za grupisanje po polju koristite groupBy():

<?php
$query->groupBy('uid');
?>

Ovo grupiše rezultate po polju "uid". Polje mora biti alias koji ste prethodno dodali. Grupisanje po više polja se radi pozivom groupBy() više puta.

Range i limit (Opseg i ograničenja, range)

Upiti se mogu ograničiti na određeni broj rezultata pomoću range(). U MySQL to je LIMIT.

<?php
$query->range(5, 10);
?>

Ovaj kod uzima 10 zapisa počevši od šestog (indeksi počinju od 0). Ponovni poziv range() menja prethodne vrednosti. Poziv bez parametara nema efekta.

Sortiranje tabele (extend)

Rezultate možete sortirati po koloni koristeći ekstenziju TableSort koja dodaje zaglavlja kolona:

<?php
$query = $query
  ->extend('TableSort')
  ->orderByHeader($header);
?>

Uslovi (condition)

Uslovi su kompleksna tema koju ćemo pokriti kasnije. Postoje dva tipa uslova za SELECT upite: WHERE i HAVING. Ponašanje im je slično. Za WHERE koristite condition() ili where(), za HAVING koristite havingCondition() ili having().

Izvršavanje upita (execute)

Da biste izvršili upit, pozovite execute():

<?php
$result = $query->execute();
?>

execute() vraća skup rezultata, isti kao i db_query(), koji možete obraditi na poznate načine:

<?php
$result = $query->execute();
foreach ($result as $record) {
  // Obrada svakog $record
}
?>

• fetchField()
• fetchAllKeyed()
• fetchCol()

Ove metode zahtevaju indekse u alias-ima tabela, ali konstruktor upita to ne garantuje uvek.

Brojanje rezultata

Bilo koji upit može vratiti broj redova. Da biste dobili broj, koristite countQuery():

<?php
$count_query = $query->countQuery();
?>

$count_query je novi SELECT upit bez sortiranja, koji kada se izvrši vraća broj redova koji odgovaraju originalnom upitu. PHP podržava metod lancanja (method chaining), pa se često piše:

<?php
$num_rows = $query->countQuery()->execute()->fetchField();
?>

Debug

Da biste proverili SQL upit koji će biti izvršen, pozovite __toString() metod:

<?php
print_r($query->__toString());
?>