How to sort a query by order in an array using pagination?

Question

SQL

There is an array with an id, for example: I execute a query: The result of the query goes in ascending order of the 'id' field (1, 2, 3) , how to get it in the same order as indicated in the array 2, 1,3 ?
$ids = [2, 1, 3];

$data = Test::whereIn('id', $ids)->paginate(5);

Answer 1

ORDER BY FIELD(`id`, 2,1,3)
Sort in order

Answer 2

Add a sort field to the table, for example, sort_index

id | sort_index
---|-----------
 1 | 2
 2 | 1
 3 | 3

Then sort by this field

$data = Test::whereIn('id', $ids)
            ->orderBy('sort_index')
            ->paginate(5);

Answer 3

No way. SQL cannot respect the order of arguments in an IN predicate.
And the fact that you get the answer in ascending order is also not guaranteed by the standard (and, suppose, PostgreSQL can return rows in an arbitrary order, which depends on how they are stored inside the database).
The only way to specify the order of the rows in the selection is to use the ORDER BY clause , that is, explicitly define the order of the returned rows.
In general, it resembles the "XY problem", so let's tell you where such a desire arose in general, what you are trying to achieve with this.