Pass parameters for ODER BY
- Sniclman
- Member | 15
Hey,
I am solving a small problem with the correct ORDER BY notation via the order
method with parameters.
Potřebuji vygenerovat SQL dotaz s následujícím řazením
ORDER BY FIELD(id,5,8,4,6,9) DESC
ORDER BY FIELD(id,9,7,6,1,5) ASC
I need to generate a SQL query with the following ordering
$ids = [5,8,4,6,9]; // passed from method, just for example
$reverse = false; // passed from method, just for example
$selection->order('FIELD( id, ' . implode(',', $ids) . ' ) ' . ($reverse ? 'DESC' : 'ASC') );
// Piggish writing but it works
$selection->order('FIELD(id, ?) ?', $ids, $reverse ? 'DESC' : 'ASC');
// Generates an invalid SQL query, ASC or DESC sort is delimited by apostrophes
// "ORDER BY FIELD(id,5,8,4,6,9) 'ASC'"
$selection->order('FIELD(id, ?) ?name', $ids, $reverse ? 'DESC' : 'ASC');
// Generates an invalid SQL query, ASC or DESC ordering is delimited by quotes `
// "ORDER BY FIELD(id,5,8,4,6,9) `ASC`"
$selection->order('FIELD(id, ?) ?', $ids, new SqlLiteral($reverse ? 'DESC' : 'ASC'));
// Secure writing, however ASC or DESC must be in SqlLiteral
The ?order
notation is also available, but it requires the
['id' => false]
⇒ ORDER BY id DESC
field.
I tried this:
$selection->order('?order', [new SqlLiteral('FIELD(id,?)', $ids) => $reverse]);
// Throws an error, the array key cannot be SqlLiteral (illegal offset)
$selection->order('FIELD(id,?) ?order', $ids, $reverse);
// Throws an error, expects ?order to be an array
I will be glad for any advice on how to use the order
method
correctly. :-)
- Sniclman
- Member | 15
Lumeriol wrote:
You've added $reverse as another parameter, try using it as part of the entire order() string:
$selection->order('FIELD(id, ?)' . ' ' . ($reverse ? 'DESC' : 'ASC'), $ids);
Almost same as example
$selection->order('FIELD(id, ?) ?', $ids, new SqlLiteral($reverse ? 'DESC' : 'ASC'));
I think that this code does not look good, i have bad feelings about that. Everytime when code contains connected text there is chance to make mistake.
- dakur
- Member | 493
I think that this code does not look good, i have bad feelings about that. Everytime when code contains connected text there is chance to make mistake.
Code shouldn't look good but be readable. If it isn't then you can try another approach or just simply make new lines in good places.
As of the mistakes, what kind of mistakes do you mean? If you use prepared statements then the driver takes care of SQL injection related problems if you don't do some wild things there. If you mean logical mistakes then the code should be covered with tests which prevent them.
Last edited by dakur (2024-02-02 16:23)