4

Is it possible to use aggregate values in Doctrine_RawSql query? Here's what I'm 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, SQL created by Doctrine leaves only columns from table question and omits aggregate value avg.

Sandeepan Nath
  • 9,966
  • 17
  • 86
  • 144
Jan Dudek
  • 807
  • 1
  • 9
  • 17

3 Answers3

6

I've never used Doctrine_RawSql before, but I have done 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');

It seems like these two methods would leave your original SQL intact.

I should note that I'm using Doctrine 1.2 within the context of Symfony 1.4 applications, but AFAIK, this would work for you regardless of what other frameworks you may be using.

Steven Mercatante
  • 24,757
  • 9
  • 65
  • 109
  • I opted to use the first solution, as I had to link two tables via a value which may exist in one and not in the other, using a LOJ. But I also had a where clause and wasn't sure how to bind the value, you can actually pass an array of placeholders => values as the 2nd argument to `fetchAssoc`, something like `fetchAssoc('SELECT * FROM user WHERE id = :userId'), array('userId' => $user->getId());` – Mike Purcell Dec 13 '12 at 01:47
0

Try putting the aggregate field in the SQL and then fetch it using curly brackets. I'm using 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');

It works for me.

Ludeks
  • 510
  • 1
  • 6
  • 13
0

Have you looked at the doctrine cookbook section on aggregates? They use createQuery method on the entity manager rather than RawSql objects.

I'm no expert with doctrine, but this could be a good place to start!

tobyodavies
  • 27,347
  • 5
  • 42
  • 57