Pass parameters for ODER BY

Sniclman
Member | 11
+
0
-

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. :-)

Lumeriol
Generous Backer | 59
+
0
-

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);
Sniclman
Member | 11
+
0
-

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
+
0
-

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)