Łączenie zapytań Zend_Db_Select w Zend Framework [cz. 1/2]

Ostat­nio w ramach spor­tów wyczy­no­wych majstruję trochę z zapy­ta­niami do bazy danych w Zendzie. Spraw­dzam co można wyci­snąć z obiek­tów Zend_Db_Select. Ten wpis zdecy­do­wa­łem się podzie­lić na dwie części. W pierw­szej pokażę jak wyge­ne­ro­wać dosyć złożone zapy­ta­nie do bazy danych za pomocą kilku obiek­tów Zend_Db_Select, a w drugiej zajmiemy się stwo­rze­niem formu­la­rza i odebra­niem danych.

Naszym celem będzie utwo­rze­nie alfa­be­tycz­nej listy poli­ty­ków, których wszy­scy lubimy wraz z ich powszech­nie znanymi pseu­do­ni­mami, np.

<select>
<option value="1">Donald Tusk</option>
<option value="2">Jareczek (Jarosław Kaczyński)</option>
<option value="3">Jarosław Kaczyński</option>
<option value="4">Słońce Peru (Donald Tusk)</option>
</select>

Najpierw musimy mieć skąd brać naszych wybrań­ców narodu. Stwo­rzymy sobie bazę danych mniej więcej taką:

Obra­zek bazy danych poli­ty­ków zrobi­łem fajnym narzę­dziem online WWW SQL Desi­gner. Sche­mat jest oczy­wi­ście maksy­mal­nie uprosz­czony. Nie czepiać się, że imiona i nazwi­ska trzy­mam w jednym polu. Chodzi nam o rela­cję jeden poli­tyk ma wiele pseu­do­ni­mów. Macie poni­żej trochę kodu SQL wyeks­por­to­wa­nego przez phpMy­Ad­min plus kilka przy­kła­do­wych wartości.

CREATE TABLE IF NOT EXISTS `politycy` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `imie_nazwisko` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='tabela zawierajaca imiona i nazwiska politykow' AUTO_INCREMENT=3 ;
 
INSERT INTO `politycy` (`id`, `imie_nazwisko`) VALUES
(1, 'Donald Tusk'),
(2, 'Jarosław Kaczyński');
 
CREATE TABLE IF NOT EXISTS `politycy_ksywki` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ksywka` VARCHAR(20) NOT NULL,
  `polityk_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `polityk_id` (`polityk_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='zawiera popularne pseudonimy politykow' AUTO_INCREMENT=3 ;
 
INSERT INTO `politycy_ksywki` (`id`, `ksywka`, `polityk_id`) VALUES
(1, 'Słońce Peru', 1),
(2, 'Jareczek', 2);
 
ALTER TABLE `politycy_ksywki`
  ADD CONSTRAINT `politycy_ksywki_ibfk_1` FOREIGN KEY (`polityk_id`) REFERENCES `politycy` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Następ­nie spró­bu­jemy sobie wyge­ne­ro­wać dosyć spore zapy­ta­nie SQL, które za jednym zama­chem wypi­sze wszyst­kich poli­ty­ków, wszyst­kie pseu­do­nimy i dodat­kowo w nawia­sie wypi­sze którego poli­tyka który pseu­do­nim doty­czy. Wszystko oczy­wi­ście w Zend Framework.

// adapter bazy umieszczony w rejestrze
$dbAdapter = Zend_Registry::get('db');
$select1 = $dbAdapter->select()
   // podstawowa tabela z politykami
   ->from(array('p' => 'politycy'),
          // ktore pola (lacznie z aliasem)
          array('p.id','p.imie_nazwisko'));
// podzapytanie dla ksywek
$subSelect = $dbAdapter->select()
   ->from(array('pp' => 'politycy'),
          array('pp.imie_nazwisko'))
   // warunek dla podzapytania
   ->where('pp.id = pk.polityk_id')
   // powinno przyspieszyc duze bazy
   ->limit(1);
$select2 = $dbAdapter->select()
   // tabela z ksywkami
   ->from(array('pk' => 'politycy_ksywki'),
          array('pk.polityk_id',
                // Zend_Db_Expr konieczny przy wywolywaniu funkcji SQL
                new Zend_Db_Expr("CONCAT(pk.ksywka, ' (', ({$subSelect}) , ')')")));
$selectUnion = $dbAdaptery->select()
   // konstruujemy zapytanie typu union
   ->union(array($select1, $select2))
   // szeregujemy wyniki
   ->order('imie_nazwisko ASC');
$stmt = $select->query();
$rowset = $stmt->fetchAll();

Powyż­szy kod stwo­rzył niczego sobie zapytanie:

SELECT `p`.`id`, `p`.`imie_nazwisko`
   FROM `politycy` AS `p`
UNION SELECT `pk`.`polityk_id`,
   CONCAT(pk.ksywka, ' (', (SELECT `pp`.`imie_nazwisko` FROM `politycy` AS `pp` WHERE (pp.id = pk.polityk_id) LIMIT 1) , ')')
   FROM `politycy_ksywki` AS `pk` ORDER BY `imie_nazwisko` ASC

Zmienna $rowset zawiera wynik zapy­ta­nia w postaci tablic:

array(4) {
  [0] => array(2) {
    ["id"] => string(1) "1"
    ["imie_nazwisko"] => string(11) "Donald Tusk"
  }
  [1] => array(2) {
    ["id"] => string(1) "2"
    ["imie_nazwisko"] => string(31) "Jareczek (Jarosław Kaczyński)"
  }
  [2] => array(2) {
    ["id"] => string(1) "2"
    ["imie_nazwisko"] => string(20) "Jarosław Kaczyński"
  }
  [3] => array(2) {
    ["id"] => string(1) "1"
    ["imie_nazwisko"] => string(27) "Słońce Peru (Donald Tusk)"
  }
}

Na koniec uwaga: obiekty typu Zend_Db_Select są przy­datne tylko wtedy, gdy nasze zapy­ta­nie nie ma charak­teru stałego, tzn. różne czyn­niki wpły­wają na jego kształt, przez co musi być tworzone w locie. Jeżeli wiemy, że zapy­ta­nie zawsze będzie takie samo to jest to zwykłe marno­traw­stwo zaso­bów serwera, aczkol­wiek ładnie wygląda i szybko się pisze.

Aktu­ali­za­cja (3.2.2011)

Skoro mnie zmusi­li­ście to poni­żej najbar­dziej poprawne wg mnie zapy­ta­nie zała­twia­jące sprawę.:

$select1 = $dbAdapter->select()
   ->from(array('p' => 'politycy'),
      array('p.id','p.imie_nazwisko'))
   ->joinLeft(array('pk' => 'politycy_ksywki'),
      'p.id = pk.polityk_id',
      array('pk.ksywka'));

Powyż­sze zapy­ta­nie wygeneruje

SELECT `p`.`id`, `p`.`imie_nazwisko`, `pk`.`ksywka` 
FROM `politycy` AS `p`
LEFT JOIN `politycy_ksywki` AS `pk`
ON p.id = pk.polityk_id

Które z kolei zwróci wyniki

array(5) {
  [0] => array(3) {
    ["id"] => string(1) "1"
    ["imie_nazwisko"] => string(11) "Donald Tusk"
    ["ksywka"] => string(13) "Słońce Peru"
  }
  [1] => array(3) {
    ["id"] => string(1) "1"
    ["imie_nazwisko"] => string(11) "Donald Tusk"
    ["ksywka"] => string(11) "Rudy Oszust"
  }
  [2] => array(3) {
    ["id"] => string(1) "2"
    ["imie_nazwisko"] => string(20) "Jarosław Kaczyński"
    ["ksywka"] => string(8) "Jareczek"
  }
  [3] => array(3) {
    ["id"] => string(1) "3"
    ["imie_nazwisko"] => string(18) "Antoni Macierewicz"
    ["ksywka"] => NULL
  }
  [4] => array(3) {
    ["id"] => string(1) "4"
    ["imie_nazwisko"] => string(20) "Stefan Niesiołowski"
    ["ksywka"] => NULL
  }
}

Podobne wpisy:

  1. Łącze­nie zapy­tań Zend_Db_Select w Zend Frame­work [cz. 2/2]
  2. Własny wali­da­tor w Zend Framework
  3. Zend Frame­work i podświe­tla­nie wybra­nego elementu menu
  4. Zend_Date i Zend_Config w Zend Framework

5 Comments

  • SebaZ
    25 stycznia 2011 - 11:06 | Permalink

    Tylko dlaczego UNION SELECT do takiego zapy­ta­nia? LEFT JOIN nie jest znane ?

  • Quasar
    26 stycznia 2011 - 00:17 | Permalink

    // powinno przy­spie­szyc duze bazy
    ->limit(1);”

    Nie przy­spie­szy bo mysql pobiera wszyst­kie rekordy spel­nia­jace waru­nek, a pod koniec je odcina i zosta­wia pierw­szy (z reszta i tak zwroci tylko jeden rekord).

  • Śpiechu
    26 stycznia 2011 - 07:55 | Permalink

    @SebaZ
    Bardziej chodziło mi o poka­za­nie współ­pracy pomię­dzy obiek­tami Select. LEFT JOIN jest znane, ale mało używane :-) Może w ramach aktu­ali­za­cji wpisu napi­szę jak powinno być poprawnie.

    @Quasar
    A to nowość trochę dla mnie. W kilku miej­scach prze­czy­ta­łem, że tam gdzie spodzie­wamy się konkret­nej liczby wyni­ków powinno się limi­to­wać. Być może Post­gre­SQL jest na tyle cwany, że potrafi sobie zopty­ma­li­zo­wać zapytanie.

  • 2 lutego 2011 - 14:57 | Permalink

    @Quasar
    więc jeśli tabela ma 20 mln rekor­dów to LIMIT nic nie pomoże, bo MySQL zawsze pobiera wszyst­kie rekordy i dopiero potem odcina wg limi­tów? hehe, dobre. mógł­byś pode­przeć tą nowinkę jakimś źródłem?

    btw. nawet jeśli by tak miało być (w co nie wierzę) to warto stoso­wać LIMIT by silnik SQL faktycz­nie odciął niepo­trzebne rekordy, a nie jesz­cze prze­ka­zy­wał je do serwera PHP i zawa­lał pamięć podręczną i zajeż­dżał serwer.

    @Śpiechu
    W powyż­szym przy­kła­dzie faktycz­nie wystar­czyło zwykłe złącze­nie po kluczach, nie trzeba było UNION, który to dopiero wydłuża opera­cje na tabelach.

    Niemniej dobrze zrobi­łeś pisząc z UNION, ponie­waż poka­za­łeś jak to rozwią­za­nie wygląda w Zendzie.

  • ishi
    8 lutego 2011 - 09:54 | Permalink

    @Kamil
    LIMIT jest wyko­ny­wany prak­tycz­nie na końcu, dlatego przy 20mln rekor­dów wszyst­kie inne opera­cje będą wyko­nane według kolej­no­ści a na samym końcu limit (działa on na goto­wym wyniku). Zaosz­czę­dzi to jedy­nie łącza sieciowego ;)

  • Dodaj komentarz

    Twój adres e-mail nie zostanie opublikowany.

    Możesz użyć następujących tagów oraz atrybutów HTML-a: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>