Reuse doctrine alias in select clause

Question

I have the following SELECT clause in Doctrine (the query itself is created using query builder):

u.username,
MAX(p.score) as highscore,
SUM(pc.badgeCount) as badgeCount,
(SUM(pc.badgeCount) / :badgeSum) AS probability,
(-LOG(RAND()) * probability) as weight

(p is an alias for the main entity, pc is a joined one)

This gives me an error message from MySQL:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'probability' in 'field list'

How can I reuse the created aliases within the same SELECT clause?

Answer 1

I would not call it "reuse". Instead, I would call it "use".

You cannot use an alias until the GROUP BY, HAVING, and ORDER BY clauses.

An alternative might be to use an @variable:

u.username,
MAX(p.score) as highscore,
SUM(pc.badgeCount) as badgeCount,
@prob := (SUM(pc.badgeCount) / :badgeSum) AS probability,
(-LOG(RAND()) * @prob) as weight

The "AS" part still provides the column name for displaying and for those later clauses. Meanwhile, @prob provides a value "immediately".

I say "might" because I think that such use of @variables is going away. The order of evaluation of the SELECT elements is probably "undefined" in the standard. The use shown above depends on the elements to be done in lexical order.