doctrine Doctrine Using DBMS functions with sfDoctrine

This article was written for early PHP-Doctrine versions (1.0 or before).

It may not be useable with recent doctrine versions (and most probably not at all with Doctrine 2.0).

I recently had a peek on symfony forum, and seen someone asking "How can I make a SELECT count(*) FROM .... with doctrine?".

An answer to such a question should be pretty obvious as it's of everyday use, but the question seems to have come to life many times as well on IRC than on the forum/mailing list.

Here is my two-cents how-to.

Using builtin DQL aggregate functions

Basically, you can add aggregate functions (functions that operates on a group of records instead of one record, also called GROUP BY functions) to your DQL query the same way you'd do in SQL, provided the fact you're using one of the builtin DQL aggregate functions (COUNT, MAX, MIN, AVG, SUM).

<?php
$result = Doctrine_Query::create()
     ->select('COUNT(t.id) cnt')
     ->from('Table t')
     ->execute()
     ->getFirst();

echo $result['cnt'];

Using DBMS specific aggregate functions

If the function you need is in the list, no need to go further. But some functions are DBMS specific, for example MySQL provides a GROUP_CONCAT() function. It would be nonsense for DQL to provide that, as it would not be translatable in some of the other DBMS SQL, but it would also be nonsense to restrict functionalities to the smallest common functionnalities set of every DBMS that Doctrine supports.

The way to go is to change the portablility level of doctrine, to match your needs. As the name implies, you're loosing in portability between the different DBMS, but you're gaining specific functionnalities of yours.

The following code, running in PORTABILITY_ALL mode, will throw a Doctrine_Query_Exception:

<?php
Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL);

$result = Doctrine_Query::create()
     ->select('GROUP_CONCAT(t.value) concatedstring')
     ->from('Table t')
     ->execute()
     ->getFirst();

Now remove the Doctrine::PORTABILITY_EXPR bit to this attribute, and you will get the correct result (using MySQL):

<?php
Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL ^ Doctrine::PORTABILITY_EXPR);

$result = Doctrine_Query::create()
     ->select('GROUP_CONCAT(t.value) concatedstring')
     ->from('Table t')
     ->execute()
     ->getFirst();

echo $result['concatedstring'];

Extending to other functions

Ok aggregate functions are usefull, but other functions can be very handy too, string functions for example. Doctrine manual tells us that builtin DQL string functions are CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LOCATE and LENGTH.

<?php
Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL);

$results = Doctrine_Query::create()
     ->select('LENGTH(t.value) val_len, t.value')
     ->from('Test t')
     ->execute();

foreach ($results as $result)
{
  echo $result['value'].' => '.$result['val_len']."\n";
}

This code will display all records' value fields, followed by their character length computed by the DBMS, in PORTABILITY_ALL mode.

Now let's say you want to use BIT_LENGTH() MySQL function...

<?php
Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL ^ Doctrine::PORTABILITY_EXPR);

$results = Doctrine_Query::create()
     ->select('BIT_LENGTH(t.value) val_len, t.value')
     ->from('Test t')
     ->execute();

foreach ($results as $result)
{
  echo $result['value'].' => '.$result['val_len']."\n";
}

This is the way to go again.

No need to say, this extends to every type of functions available in the different DBMS.

Share the love!

Liked this article? Please consider sharing it on your favorite network, it really helps me a lot!

You can also add your valuable insights by commenting below.