I am facing a very weird issue where my query return a different order depending on the pagination size:
$query->orderByRaw("CASE WHEN tracking_eta IS NOT NULL THEN tracking_eta ELSE arrival_date END DESC")
->paginate(23)
->withQueryString();
This return
And if I do the same but paginate 24
Return this
For your reference the values on tracking_eta & arrival_date in this particular case are the same for both ids:
id: 219 arrival_date: 2024-09-17 06:00:00
tracking_eta: 2024-09-17 08:58:00
id: 218 arrival_date: 2024-09-17 06:00:00
tracking_eta: 2024-09-17 08:58:00
At first sight it might not be that important but when a customer is navigating by pages, in page 1 the id 218 will be shown last and when they change to page 2 the id 218 will be shown first… so basically the id 219 dissapear
Any idea how can I force the result to stick to the same order always?
If the data you sort them on is exact the same, the sorting is not actually done for those items between them. The sorting between both items is then arbitrary, and this arbitrary sorting can be different for both paginations. One solution is adding the ID column as second sorting condition.
This way 219 and 218 will always be sorted the same.
Something like this
$query
->orderByRaw("
CASE
WHEN tracking_eta IS NOT NULL THEN tracking_eta
ELSE arrival_date
END DESC,
id DESC
")
->paginate(23);
1