How to order the query in the same order as an array used in Wherein?

Question

I would like Laravel to return me a list of products where the ID belongs to this array: [12,5,1,8,16], but sort by id as per array order!*

What I have so far:

 $produtos = Produto::whereIn('id', $ids)
        ->orderBy( adicionar aqui a ordem do array $ids )
        ->get();

where $ids is the variable that contains this array [12,5,1,8,16].

How can I do the orderBy in this way?

Answer 1

Method 1

Perhaps the best solution would be to use the Collection of the Laravel, since you are bringing everything with the get.

In version 5.5 of Laravel you could use firstWhere to do this by iterating on the $ids obtaining the values according to their position:

Example:

foreach ($ids as $id) {

     $produto = $produtos->firstWhere('id', '=', $id);
}

Method 2

Besides, you could use the sortByto sort the products:

 $sorter = static function ($produto) use ($ids) {
    return array_search($produto->id, $ids);
 };

 $produtos = Produtos::whereIn('id', $ids)->get()->sortBy($sorter);

In this second example, array_search will return to the position of array where the id, making the ordination according to the position of $ids.

See the documentation of array_search

Note: In this second example I did not do tests, but probably you may want to use sortByDesc instead of sortBy.

Method 3

Depending on the situation you’re going to use this, it might still make up for you using the method lists, combined with iteration in $ids

  $produtos = Produto::whereIn('id', $ids)->lists('nome', 'id');

  foreach ($ids as $id) {
    if (isset($produto[$id]) {
       echo $produto[$id];
    }
  }

Method 4

This one I think is best applied to your case. I did a search on documentation of the Collection and I found this beauty called keyBy.

Basically what it does is to transform the contents of the Collection according to the past key. In this case I choose the id.

Behold!

 $produtos = Produto::whereIn('id', $ids)->get()->keyBy('id');

So you could do something similar to the operation of method 3 previously explained, however $produtos[$id] would give you access to the object Produto, instead of just the same name.

Method 5

To a question in the SOEN also that there is the same question as yours. I didn’t particularly like the way it was made, but it would be basically this:

$rawOrder = DB::raw(sprintf('FIELD(id, %s)', implode(',', $ids)));

$produtos = Produto::whereIn('id', $ids)
                 ->orderByRaw($rawOrder)
                 ->get();

Note that you use the implode with a comma to generate a chunk of an SQL query through the DB::raw. Thus, as much as I find it aesthetically ugly to do so, I have to admit that it is better that the ordering already comes from the bank, than having to reprocess everything by PHP.

The FIELD is intended to determine the order that the ORDER BY should consider when ordering by field ID. That is, the query executed in the database will be exactly:

 SELECT * FROM produtos ORDER BY FIELD(id, 12, 5, 1, 8, 16);

Answer 2

You can create a clause order by taking the array as a basis.

$ids = [12, 5, 1, 8, 16];
$orders = array_map(function($item) {
    return "id = {$item} desc";
}, $ids);
$rawOrder = implode(', ', $orders);

$prod = Produto::whereIn('id', $ids)->orderByRaw($rawOrder)->get();

The array_map will produce a new array where each element will be a rule of ordering and the implode will make this new array manages a string where each element is separated by a comma.

That is, the map will generate something like this:

0 => "id = 12 desc"
1 => "id = 5 desc"
2 => "id = 1 desc"
3 => "id = 8 desc"
4 => "id = 16 desc"

And the implode will generate this

"id = 12 desc, id = 5 desc, id = 1 desc, id = 8 desc, id = 16 desc"

So you can use the string $rawOrder in the method orderByRaw querybuilder.

---

In fact, you can even shorten the code a little by accumulating orders in Builder

$ids = [12, 5, 1, 8, 16];
$query = Produto::whereIn('id', $ids); 

foreach ($ids as $id) { 
    $query->orderByRaw("id = {$id} desc");
}

Answer 3

Actually Mysql has an option to SORT the menu you are wanting that is the function FIELD() and in Laravel you can use the orderByRaw, where you can write an SQL statement, I think it’s simpler to do it that way, your code would look like this:

$produtos = Produto::whereIn('id', $ids)
        ->orderByRaw('FIELD(id,'.implode(",",$ids).')')
        ->get();

It turns out to be a simpler way to do it because you don’t have to go through the array of ids or the result and sort it in the future, so it solves the problem in a more readable way. Although the other options meet the need.