6

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?

Rick James
  • 135,179
  • 13
  • 127
  • 222
mark.sagikazar
  • 1,032
  • 8
  • 19
  • You need a subquery. I don't know if doctrine has any elegant way to write subqueries - But I would rather calculate `probability` and `weight` in PHP either way. – Paul Spiegel Mar 21 '20 at 15:57
  • The correct non-hackish way is subquery/cte [using a calculated column in the same query](https://stackoverflow.com/questions/8840228/postgresql-using-a-calculated-column-in-the-same-query) or simply copy-paste expression definition and it will work with all flavours of SQL.The mechanism you are searching for is called [lateal column reference](https://stackoverflow.com/questions/59334543/amazon-redshift-lateral-column-alias-reference). – Lukasz Szozda Mar 22 '20 at 08:47

1 Answers1

4

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I always love reading your answers -- I just have a hard time shedding the mental image that you are _THIS_ Rick James: https://www.youtube.com/watch?v=dJB0BkJlbbw&list=PLIYT3J6kwPPE7OcT5uieDrzQZoxXWfjEA – mickmackusa Mar 24 '20 at 04:43
  • Thanks! Looks good. Only problem is how to get this in a QueryBuilder, because it looks like Doctrine ORM doesn't support MySQL variables. Any idea? – Stephan Vierkant Mar 25 '20 at 12:41
  • Does it handle `CALLs` to Stored Procedures? – Rick James Mar 25 '20 at 14:50