foreach in select eloquen in laravel?-CodePudding

In controller :

    $productId = 1;
    $productNew = 1;
    $productHot = 2;
    $productDefective = 3;
    
    $product = Product::select('product_type', DB::raw('count(*) as total'), DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d') as date"),
                    DB::raw('SUM(product_type = ' . $productNew . ') as productNew'), 
                    DB::raw('SUM(product_type = ' . $productHot . ') as productHot'),
                    DB::raw('SUM(product_type = ' . $productDefective . ') as productDefective'))
                    ->where('product_id', $productId)->groupBy('date', 'product_type')->get()->toArray();

Now I want to create a foreach loop in select, can I?

Eg:

$productType = [
   'productNew' => 1,
   'productHot' => 2,
   'productDefective' => 3
];
foreach ($productType as $key => $type) {
   DB::raw('SUM(product_type = ' . $type . ') as '.$key.');
}

Give me any ideas.Thanks

CodePudding user response：

You can use addSelect() to add columns to your query:

$query = Product::where('product_id', $productId)->groupBy('date', 'product_type');

$productType = [
   'productNew' => 1,
   'productHot' => 2,
   'productDefective' => 3
];
foreach ($productType as $key => $type) {
    $query->addSelect(DB::raw('SUM(product_type = ' . $type . ') as ' . $key));
}

$result = $query->get()->toArray();