Aggregate Values ​​in Doctrine_RawSql Queries

Question

Can I use aggregated values ​​in a Doctrine_RawSql query? Here is what I am trying to do:

$q = new Doctrine_RawSql(); $q->select('{q.*}, AVG(a.value) AS avg'); $q->from('-- complex from clause'); $q->addComponent('q', 'Question'); 

However, the SQL generated by Doctrine only leaves the columns from the question table and omits the aggregated avg value.

Answer 1

I had never used Doctrine_RawSql before, but I was making raw SQL queries through Doctrine using either of these two methods:

 Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc("YOUR SQL QUERY HERE"); 

and

 $doctrine = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh(); $result = $doctrine->query('YOUR SQL QUERY HERE'); 

These two methods seem to leave your original SQL intact.

I should note that I use Doctrine 1.2 in the context of Symfony 1.4 applications, but AFAIK, this will work for you, no matter what other frameworks you can use.

Answer 2

Have you looked at the aggregate doctrine cookbook section? They use the createQuery method for the object manager, and not for RawSql objects.

I do not understand the doctrine, but it can be a good start!

Answer 3

Try putting the aggregate field in SQL and then extract it using curly braces. I am using an SQL subquery.

 $q = new Doctrine_RawSql(); $q->select('{s.*}, {s.avg} AS avg'); $q->from('(SELECT q.*, AVG(value) AS avg FROM Question AS q) AS s'); $q->addComponent('s', 'Question'); 

This works for me.