Doctrine: Order by alias with same name as column

Question

I have following DQL (Doctrine1.2):

$dql->select('sum(t.column1) column1, t.column2')
    ->from('Table t')
    ->groupBy('t.column2')
    ->orderBy('column1');

But the generated SQL is same like for the following DQL:

$dql->select('sum(t.column1) column1, t.column2')
    ->from('Table t')
    ->groupBy('t.column2')
    ->orderBy('t.column1'); //Ordered by column1 not by sum(t.column1)

Is it possible to force Doctrine to use alias column1 instead of the column name t.column1?

I need this, because I would like to have same output format from model for two very similar DQLs. (lately rendered by the same template). The other DQL looks like this:

$dql->select('t.column1, t.column2')
    ->from('Table t')
    ->orderBy('t.column1');

Possible workaround is to select sum(t.column1) twice. First with alias column1 and second time with some other alias, why is after passwd to the orderBy function, but it's does not look like the clearest solution.

Any suggestions?

Thanks

score 6 · Accepted Answer · answered Jan 12 '11 at 14:41

6

Could you try this approach? Just put the "sum(t.column1)" in the order by, instead of trying to use the alias:

$dql->select('sum(t.column1) as column1, t.column2')
    ->from('Table t')
    ->groupBy('t.column2')
    ->orderBy('sum(t.column1)');

answered Jan 12 '11 at 14:41

Matt Gibson

37,886
9
99
128

This works, but generates `ORDER BY SUM(t.columnt1)` into SQL. Looks better than selecting column two times, but is this the best solution? Does SQL optimizer recognize it and count `sum(t.column1)` only once? – Petr Peller Jan 12 '11 at 15:43
1

@Petr Yes, unless you're using some fairly crazy DB backend. That's a venerable SQL idiom and all the modern engines will recognise what you're doing and optimise correctly. MySQL will certainly be fine. – Matt Gibson Jan 12 '11 at 15:47

Doctrine: Order by alias with same name as column

1 Answers1