Kohana ORM ordering

Question

I'm using Kohana's ORM library, and I'm wondering if there is any way to select DB records in a particular predetermined sequence.

$products_ids = array('5', '6', '1', '33', '2');

$products = ORM::factory('Product')->where('state', '=', 1)
->and_where('id', 'IN', $products_ids)->find_all();

This orders result by primary key (id). So result records ordered like (1, 2, 5, 6, 33). How select records by order defined in $products_ids ('5', '6', '1', '33', '2')?

Thanks.

Answer 1

Yes this is possible.

$products = ORM::factory('Product')->where('state', '=', 1) ->and_where('id', 'IN', $products_ids)->order_by('product_id', 'desc')->find_all();

Note the added order_by() in the string above.

You can order your results now. It has more cool features like group_by().

Read the documentation and you will find more unexpected magic in ORM.

Answer 2

In MySQL you can have conditions in order by clause where if the condition match it gets treated as 1 and 0 on fails.

The code for the example would be:

$products_ids = array('5', '6', '1', '33', '2');

$products = ORM::factory('Product')->where('state', '=', 1)->and_where('id', 'IN', $products_ids);
foreach($products_ids as $product_id)
{
    $products->order_by(DB::expr('id='.$product_id), 'desc');
}
$products = $products->find_all();

In MySQL it would look something like this:

SELECT * FROM products WHERE ... ORDER BY id=5 DESC, id=6 DESC, id=1 DESC, id=33 DESC, id=2 DESC;

Answer 3

I don't know why you wan do so stupid thing, but 2 answers you have. (For stric problem. But probably you don't show full background.

I propose 2 scenarios:

1. Products on page category - add column for order it to table.
2. Display shopping chart - order by order_item_id

BTW: ORM is comfortable, but slow, so if you nedd it only for display (read operation) better is using raw query. Here solution for ORM.

public function get_ordered(array $ids){
  if(empty($ids))
    return array();
  $res = DB::select->from($this->_table_name)
           ->where($this->primary_key(),'IN',$ids)
        ->execute($this->_db)->as_array($this->primary_key());
  $out = array();
  foreach($ids AS $one){
    if(isset($res[$one]))
       $out[$one] = $res[$one];
  }
  return $out;
}