Laravel order By name in ascending order except some specific rows

user_4983101 0
Viewed 76

I'm building an API to return shops from a database in ascending order except that it should begin with all shops named swiva eg Swiva Electronics, Swiva Gas, Swiva Mall etc. then proceed to all the other shops in ascending order. I have tried the following code

       $shops=Shop::join('users','users.id','=','shops.owner_id')
        ->select('shops.id','shops.name','users.email','users.tel','users.estate','shops.logo')
        ->orderBy('shops.name')
        ->get()
        ->partition(function ($item) {
            return $item->name != 'Swiva Electronics';
        })->flatten();

but that is not doing what I want as

  1. It is not beginning with the intended row rather it is ending with it.
  2. It is only working with one row only, I do not know how to specify all the other rows

Note that the output of this query is being returned as a json as follows

        if(!$shops->isEmpty()){
            return response()->json([
            'success'=>true,
            'shops'=> $shops
        ]);
    }else if($shops->isEmpty()){
            return response()->json([
            'success'=>false,
            'message'=> 'There exists no subscribed shops from '
            ]);
        }

How can I achieve the intended output either through formatting the JSON( Which I do not know how to do) or by using eloquent functions.

laravel-9 eloquent json
1 Answers

You can use the Field Function of MySQL to achieve this see: https://www.w3schools.com/sql/func_mysql_field.asp

$shops = Shop::join('users','users.id','=','shops.owner_id')
        ->select('shops.id', 'shops.name', 'users.email', 'users.tel', 'users.estate', 'shops.logo')
        ->orderByRaw("FIELD(shops.`name`, 'Swiva Electronics', 'Swiva Gas', 'Swiva Mall') DESC, shops.`name`")
        ->get()

As we can't add all names in FIELD options by default it will list all other names first Use DESC order to invert it will move those names first in results and then sort the all other shop names by adding simple ASC order by shop names.

user_49952570
Related