Using GROUP BY after executing a procedure in Laravel?-CodePudding

After executing the procedure i have table like this

SELECT ROW_NUMBER() OVER(ORDER BY t.name)new_id,
       t.name AS cat_name,
       f.id AS id_food 
FROM food AS f 
INNER JOIN categories AS t 
ON t.id = f.id_cat
WHERE f.quantity > 0 
ORDER BY t.name


| new_id  | cat_name | id_food |
|----     |------    | -----   |
| 1       | Seafood  | 1       |
| 2       | Seafood  | 3       |
| 3       | Seafood  | 5       |
| 4       | Beef     | 2       |
| 5       | Beef     | 16      |
| 6       | Special  | 7       |

This is my controller and JSON value from the procedure

public function first(){
    $fi = DB::select("CALL GetListCat()");
    return response()->json($fi);
    }

Result: [{"new_id":1,"cat_name":"Seafood"},
{"new_id":2,"cat_name":"Seafood"},
{"new_id":3,"cat_name":"Seafood"},
{"new_id":4,"cat_name":"Beef"},
{"new_id":5,"cat_name":"Beef"},
{"new_id":6,"cat_name":"Special"}]

Now i want to use GROUP BY on the cat_name field so when i return a JSON, it should only get the 1st value of each cat_name like this (I need the new_id field to stay)

| new_id  | cat_name | id_food |
|----     |------    | -----   |
| 1       | Seafood  | 1       |
| 4       | Beef     | 2       |
| 6       | Special  | 7       |

Result desired: [{"new_id":1,"cat_name":"Seafood"},
{"new_id":4,"cat_name":"Beef"},
{"new_id":6,"cat_name":"Special"}]

How can I achieve this?

CodePudding user response：

Simply turn your array into a Laravel collection so you can operate on it more easily:

$fi = [
    ["new_id" => 1, "cat_name" => "Seafood"],
    ["new_id" => 2, "cat_name" => "Seafood"],
    ["new_id" => 3, "cat_name" => "Seafood"],
    ["new_id" => 4, "cat_name" => "Beef"],
    ["new_id" => 5, "cat_name" => "Beef"],
    ["new_id" => 6, "cat_name" => "Special"],
];
$coll = collect($fi)->unique("cat_name");
dump($coll->values()->all());

Output:

=> [
     [
       "new_id" => 1,
       "cat_name" => "Seafood",
     ],
     [
       "new_id" => 4,
       "cat_name" => "Beef",
     ],
     [
       "new_id" => 6,
       "cat_name" => "Special",
     ],
   ]