I have such entities as: user, contact, activity, touch.
One user has many contacts. One contact has many activities. Activity is polymorphic and may be a touch or something else. Touch has a type (email/visit/call).
I need to count how many contacts a user has, which have activities which are touches with visit type.
So this is how to count number of contacts which have activities no matter what type:
$this->model->find($id)->contacts()->whereHas('activities')->count();
But how to take into account only activities which are touches with type visit?
$this->model->find($id)->contacts()->whereHas('activities', function($query) {
$query->whereType('App\EloquentModels\Touch') // And touch must have type = visit
)->count();
update
Well, seems like I achivied some result with query builder. One note: an activity table also polymorphic for contacts because it might be as well belong to other entities. Shouldn't be a big difference. What I want to be pretty written without joins is:
\DB::table('users')->join('contacts', 'contacts.user_id', '=', 'users.id')
->join('activities', 'activitable_id', '=', 'contacts.id')
->join('touches', 'touches.id', '=', 'activities.child_id')
->where('activities.activitable_type', '=', 'CRM\EloquentModels\Contact')
->where('activities.child_type', '=', 'CRM\EloquentModels\Touch')
->where('touches.type', '=', 'visit')
->distinct()
->count('contacts.id');
I think you're missing the filter part, if you get() the query, then you will retrieve a Collection, then you can run a filter for that collection to get only what you seek.
$this->model
->find($id)
->contacts()
->whereHas('activities', function($query) {
$query->whereType('App\EloquentMode\Touch')
->get()
->filter(function($touch){
if($touch->type == 'visit') return $touch;
});
})
->count();