1

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.

Jignesh Joisar
  • 13,720
  • 5
  • 57
  • 57
Neha
  • 2,136
  • 5
  • 21
  • 50

3 Answers3

1

You should better do this task manually in PHP, not in MySQL.

$keyed_list = $list->keyBy('id');
$ordered_list = collect();

foreach ($ids as $id) {
    $ordered_list[] = $keyed_list[$id];
}

//Add the rest of items
$ordered_list = $ordered_list->merge($keyed_list->except($ids));
Stalinko
  • 3,319
  • 28
  • 31
1

check this answer https://stackoverflow.com/a/38574861/11219477

$list->sortBy(function($model) use ($ids){
    return array_search($model->getKey(), $ids);
}
Hanan Alhasan
  • 298
  • 3
  • 15
  • I appreciate your answer. thank you for your time. But in my case i need to do this before getting data. i am also using take(10). so 10 records may or may not have that id. – Neha Sep 13 '21 at 14:09
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`
Jignesh Joisar
  • 13,720
  • 5
  • 57
  • 57
  • Nice answer. I'd better use completely raw queries instead of query builder here. Because the builder doesn't help, but instead adds more complexity. – Stalinko Sep 13 '21 at 18:22