Bug: orderBy method does not allow ordering by custom field names containing spaces

[leigh-ols]

SELECT CONCAT('',a.date_created,'',DATE_FORMAT(a.date_created,'%d/%m/%Y')) AS 'Order Date',
FROM my_table
ORDER BY `Order Date`

Is valid SQL

However doing

$db->orderBy('Order Date');

Results in the SQL query having:

ORDER BY OrderDate

Note how Order Date became OrderDate which is a non-existant field and returns an SQL error.

This is due to the regex in the orderBy method stripping out spaces.

[avbdr]

thanks, fixed

[leigh-ols]

Ugh,... this is still an issue. It appears my solution only half fixed the problem.

ORDER BY fields that contain spaces need to be wrapped with backticks in order for mysql to correctly sort the results.

Could we get this re-opened please?

[leigh-ols]

This is more complex than it first appears. Simply wrapping backticks around the field will not work, because you can have multiple comma separated columns resulting in `date,name` when what you wanted was `date`,`name`.

We could explode this on the , and trim any whitespace to solve this, but we also have to consider a.date,b.name which would result in `a.date`,`b.name`, which is also wrong.
To fix this we could then explode the already exploded results on a . to get a.`date`,b.`name` Which is correct until we try something like: My.Field.Alias, or My,field,alias which would get incorrectly exploded and wrapped.

To keep things simple we could explode on , and then preg_match each field for certain characters like whitespace, and if they match then wrap in backticks. I'm testing this method now and it appears to work throughout the codebase i'm using. I'm not sure if this is a safe enough assumption to make in the public domain though?

Ultimately what we want is to end up with results like this:

ORDER BY `date`
ORDER BY `date`,`name`
ORDER BY a.`date`,b.`name`
ORDER BY `Start Date`, `End Date`
ORDER BY `Start,Date`,`End.Date`

The final approach almost achieves this (except for the final one) but makes the assumption that if a field contains a space it needs to be backticked, otherwise it doesn't.
I'm curious to hear what others here think is the best approach.

[maan81]

Could I have an example, such as for https://stackoverflow.com/questions/9378613/how-to-define-a-custom-order-by-order-in-mysql#answer-9378709 ?