Any way to do simple count of relations with different 'where' clauses?

Question

I have this relation in User model

public function bulletins()
{
    return $this->hasMany('App\Bulletins','owner');
}

in controller I getting count of bulletins:

dump(
 User::where('id',Auth::id())
  ->withCount('bulletins')
  ->where('status','=',1)
  ->first()
);

This count all with status=1 but I also need status=0 and other parameters which in different table columns. On out I want something like this:

bulletin_counters->total =10//all
bulletin_counters->active =20//status=1
bulletin_counters->archive =30//status=0
bulletin_counters->deleted =40//deleted=1
etc...

Which is the best way to do this? I know that I can do many queries and manually assign this variables.

score 2 · Accepted Answer · answered May 06 '19 at 08:26

You should be able to customize the query which is generated by withCount

Try the following:

 ->withCount([
    'bullentins as bullentins_total',
    'bullentins as bullentins_active' => function($query) { $query->where('status', 1); },
    'bullentins as bullentins_archive' => function($query) { $query->where('status', 0); },
    'bullentins as bullentins_deleted' => function($query) { $query->where('deleted', 1); }
    ]);

Any way to do simple count of relations with different 'where' clauses?

1 Answers1