How to sort array preserving its initial state

Question

Using CakePHP 2.0 here. The title is a bit misleading, so to clear things up - I have session array which is populated with ids of products as user adds them to cart. So this array goes like [0] => 25, [1] => 70 etc. Let's say this array is called $products.

What I want to ask is is there some possibility to have array which I get by using 'find' model's function ('conditions' => array('Product.id' => $products)) to be sorted not by some of Model.field values (as in 'order' option), but by $products array indices, so that when I render the products content in view I would get all those products in cart sorted in such sequence as user were adding them.

Here's an example - Session array $products:

[0] => 35,
[1] => 15,
[2] => 25

Then I pass this array to conditions of find function:

$list = $this->Product->find('all', array('conditions' => array('Product.id' => $products)));

In the end $list gives me array which sorted by product.id. So instead of having:

[0] => Array
    (
        [Product] => Array
            (
                [id] => 35 )),
[1] => Array
        (
            [Product] => Array
                (
                    [id] => 15 )),

[2] => Array
        (
            [Product] => Array
                (
                    [id] => 25 ))

I get:

[0] => Array
    (
        [Product] => Array
            (
                [id] => 15 )),
[1] => Array
        (
            [Product] => Array
                (
                    [id] => 25 )),
[2] => Array
        (
            [Product] => Array
                (
                    [id] => 35 ))

So far all the answers doesn't solve my problem. Please, pay closer attention to the example I gave, it's very simple, but the answers provided are in different key.

Again, I need final array $list to be sorted by indices of session array $products, but I get $list sorted by Product.id field, even though I didn't specify any 'find(array('order'=> ...))' at all and even tried to set it to false.

Answer 1

Don't want to sound rude, but all of responses to my question were about how to order my array by Product id (which was clearly not what I asked) or suggestions on doing manual sql queries which is inappropriate. Seems like I couldn't properly describe what I needed, but I tried to make it as clear as possible, sorry.

I stumbled upon a solution while trying to find how to 'disable order' in cake. So basically what you need to do is to have an array (let's call it $queue) where you keep product ids as they are added to cart, and then in 'order' option in find model function (or paginate) you should provide it like:

'order'=>array('FIELD(Product.id,'.implode(',', $queue).')')

$queue might look like this, as example:

[queue] => Array
        (
            [0] => 51
            [1] => 1
            [2] => 11
        )

In the end you get $list array in which the order of products is same as in $queue. If you didn't specify 'order' then you would get you $list array like this:

 [queue] => Array
            (
                [0] => 1
                [1] => 11
                [2] => 51
            )

Answer 2

I can't think of a simple SQL query that would order the results according to a list of parameters.

So the simplest is probably to reorder the records after having fetched them from the db.

$records = $this->Product->find('all', array('conditions' => array('Product.id' => $products)));

$sorted_records = array();
foreach($records as $record)
{
  $key = array_search($record['Product']['id'], $products);
  $sorted_records[$key] = $record;
}

ksort($sorted_records);

debug($sorted_records);

Answer 3

This is valid SQL, not sure how to make cakephp render it but i think it will solve your ordering issue :

SELECT * FROM product WHERE id IN (35, 15, 25) ORDER BY id = 35 DESC, id = 15 DESC, id = 25

Answer 4

I'm not familiar with cake or its database layers, but maybe you can add a little bit of sql.

The relevant sql would be something like

order by field(myTable.myID, 35, 15, 25)
-- or alternatively, more cross database compatible
order by case when myTable.myID = 35 then 1
              when myTable.myID = 15 then 2
              when myTable.myID = 25 then 3
         end

however, the most universal approach would be to just reorder in php code.

$products = array(35,15,25);//the ordered array
$records = array_flip($products);
foreach ($list as $row) {
    $id = $row['Product']['id'];
    $records[$id] = $row;
}
print_r($records);

Answer 5

The way I usually handle problems like this is by indexing the $list by the Product ID using the Set class, which is built into Cake. So

$list = Set::combine($list, '{n}.Product.id', '{n}.Product');

Then use loop through your cart ($products) which is already in the order you want

foreach($products as $prod) echo $list[$prod]['Product']['name'];

Hope this helps.

Answer 6

If I understand correctly you need to order the model result, or in other words set an order on the query to sort in an arbitrary fashion.

Here's two options:

1. Specify a sort. Not familiar with Cake but the mysql you need to generate is: order by id = 25 desc, id = 70 desc, id = etc (a guess would say to try order => 'id = 25 desc, id = 70 desc, id = etc') which you'd have to assemble from the $products array.

2. create a basket model and use a join query to get the product list in the correct order (not simple but probably better than relying on a session var).

Update

I had paid attention and did fix your issue providing you with the SQL you needed to generate, along with a guess at the Cake syntax. Looking at your posted solution, your FIELD(id, x, x, x) is doing the same thing, but is a much cleaner solution. Well done for finding it, I'd remove your "don't mean to be rude" comments though, as they generally do seem a little rude.