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?

Marvellamarvellous

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.

Pashto

This helps thanks, now I only get a problem when I start using setFirstResult for the pagination.. "An exception occurred while executing 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY sclr4 DESC) AS doctrine_rownum ..." obviously gives "Invalid column name 'sclr4'." but I can't seem to work out how to stop it! – Marvellamarvellous

@Marvellamarvellous Did you ever figure out why you got that "Invalid column name 'sclr4'" exception? I'm having the same issue. – Brianabriand