I have the following relation:
I have a many-to-many relation between Course and Users, and then again a has-many relation between Users and Certificates.
Now here is way how to get all users from one course, who have a certificate in the same course:
$user = $course->users()
->whereHas('certificates', function ($query) use($course){
$query->where('course_certificates.course_id', '=', $course->id);
})->first();
This will return a user with certificates $user->certificates.
This is the query.
SELECT *
FROM "course_users"
WHERE "course_users"."course_id" = ? AND
"course_users"."course_id" IS NOT NULL AND
EXISTS (
SELECT *
FROM "course_certificates"
WHERE "course_users"."id" = "course_certificates"."user_id" AND
"course_certificates"."course_id" = ?
)
If I use a join instead:
$user = $course->users()
->join('course_certificates', 'course_certificates.user_id', '=', 'course_users.id')
->where('course_certificates.course_id', '=', $course->id)
->first();
Then I get the same user, however the relationship $user->certificates is empty?
SELECT *
FROM "course_users"
INNER JOIN "course_certificates"
ON "course_certificates"."user_id" = "course_users"."id"
WHERE "course_users"."course_id" = ? AND
"course_users"."course_id" IS NOT NULL AND
"course_certificates"."course_id" = ?
Why is that?