Zend framework 3 ORDER BY FIELD

Question

How would you write the following query in Zend framework 3 ?

SELECT * FROM table_name ORDER BY FIELD(field_name, 'a','b','c');

I tried

$select->order(new Expression("FIELD(field_name, 'a', 'b', 'c')"),field_name);

but not moves...

Answer 1

Instead of using expression, can you give string to order method

$select->order("FIELD(field_name, 'a', 'b', 'c')");

If field name is a variable

$select->order("FIELD(" . $field_name . ", 'a', 'b', 'c')");

If the issue still persists, then we can trace the issue by printing the query

$select->query()->__toString()

or 

$select->__toString();

dont remember the query exactly, you can try them

Answer 2

I think you made a mistake somewhere, make sure that you've:

• Imported Expression correctly with use.
• removed the second argument form order(), there is none.

Zend\Db\Sql\Expression is the correct way to extend a query with DBMS-specific expressions.

With the following test script I get your expected result:

$platform = new Zend\Db\Adapter\Platform\Mysql();
$select = new Zend\Db\Sql\Select();
$select->from(['t' => 'table_name']);
$select->order(
    new Zend\Db\Sql\Expression(sprintf(
        "FIELD(t.%s, 'a', 'b', 'c')",
        $platform->quoteIdentifier('field_name')
    ))
);

var_dump($select->getSqlString($platform));

Result is:

string(81) "SELECT `t`.* FROM `table_name` AS `t` ORDER BY FIELD(t.`field_name`, 'a', 'b', 'c')"

Few notes here:

• Replace the Zend namespace with Laminas when you using Laminas instead.
• Make sure you always quote identifiers with quoteIdentifier() from the platform instance (you should be able to receive the platform from your table or DB instance).
• Use table aliases where you can, as an extra level of security against SQL Injections and they also make your life easier.
• On big tables with thousand of rows I guess you're query will get very slow. So you can either add an index and if this doesn't work create another column with the pre-calculated value from FIELD() (you can either do this in your application or on the DDL level).