Sort MySQL query with multiple joined tables

My query uses left join to fetch multiple values from the table objects_values. I’m having trouble sorting the results the way I want; if ASC, then show the objects with the lowest attribute values first, and if DESC, show the objects with the highest attribute values first.

I have actually managed to get it to work when the order direction is DESC, but when the direction is ASC the results they just go to the bottom of the results, no matter the attributeId.

The query that puts the 237 results at the bottom is the following:

SELECT
    objects.id,
    objects.title,
    values.value
FROM objects
LEFT JOIN objects_values AS values ON values.objectId=objects.id
WHERE objects.categoryId IN (195)
ORDER BY
    CASE WHEN values.attributeId=237 THEN values.value END ASC,
    objects.id asc

I want the query to ALWAYS order by attribute 237 first, and put those results first in the results, no matter if the value of 237 is 0.1 or 10.

This would be easy if all joined tables had unique names, but this query can feature 20 different attributes from the objects_values table.

Am I approaching this the wrong way?

Answer

To put 237 first, you can use:

ORDER BY (values.attributeId = 237) DESC
         objects.id asc

This uses the MySQL feature that boolean expressions are treated as numbers in a numeric context, with “1” for true and “0” for false (that is why DESC is needed for this to be first).

This is equivalent to:

ORDER BY (CASE WHEN values.attributeId = 237 THEN 1 ELSE 0 END) DESC
         objects.id asc