[FIXED] Why is the table alias converted to lower case in this query expression

Issue

This query shall get all products with a price reduction exceeding 30%:

return $query->where([
    'Products.reduced_price / Products.price <' => 0.7,
]);

This query results in the following error:

Column not found: 1054 Unknown column 'products.price' in 'where clause'

Why is the table alias converted to lower case? This error seems to depend on your mysql-settings. Some setups seem to be case-insensitive (for instance my dev-machine) others like my production server are case-sensitive ;)

Unfortunately it is not possible to leave out the table-alias "Products" in this condition, because there is a joined table that has also a column named "price". Leaving the alias out would lead to this error: Column 'price' in where clause is ambiguous

Solution

The reason is the _parseCondition method of QueryExpression class. It will assume everything after the first space to be the operator and use strtolower on it, thus making the alias lower-case.

This can be easily mitigated by removing all spaces from the expression like this:

return $query->where([
    'Products.reduced_price/Products.price <' => 0.7,
]);

Answered By - David Albrecht