Doctrine orderBy on SUM() field with alias

Question

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?

Answer 1

1. You can orderBy the SUM result field by list it in query projection by aliasing result using AS.
2. 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.