How to get Records order by dynamic array of values?

Question

I need to get Records so that an array of ids come at top of the collection.

$ids = [15, 20];

I tried :

 $list =  $list->orderByRaw("field(id,".implode(',',$id).")"); 

But this is working only with whereIn :

$list =  $list->whereIn('id',$ids)->orderByRaw("field(id,".implode(',',$id).")"); 

But I need to fetch all records but Ids 15 and 20 at the top. How to achieve this.

Answer 1

you need to use MySQL derived table and union here

note: when you are using union with an order by then you must set a limit otherwise it will not work.

$ids = [15, 20];
DB::table(function($query) use ($ids) {
  $query->from('users')
    ->whereIn('id',$ids)
    ->orderByRaw("field(id,".implode(',',$ids).")")
    ->limit(count($ids))
    ->union(DB::table('users')->whereNotIn('id',$ids));
},'users1')

Your Query :

select * from (
      (select * from `users` where `id` in (?, ?) order by field(id,15,20) limit 2)
    union 
      (select * from `users` where `id` not in (?, ?))
  ) as `users1`