How would you (elegantly) order a select statement by a field using closures in Zend Framework 2?
I have the following PHP code:
$gateway = new \Zend\Db\TableGateway\TableGateway('table_name', $adapter);
$select = $gateway->select(function($select){
$select->where->in('id', array(4, 2, 3, 1));
// insert some awesome ordering magic here..!
});
I'm aware that you could easily do this:
...
$select->order(array('id ASC'));
...
But that would just order the results by ascending id
value, and I need to order them by a specific id sequence (i.e.: have them ordered in the sequence 4, 2, 3, 1
.
Is there an elegant Zend Framework 2 function I could use here to order by the id
's themselves? Effectively creating the SQL:
select * from table_name
where 'id' in (4, 2, 3, 1)
order by field('id', (4, 2, 3, 1));
Here's the best way I've found to order results in Zend 2 by a field. Please contribute your own solutions if you have any suggestions / better approaches!
I eventually went with the Expression
class to give the desired result. To give a detailed example, have a look at the PHP code below:
use Zend\Db\Sql\Expression;
// create the array of ID's we want to order by...
$ids = array(4, 2, 3, 1);
// get our table gateway and our select going...
// don't forget the 'use' syntax here...
$gateway = new \Zend\Db\TableGateway\TableGateway('table_name', $db_adapter);
$select = $gateway->select(function($select) use($ids){
// standard 'in' functionality...
$select->where->in('id', $ids);
// use an expression here to achieve what we're looking for...
$ids_string = implode(',', $ids); // assuming integers here...
$select->order(array(new Expression('FIELD (id, '. $ids_string .')')));
});
Hope this helps someone sometime!