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

5

I would like Laravel to return a list of products where the ID belongs to this array: [12,5,1,8,16] , but sort by id according to 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 [12,5,1,8,16] array.

How can I make orderBy this way?

    
asked by anonymous 05.12.2017 / 15:30

3 answers

6

Method 1

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

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

Example:

foreach ($ids as $id) {

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

Method 2

What's more, you could use sortBy to sort 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 the position of the array where the id is, causing the order to be according to the $ids position.

See the documentation for array_search

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

Method 3

Depending on the situation you are going to use this, it may still be worthwhile to use the lists method, combined with the iteration in $ids

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

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

Method 4

I think this is best applied to you. I did a search on Collection documentation and found this beauty called keyBy .

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

See!

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

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

Method 5

A asks in SOEN also that there is the same questioning as yours. I particularly did not like the way it was done, 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 implode with a comma to generate a snippet of an SQL query through DB::raw . That way, however much 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 with PHP.

FIELD aims to determine the order that ORDER BY should consider when sorting through the ID field. That is, the query executed in the database will be exactly:

 SELECT * FROM produtos ORDER BY FIELD(id, 12, 5, 1, 8, 16);
    
05.12.2017 / 15:39
2

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

$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 sort rule and implode will cause this new array to generate > string where each element is separated by a comma.

That is, 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 orderByRaw method of QueryBuilder.

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

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

foreach ($ids as $id) { 
    $query->orderByRaw("id = {$id} desc");
}
    
05.12.2017 / 16:05
0

Actually MySQL has an option to ORDER from the menu you want the FIELD() function to be, and in Laravel you can use orderByRaw , where you can write a SQL statement, I think it's simpler to do so your code looks like this:

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

It ends up being a simpler way to do it, so you do not have to go through the ids array or the result and sort it in the future, so solve the problem in a more readable way. Although the other options meet the need.

    
05.12.2017 / 17:24