Sortowanie pobieranych elementów z dql według zadanego zbioru

Napisano: 16 sierpnia 2010 20:30
Tagi: doctrine php dql sql where in order by

Czasem zdarza się, że chcielibyśmy pobrać z bazy danych elementy z określonego zbioru, a także je posortować według kolejności, w jakiej w tym zbiorze się znajdują. Samo zadanie skomplikowane nie jest, aczkolwiek nie jest również takie proste. Poniżej zaprezentuję podejście do problemu, oraz pułapki, które mogą nas spotkać podczas rozwiązywania tego problemu.

Pobranie z bazy danych elementów z określonego zbioru jest proste do wykonania, zarówno w sql:

MySQL code
  1. WHERE IN (3,1,2)

jak i w dql:

PHP code
  1. $query->whereIn( 'id', array( 3, 1, 2 ) );

Takie pytanie niestety zwróci nam te trzy elementy w kolejności, w jakiej znajdują się w bazie, lub posortowane według wartości któregoś z atrybutów (rosnąco / malejąco). Posiadamy więc elementy, które chcieliśmy, ale nie są one ustawione tak jak chcieliśmy.

Zacznijmy jednak od stworzenia dql, które umożliwi nam właśnie pobranie elementów, które chcieliśmy (posortowanie ich tak jak chcieliśmy nastąpi później):

PHP code
  1. class MyModelTable extends Doctrine_Table
  2. {
  3.         /**
  4.         * Returns elements based on given ids
  5.         * @param array $element_ids
  6.         * @return Doctrine_Query
  7.         */
  8.         public function getElementsByIdsQuery( $element_ids )
  9.         {
  10.                 return $this->createQuery( 'e' )
  11.                         ->select( 'e.*' )
  12.                         ->whereIn( 'e.id', $element_ids );
  13.         }
  14.  
  15. }

To zapytanie zapewnia podstawę, którą możemy wykorzystać bez konieczności przechodzenia przez następny krok. Do posortowania elementów wykorzystamy instrukcję warunkową CASE:

PHP code
  1. class MyModelTable extends Doctrine_Table
  2. {
  3.  
  4.         /**
  5.         ...
  6.         */
  7.  
  8.         /**
  9.         * Returns elements based on given ids, ordered as in given array.
  10.         * @param array $element_ids
  11.         * @return Doctrine_Query
  12.         */
  13.         public function getElementsByIdsOrderedQuery( $element_ids )
  14.         {
  15.                 $query = $this->getElementsByIdsQuery($element_ids);
  16.                 $orderCASE = '';
  17.                 foreach( $element_ids as $order => $id )
  18.                 {
  19.                         $orderCASE .= 'WHEN '.$order.' THEN '.$id.' ';
  20.                 }
  21.  
  22.                 return $query->addSelect( '(CASE e.id '.$orderCASE.' ELSE 0 END) as order' )
  23.                          ->orderBy( 'order ASC' );
  24.         }
  25. }

Tak otrzymany dql zwraca nam obiekty ustawione w takiej samej kolejności, jak w zadanej tablicy identyfikatorów.

Możemy się jednak spodziewać wystąpienia paru błędów i niezamierzonych działań naszego kodu.

  • W przypadku braku elementów w tablicy $element_ids (pusta tablica), zapytanie podstawowe zwróci nam wszystkie elementy z tabeli.
  • Natomiast próba wykonania DQL z metody getElementsByIdsOrderedQuery zwróci nam błąd, gdyż w przypadku braku elementów w tablicy $element_ids addSelect będzie wyglądał następująco:
    PHP code
    1. '(CASE e.id  ELSE 0 END) as order'

O ile pierwszy problem, zależnie od intencji programisty może być wynikiem pożądanym, o tyle drugi problem nie ucieszy nikogo.

Zabezpieczyć się przed tym drugim problemem można w następujący sposób:

PHP code
  1. if( count( $element_ids ) > 0 )
  2. {
  3.         $orderCASE = '';
  4.         foreach( $element_ids as $order => $id )
  5.         {
  6.                 $orderCASE .= 'WHEN '.$order.' THEN '.$id.' ';
  7.         }
  8.  
  9.         return $query->addSelect( '(CASE e.id '.$orderCASE.' ELSE 0 END) as order' );
  10. }

W tym wypadku, jeśli tablica będzie pusta, do zapytania dql nie zostanie dodana instrukcja case. Należy jednak zwrócić uwagę na brak w tej pętli instrukcji orderBy. Została ona pominięta celowo, by zwiększyć elastyczność rozwiązania i zademonstrować kolejną pułapkę.

Elastyczność polega na możliwości zmiany sortowania dynamicznie. Można zmienić kierunek, lub nawet atrybut, po którym sortujemy.

Natomiast zawarta pułapka w momencie przekazania pustej tablicy i jednoczesnej próbie posortowania po atrybucie order (którego po prostu nie będzie) doprowadzi do wystąpienia błędu bazy danych. By pozbyć się tego ostatniego błędu, należy dodać atrybut order z ustawioną na sztywno wartością:

PHP code
  1. else
  2. {
  3.         $query->addSelect( '\'1\' as order' );
  4. }
Share
Grzegorz Śliwiński

Komentarze (2)

jckll 21 sierpnia 2010 16:19 #1 replyreport
Gravatar photo
Wywazasz otwarte drzwi ;) MySql (ktorego przyklad podajesz) obsluguje swietnie skladnie ORDER BY FIELD, tak wiec mozna zadany efekt uzyskac prosto z samej bazy. Nie wiem na ile DQL dokladnie odzwierciedla MySqla, ale na pewno mozna zrobic to po prostu generujac bezposrednie zapytanie (i tak generujesz), ktore w Twoim przypadku wygladac bedzie: SELECT * FROM tabela WHERE id IN (3,1,2) ORDER BY FIELD(id, 3,1,2). Zlozonosc stala, a nie liniowa, do tego wbudowana struktura jezykowa w ORDER jest na pewno szybsza niz uzywanie CASE ;)
fizyk 21 sierpnia 2010 20:25 #2 replyreport
Gravatar photo
Szczerze mówiąc, nie widziałem nigdy wcześniej tej funkcji. Jednak przede wszystkim jest to funkcja typowa dla MySQL, nie mogłem znaleźć nawet odpowiednika dla np. PostgreSQL.
Doctrine rzeczywiście nam zaakceptuje użycie funkcji FIELD, więc o ile nasza aplikacja będzie opierała się tylko o MySQL, użycie tej funkcji jest rzeczywiście lepsze, niż CASE.
A co do powoływania się na MySQL... nie dorobiłem się jeszcze kolorowania dla zwykłego SQL.
Back to topBack to top
Skomentuj!

Adres email nie zostanie opublikowany

Musi zaczynać się od http:// lub https://
Usuń odpowiedź