phpzend-framework2zend-dbzend-db-select

SQL ordering by field in Zend Framework 2


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));

Solution

  • 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!