Laravel在1个查询中多次计数

Im new to this Framework, i dont know how to optimize it using db::raw count and aliases and display it to my blade.php using @foreach

im trying to optimize my code, my goals is to count pallet_conditions and store it to my aliases, i dont want to count it one by one like what i did on this code

this is my code not optimize:

//computing the total rapairable
$repairable_total = DB::table('liip_psrm_items')
->where('psrm_items_id', '=' , $psrm_maintenance->id)
->where('pallet_condition', '=', 1)
->count();
//REPAIRABLE

//computing the total good pallets
$good_total = DB::table('liip_psrm_items')
->where('psrm_items_id', '=' , $psrm_maintenance->id)
->where('pallet_condition', '=', 0)
->count();
//GOOD

this is the code, what i wanted to learn. just to minimize, and use aliases

$result = DB::table('liip_psrm_items')
->select(DB::raw('COUNT(liip_psrm_items.pallet_condition = 0 ) AS condition_1',
                 'COUNT(liip_psrm_items.pallet_condition = 1 ) AS condition_2'))                      
                ->where('psrm_items_id', '=' , $psrm_maintenance->id)
                ->get();

Try to pass a closure like so:

$results = DB::table('liip_psrm_items')
->where('psrm_items_id', '=' , $psrm_maintenance->id)
->where(function($query){
   $query->where('pallet_condition', 1)
      ->orWhere('pallet_condition', 0);
})->count();

You can't use single query for two different results, which has totally opposite conditions.

Case 1. You are trying to count the items where pallet_condition = 1;

Case 2. You are trying to count the items where pallet_condition = 0;

Now you want to merge these two cases into single query, which is impossible...

So, For these two cases, you have to use either separate queries ( what you did already )

or you can use single query to grab all the items and then use PHP to separate them.

Like:

$total_items = DB::table('liip_psrm_items')
   ->where('psrm_items_id', '=' , $psrm_maintenance->id)
   ->get();

$repairable_count = count(array_filter($total_items, function($item){
   return (bool)$item->pallet_condition;
}));

$good_count = count(array_filter($total_items, function($item){
   return !(bool)$item->pallet_condition; //just inverse of the above condition
}));

i hope this might help.