Doctrine DQL or SQL

Developers that use Doctrine are often puzzled on what should they use for SELECT queries - DQL or SQL?
Short answer: both.

Long answer:
Official Doctrine documentation here says:

 A common mistake for beginners is to mistake DQL for being just some form of SQL and therefore trying to use table names and column names or join arbitrary tables together in a query. You need to think about DQL as a query language for your object model, not for your relational schema.

 That page clearly tells us: don't use DQL to built complicated and heavy queries.
But what about database independent code? Query building? You might ask...
For former question Doctrine has whole DBAL layer located in Doctrine\DBAL namespace, for latter it has Doctrine\DBAL\Query\QueryBuilder which builds pure SQL code in similar way as Doctrine\ORM\QueryBuilder builds DQL and A LOT! of developers don't even aware of its existence!

$qb = $this->connection->createQueryBuilder();
$qb->from('shipment', 's')
    ->where('s.shop_id = :shop_id')
    ->andWhere('s.shipment_status_time BETWEEN :from AND :to')
    ->setParameter('shop_id', $this->shop->getId())
    ->setParameter('from', $this->from, Type::DATE)
    ->setParameter('to', $this->to, Type::DATE)
;
if ($this->carrier) {
    $qb->andWhere('s.carrier_id = :carrier_id')
        ->setParameter('carrier_id', $this->carrier->getId());
}
if ($this->country) {
    $qb->andWhere('s.customer_address_country_id = :county_id')
        ->setParameter('county_id', $this->country->getId());
}

Believe me, I saw a few projects (including one that worth millions and has dozens of developers working on it!) where people do not even know that SQL builder exists and that according to the official documentation it is preferably to avoid build complex queries, but still, they write huge and slow running queries!

So my philosophy here is:

  • if you are not going to migrate to noSQL database it is quite possible to write DBAL query builder code that can be portable between SQL database platform
  • almost any project at some point of its existence will require to write complex queries that require complex joins, grouping etc. So unless you are intended to migrate to completely different database engine - use SQL builder!
  • it is very unlikely that even sticking only to DQL you will not experience any complications after database engine change
  • DQL is very handy to write simple queries and get resulting object tree
  • ask yourself - is query complex and requires arbitrary joins? If yes - use SQL query builder and if you need entities as a result of a query you can hydrate SQL query to entities using ResultSetMapping
  • do you need entities or just pure data? If pure data only - use SQL query builder


Comments

  1. UnknownJune 29, 2016 at 6:58 AM

    DQL in practice not so good
    we often kick put DQL and write that we need in SQL
    any AS clause in DQL select broke DQL at all

    ReplyDelete

Post a Comment

Popular posts from this blog

Memory efficient array permutation in PHP 5.5 using generators

Generators support was introduced in PHP 5.5. The yield keyword is a really cool thing that allows to write an elegant and memory efficient code. It was existing in another dynamic languages like python for quite a long time and now it is in PHP as well! To demonstrate generators concept, i wrote a sample function that  yields  all permutations of the given array and avoids of data copying on recursive calls: function permute( $array ) { $length = sizeof( $array ); $inner = function ( $ix = []) use ( $array , $length , & $inner ) { $yield = sizeof( $ix ) == $length - 1 ; for ( $i = 0 ; $i < $length ; $i ++) { if (in_array( $i , $ix )) { continue ; } elseif ( $yield ) { $toYield = []; foreach (array_merge( $ix , [ $i ]) as $index ) { $toYield [] = $array [ $index ]; } yield $toYield ; ...
Read more

How to dump http request headers with PHP under CGI/FastCGI SAPI

Good old days of PHP installation exclusively as an Apache module are gone. Most of the modern PHP installations interact with a web server via FastCGI protocol. For instance nginx webserver and php-fpm daemon. This means that certain apache SAPI functions are not available anymore. One of this functions is apache_request_headers() to dump http headers sent by client. From CGI specification rfc3875, section 4.1.18: Meta-variables with names beginning with "HTTP_" contain values read from the client request header fields, if the protocol used is HTTP. The HTTP header field name is converted to upper case, has all occurrences of "-" replaced with "_" and has "HTTP_" prepended to give the meta-variable name. According to the specification above i wrote a piece of the PHP code that dumps all headers sent by client under any PHP SAPI: foreach ($_SERVER as $key => $value) { if (strpos($key, 'HTTP_') === 0) { $chunks = e...
Read more

Jqgrid show totals in the footer plugin

This plug-in automatically calculates sum of the all numeric rows in your grid and displays it in the new "Totals" row at the bottom of the grid. jqgrid 4.3.2+ is required. (function($) { $.jgrid.extend({ showTotals: function(options) { var o = $.extend({ total: "Totals", totalRow: undefined, includeRows: [] }, options || {}) return this.each(function() { if (!this.grid) { return; } var self = this; var showTotals = function() { var totals = {}; $.each(self.p.colModel, function(index, data) { if (o.totalRow && o.totalRow == data.index) { totals[data.index] = o.total; } else { var sum = NaN; if (...
Read more