Using DBMS functions with sfDoctrine

Romain — Tue, 30 Oct 2007 22:11:00 +0100

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).

$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:

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 (with mySQL):

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.

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...

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.

Symfony Resources Central - MySQL

Using DBMS functions with sfDoctrine

Using builtin DQL aggregate functions

Using DBMS specific aggregate functions

Extending to other functions