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


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 the array order!*

What I have so far:

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

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

How can I orderBy this way?


method 1

Perhaps the best solution would be to use Laravel's Collection , since you're bringing everything in with get .

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


foreach ($ids as $id) {

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

method 2

Also, you could use sortBy to 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 the position of the array where the id is, making the ordering according to the position of $ids .

See the array_search documentation

Note : I didn't test this second example, but you probably want to use sortByDesc instead of sortBy .

method 3

Depending on the situation you're going to use this in, it might still pay you to use the lists method, combined with iterating over $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 your case. I did a search in the Collection documentation and found this beauty called keyBy .

Basically what it does is transform the Collection indices according to the passed key. In this case I choose the id .


 $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 its name.

Method 5

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

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

$produtos = Produto::whereIn('id', $ids)

Note that you use implode with a comma to generate a snippet of an SQL query through DB::raw . That way, as much as I find it aesthetically ugly to do so, I have to admit that it's better to have the sorting already come from the database, than having to reprocess everything through PHP.

The FIELD is intended to determine the order that the ORDER BY should consider when sorting by 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);
Scroll to Top