Join on Eloquent breaks Relationships?

Viewed 294

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?

1 Answers

I am not 100% sure, but if you use join I think laravel do not understand whatever table you are joining it with that it is a relation, it is just a join, it could mean anything, but the first query you are telling it what the relation means (whereHas some data for X relation).


Looking at the source code, I can confirm what I am saying: join and whereHas (and has).

getRelated() does the magic (adds the data to your relation). See that join does not do anything of this, it literally joins queries, nothing else related to relations. Also join comes from Query/Builder and any other method about relations comes from Eloquent/Builder.

Related