Backticks added to values in ORDER BY FIELD()

[brucecastro]

Hello,

First off, thanks for your work on this library!

I am running into an issue after updating to the latest version. I was previously using version 1.3, which did not have this issue. Running the following query:

$users->find(array('id IN ?', array(3,2,1)), array('order' => 'FIELD(id, 1, 2, 3)'))

Results in the following error:

PDOStatement: Unknown column '1' in 'order clause'

Because the underlying SQL Query is:

SELECT `id` FROM `users`
WHERE id IN (3,2,1)
ORDER BY FIELD(id, `1`, `2`, `3`)

Notice how backticks are being added to the values within the FIELD function, making MySQL think those are field names.

[ikkez]

Hi.
Well this issue was recently discussed in the core framework at f3-factory/fatfree-core@5dcbd8c#all_commit_comments which ended up in f3-factory/fatfree-core#244

The good news is that this is not caused by cortex itself, because it does not do anything with the order by clause, but the underlying SQL mapper does a false guess here.
At the moment, you can workaround this issue by fetching the latest fatfree-core version and use

array('order' => 'FIELD(`id`, 1, 2, 3)')

When you add your own backticks here, the auto-quoting is disabled and everything should work fine. Nevertheless I'm looking into a more generic preparation of the order clause in cortex to mitigate future issues like that.

[ikkez]

@brucecastro With the latest commit, ordering fields are now automatically quoted. If it does detect already quoted fields, it'll skip this process. Hope this works fine ;)

[brucecastro]

Thank you very much!