Doctrine orderBy on SUM() field with alias

doctrine, doctrine-orm, doctrine-query, php

Solution

- You can orderBy the SUM result field by list it in query projection by aliasing result using `AS`.

- If you want to use an aggregate function such as `MIN()`, `MAX()`, `AVG()`, you have to use `GROUP BY`.

Try simmilar to this, which works perfectly for me (BTW instead of associative array in `select` method):

$q = $this->em()->createQueryBuilder();

$q->select(['product.id', 'product.title'])
  ->addSelect('SUM(product.price) AS HIDDEN stat_sum_realised')
  ->from('ModuleAdmin\Entity\ProductEntity', 'product')
  ->groupBy('product.id');

$q->orderBy('stat_sum_realised', 'DESC');

Aggregate functions are detailed here (for e.x. for MySQL): http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

As of Doctrine ORM 2.3, you can also use the `HIDDEN` keyword, which will avoid (in this case) `stat_sum_realised` from getting hydrated into your resultset.

Problem

I am trying to do a simple query in doctrine but struggling. ``` $query->select(array( 'app_title' => 'u.title', 'user_name' => 'u.user_name', 'first_used' => 'MIN(u.creation_time)', 'last_used' => 'MAX(u.stop_time)', 'total_usage' => 'SUM(u.stream_seconds)', )) ->from(self::USAGE_TABLE, 'u') ->orderBy('total_usage', 'DESC'); ``` Obviously I get an error about the column name not being known because Doctrine is using it's own aliases (sclr4). However, if I try and order by the actual value; SUM(u.stream_seconds), then I get an unexpected bracket in the order by clause, I'm pretty sure SQL doesnt support this. So, I am simply trying to put data in a table and handle the sorting of the columns. This seems so simple, how do I do it? Any ideas?

Original source