The issue you're encountering is due to the way you're applying the where clause on the homePhone.phone_type field. When you use a LEFT JOIN, it will return all rows from the left table (in this case, the result of the previous joins) and the matched rows from the right table (phone_numbers). If there is no match, the result is NULL for columns from the right table.
However, when you add a where condition on a column from the right table (like homePhone.phone_type), it effectively turns the LEFT JOIN into an INNER JOIN because it filters out rows where homePhone.phone_type is NULL.
To fix this, you should move the condition on homePhone.phone_type to the on clause of the leftJoin. Here's how you can adjust your query:
DB::table('candidates')
->join('audition_results', 'candidates.id', '=', 'audition_results.candidate_id')
->join('students', 'candidates.student_id', '=', 'students.id')
->join('users', 'students.user_id', '=', 'users.id')
->leftJoin('phone_numbers AS homePhone', function($join) {
$join->on('users.id', '=', 'homePhone.user_id')
->where('homePhone.phone_type', 'home');
})
->where('candidates.version_id', $this->versionId)
->where('candidates.status', 'registered')
->where('audition_results.accepted', 1)
->where('audition_results.acceptance_abbr', 'mx')
->select('audition_results.total')
->orderBy('candidates.id')
->count('candidates.id');
By moving the where('homePhone.phone_type', 'home') condition into the on clause of the leftJoin, you ensure that the join condition is applied correctly without filtering out rows where homePhone is NULL. This should give you the expected number of rows.
