Laravel, how to use select AS in where query?

Question

        $data['losers'] = $data['losers']
            ->select(DB::raw('ROUND((((users_24h-users_48h_24h) / users_48h_24h) * 100),2) AS daypercentage, name'))
            ->where('daypercentage', '>=', '0')

If i use daypercentage in my where query, it can´t be found. How would that be possible in this case?

what's the error with this code? also please post what is `$data['losers']` — Alberto Sinigaglia, Aug 17 '20 at 17:17
Column not found: 1054 Unknown column 'daypercentage' in 'where clause' $data['losers'] are a bunch of data, the point is that i calculate daypercentage in my select with 2 integer. Without the where query everything works. But if i try to filter where daypercentage the error accures. I guess the "AS" from the select cant be used in the where, the question is how i m able to use it. My guess is with 2 calculations but one would be better. — Dapp Future, Aug 17 '20 at 17:19
Try using an alias in a query you run via phpMyAdmin or workbench. You will find that the alias cannot be used in the where — RiggsFolly, Aug 17 '20 at 17:22

score 0 · Accepted Answer · answered Aug 17 '20 at 17:22

In SQL, aliases defined in the select clause cannot be reused in the where clause. You need to repeat the expression (or use a subquery or cte).

MySQL has a trick that allows using aliases in the having clause, but I would not recommend that here. You seem to want only non-negative percentages, so I think your where clause could be simplified as:

where users_24h >= users_48h_24h

I am not sure what is the best way to phrase this in Lavarel, maybe:

->select(DB::raw('round( (users_24h - users_48h_24h) / users_48h_24h * 100,2) as daypercentage, name'))
->where(DB::raw('users_24h >= users_48h_24h'))

Note that I removed a few unnecessary parentheses in the round() expression.

Query builder syntax: `->where('users_24h', '>=', 'users_48h_24h')` — aknosis, Aug 17 '20 at 17:53

Laravel, how to use select AS in where query?

1 Answers1