12

I have two queries, the first one gives me an array of ids, that is in a specific order. Then that array of ids I pass it to the second query like so:

 Operation::whereIn('id', $ids)->get();

But when I output the result of that query, the order has changed, if the array $ids was something like (4,2,6,9) which is the order I wanted the results to be in, the output will give me 2,4,6,9. How can I avoid that?

Jarek Tkaczyk
  • 78,987
  • 25
  • 159
  • 157
Elaine Marley
  • 2,143
  • 6
  • 50
  • 86

3 Answers3

24

MySQL way of sorting with order same as in where in clause:

$ids; // array of ids
$placeholders = implode(',',array_fill(0, count($ids), '?')); // string for the query

Operation::whereIn('id', $ids)
   ->orderByRaw("field(id,{$placeholders})", $ids)->get();
Jarek Tkaczyk
  • 78,987
  • 25
  • 159
  • 157
6

You can do

$idsImploded = implode(',',$ids);
Operation::whereIn('id', $ids)->orderByRaw("FIND_IN_SET('id','$idsImploded')")->get();

It's a problem where MySql doesn't return the result in the order you specify them, so you need to reorder them after that.

A similar solution can be found here: avoid Sorting by the MYSQL IN Keyword

Community
  • 1
  • 1
Yasen Slavov
  • 787
  • 4
  • 16
  • Syntax error or access violation: 1582 Incorrect parameter count in the call to native function 'FIND_IN_SET' (SQL: select * from `operations` where `id` in (42, 3, 4, 37, 16, 38, 39, 14, 17) order by FIND_IN_SET(id,42,3,4,37,16,38,39,14,17 )) – Elaine Marley Oct 03 '14 at 09:31
  • Fixed. I forgot to add the ' ' to the FIND_IN_SET arguments. – Yasen Slavov Oct 03 '14 at 10:01
1

If you have the sorting order in 4,2,6,9, you can fetch these rows, then use php to sort.

ajreal
  • 46,720
  • 11
  • 89
  • 119
  • I need to be able to use eloquent later on to get more info from tables related to Operations, how can I sort the objet returned by that query without losing that capability? – Elaine Marley Oct 03 '14 at 09:35
  • As in, in my view I want to do $operations->event->name, where event is a related table and operations is what comes from the query in my question. – Elaine Marley Oct 03 '14 at 09:36
  • you can put all the info first then do sort – ajreal Oct 03 '14 at 09:45
  • I'd rather not do all that on the view, I prefer the other solution, but I guess yours would work aswell – Elaine Marley Oct 03 '14 at 09:50