CODEWITHSUNDEEP
phpmysqllaravelsphinx
MPikkle
MPikkle

Reputation: 2047

Laravel: order by where in

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 ?

Upvotes: 44

Views: 35151

Answers (3)

HARIPRASATH PANNEERSELVAM
HARIPRASATH PANNEERSELVAM

Reputation: 27

$category_id = Category::select('id')->orderby('name','ASC')->get()->pluck('id')->toArray();

Upvotes: -2

MPikkle
MPikkle

Reputation: 2047

Solution:

$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();

Upvotes: 121

Mehdi hosein pour
Mehdi hosein pour

Reputation: 123

I got this problem too, but my target array elements were strings. in this case ...

$strings = array('xxx','yyy','zzz');

$imploded_strings = implode("','", $strings);

$items = static::whereIn('some_column', $strings)
->orderByRaw(DB::raw("FIELD(some_column, '$imploded_strings')"))
->get();

Upvotes: 9

Related Questions