1

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));
Chris Kempen
  • 9,491
  • 5
  • 40
  • 52

2 Answers2

6

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!

Chris Kempen
  • 9,491
  • 5
  • 40
  • 52
  • 1
    Works like a charm, thank you for posting your solution. In my opinion using an expression is the best way to order by FIELD in ZF2. – Patito Jan 24 '15 at 19:34
0

I am not sure I know what you mean when you apply word "elegant" to a function, but if it is when it looks like:

$select->orderInExactlyThatOrder (array (4,2,3,1));

then no, I don't think so.

akond
  • 15,865
  • 4
  • 35
  • 55
  • I mean 'elegant' in the sense of using built-in Zend capabilities, rather than senselessly piling on lines of PHP code inside my function doing the selecting purely to loop through the result sets and re-order them inefficiently. But thanks for the response :) – Chris Kempen Oct 04 '13 at 11:11