Laravel count number of Male or Female from a relationship table

I have models below:

UserProgram user_id, program_id.

User

Profile, user_id, gender

Program

and I have a function gender in my UserProgram model

public function gender()
{
  $gender = "N/A";
  $user = User::find($this->user_id);
  if (!empty($user)) {
    $profile = Profile::where('user_id',$user->id)->first();
    if (!empty($profile)) {
        $gender = $profile->gender;
    }
  }
  return $gender;
}

Now I want to run a query that counts how many male or Female I have for a particular program. I want to be able to do something like this.

$male_count = UserProgram::with(‘gender)->where([

  ['program_id',104],[‘gender’,’Male’]

])->count();

Answer

The above won’t work as Laravel will be expecting gender() to return a Relation not a Model.

Assuming that you have a user relationship in your UserProgram model and you have a profile relationship in your user model, you can use whereHas:

$count = UserProgram::where('program_id', 104)
    ->whereHas('user.profile', function ($query) {
        $query->where('gender', 'Male');
    })
    ->count();