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 ?

score 119 · Accepted Answer · edited Jun 20 '20 at 09:12

119

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 02 '14 at 21:42

MPikkle

1,977
3
13
16

8

This is pure gold – Valdrinium Jul 06 '18 at 14:50
$ids_ordered = implode(',', $itemIds); to $ids_ordered = implode(',', $ids); – kunal Jul 25 '18 at 04:28
this only works on mysql. I had to use php to sort because I use postgres – TheDevWay Jan 08 '19 at 15:48
As stated in the original post, this was a question specifically about MySQL. You can try to change the raw SQL with something like this: https://stackoverflow.com/questions/866465/order-by-the-in-value-list – MPikkle Jan 09 '19 at 16:04
This is incredible! – James Shisiah Jun 09 '20 at 23:21
Is there a Postgres version? FIELD isn't available for that database. – Riza Khan Feb 05 '23 at 04:54

score 9 · Answer 2 · answered Jul 09 '18 at 19:43

9

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

answered Jul 09 '18 at 19:43

Mehdi hosein pour

123
2
8

1

What worked for me was: `$searchedStrings = "'".implode("', '", $originalArrayOfStrings)."'";` – Banik May 16 '21 at 12:34
Do you have a Postgres friendly version. FIELD isn't available for it. – Riza Khan Feb 05 '23 at 05:09
dear Riza Khan, here you can read more about this feature in Postgres: https://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql – Mehdi hosein pour May 31 '23 at 06:59

score -1 · Answer 3 · edited Aug 23 '20 at 17:04

-1

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

edited Aug 23 '20 at 17:04

halfer

19,824
17
99
186

answered Aug 23 '20 at 17:02

HARIPRASATH PANNEERSELVAM

29
3

Long lines like this often benefit from line-wrapping `:-)`. – halfer Aug 23 '20 at 17:05

Laravel: order by where in

3 Answers3

Solution:

Additional Notes:

Linked