Laravel whereIn incorrect order of result

Question

I want to get record from database when array of ids match the id in the customer table.

Here is my array of ID:

0 => 1
1 => 1788
2 => 887
3 => 697
4 => 719

I have following query,

$customers = Customer::whereIn('id', $idArray)->get();

I am getting all the customers I need but not in correct order. I am getting customers in following order.

Is it default behaviour or something I am doing wrong.

Any suggestion is appreciated.

Ah right, what's the order rule? Just the order of the $idArray? https://stackoverflow.com/questions/8322849/mysql-order-by-specific-id-values does that help? I don't know how that would translate in Laravel. — Jonnix, Sep 02 '19 at 12:17
Perhaps https://stackoverflow.com/questions/29659430/mysql-order-by-field-in-eloquent — Jonnix, Sep 02 '19 at 12:20

Salim Djerbouh · Accepted Answer · 2019-09-02T12:56:31.953

Use the òrderByRaw query builder method to add a raw "order by" clause to the query. The method's signature is

$this orderByRaw(string $sql, array $bindings = [])

So it expects a raw sql query as an argument, let's give it one using the DB facade providing the desired $ids_ordered as a string

$idArray = [
    0 => 1,
    1 => 1788,
    2 => 887,
    3 => 697,
    4 => 719,
];
$ids_ordered = implode(',', $idArray); // Basically casts the array values to a string
$customers = Customer::whereIn('id', $idArray)
                     ->orderByRaw(DB::raw("FIELD(id, $ids_ordered)"))
                     ->get();
return $customers;

The raw sql query would be like (assuming MySQL as a DB engine)

select * from `customers` where `id` in (?, ?, ?, ?, ?) order by FIELD(id, 1,1788,887,697,719)

Results:

[
    {
        "id": 1,
        "created_at": "2019-09-02 12:21:15",
        "updated_at": "2019-09-02 12:21:15"
    },
    {
        "id": 1788,
        "created_at": "2019-09-02 12:21:15",
        "updated_at": "2019-09-02 12:21:15"
    },
    {
        "id": 887,
        "created_at": "2019-09-02 12:21:15",
        "updated_at": "2019-09-02 12:21:15"
    },
    {
        "id": 697,
        "created_at": "2019-09-02 12:21:15",
        "updated_at": "2019-09-02 12:21:15"
    },
    {
        "id": 719,
        "created_at": "2019-09-02 12:21:15",
        "updated_at": "2019-09-02 12:21:15"
    }
]

Laravel whereIn incorrect order of result

1 Answers1