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 ?
To sort results in the database query, you'll need to use the orderBy() method, and provide the table field you want to use as criteria for ordering. This will give you more flexibility to build a query that will obtain only the results you need from the database. You'll now change the code in your routes/web.
Check if not null: whereNotNullSELECT * FROM users WHERE last_name IS NOT NULL; The equivalent to the IS NOT NULL condition in Laravel Eloquent is the whereNotNull method, which allows you to verify if a specific column's value is not NULL .
$ids = array(1,17,2);
$ids_ordered = implode(',', $ids);
$items = static::whereIn('id', $ids)
->orderByRaw("FIELD(id, $ids_ordered)")
->get();
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();
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With