php - Laravel: order by where in

Question

I am using SphinxSearch to query some contents and have the ids of my objects that I want to query with MySQL. The array of my ids are sorted depending on their rank Sphinx gives. Thus, I would like to make a MySQL like so:

SELECT * FROM table WHERE id IN (1,17,2) 
ORDER BY FIELD(id,1,17,2)

I know I can do:

Table::whereIn('id', $ids)->get();

But I can't get the order I had.

How can I do that in a proper way with Laravel ?

Answer 1

$ids = array(1,17,2);
 
$ids_ordered = implode(',', $ids);
 
$items = static::whereIn('id', $ids)
 ->orderByRaw("FIELD(id, $ids_ordered)")
 ->get();

Additional Notes:

Using the solution found on an article titled Get all items at once ordered by the current order of ids in the WHERE IN clause using Eloquent:

Useful when you want to orderBy the items from the database using the order of id's in the array you supplied in the WHERE IN clause. Can be easily modified to suit your needs, like provide a different order of ids to be used.

// Raw SQL:
// SELECT * FROM items WHERE id IN (1,2,3,4,5) ORDER BY FIELD(id,1,2,3,4,5);
 
$itemIds = array(1,2,3,4,5);
 
$ids = implode(',', $itemIds);
 
$items = static::whereIn('id', $itemIds)
    ->orderByRaw(DB::raw("FIELD(id, $ids)"))
    ->take($limit)
    ->get();