Question:
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 )
->get();
where $ids
is the variable that contains this array [12,5,1,8,16]
.
How can I orderBy
this way?
Answer:
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:
Example:
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
.
Look!
$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)
->orderByRaw($rawOrder)
->get();
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);