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.