Laravel multiple count in 1 Query

Question

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();

Your Laravel code looks fine to me. What is your actual question? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 26, 2018 at 5:41

Zeeshan DaDa · Accepted Answer · 2021-06-17 00:04:37Z

4

To count at multiple condition I used this approach

 $lastMonthInvoices = Invoice::select(DB::raw("(COUNT(*)) as count"), DB::raw('SUM(total) as total'),'status')
        ->whereDate('created_at', '>', Carbon::now()->subMonth())
        ->groupBy('status')
        ->get();

i got the result with groupBy Status and in each group total number of records as count & also their sum as total

these two snaps are of one query result

answered Jun 17, 2021 at 0:04

Zeeshan DaDa

3173 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jaskaran Singh · Accepted Answer · 2018-10-26 06:03:47Z

3

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.

answered Oct 26, 2018 at 6:03

Jaskaran Singh

9248 silver badges8 bronze badges

1 Comment

BlackLeaf Over a year ago

thank for the suggestion @jaskaran appriciate it. the above answer jonjie solve my problem

β.εηοιτ.βε · Accepted Answer · 2020-05-30 17:57:15Z

2

You can, first group by, then get count

Like :

DB::table('liip_psrm_items')
  ->groupBy('pallet_condition')
  ->select('pallet_condition', DB::raw('count(*) as total'))
  ->get();

edited May 30, 2020 at 17:57

β.εηοιτ.βε

40.3k14 gold badges81 silver badges104 bronze badges

answered May 30, 2020 at 17:08

jaber sabzali

435 bronze badges

Comments

Blues Clues · Accepted Answer · 2018-10-26 05:48:33Z

-1

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();

answered Oct 26, 2018 at 5:48

Blues Clues

1,9084 gold badges40 silver badges84 bronze badges

2 Comments

Blues Clues Over a year ago

@YasserCHENIK You can file a bug at github.com/laravel/laravel if you feel that where method is useless.

Yasser CHENIK Over a year ago

in other words : [ i will wake up tomorrow if you = "like it" or if you ="don't like it" ] is equivalent to [ i will wake up tomorrow ] because it's boolean with all possible conditions inside it, so your where statement is indeed useless my friend :l

Collectives™ on Stack Overflow

Laravel multiple count in 1 Query

4 Answers 4

Comments

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related