Skip to main content

Join Relationship

Let's say you have a User model with a hasMany relationship to the Post model. If you want to join the tables, you would usually write something like:

User::select('users.*')->join('posts', 'posts.user_id', '=', 'users.id');

This package provides you with a new joinRelationship() method, which does the exact same thing.

User::joinRelationship('posts');

Both options produce the same results. In terms of code, you didn't save THAT much, but you are now using the relationship between the User and the Post models to join the tables. This means that you are now hiding how this relationship works behind the scenes (implementation details). You also don't need to change the code if the relationship type changes. You now have more readable and less overwhelming code.

But, it gets better when you need to join nested relationships. Let's assume you also have a hasMany relationship between the Post and Comment models and you need to join these tables, you can simply write:

User::joinRelationship('posts.comments');

So much better, wouldn't you agree?! You can also left or right join the relationships as needed.

User::leftJoinRelationship('posts.comments');
User::rightJoinRelationship('posts.comments');

Joining polymorphic relationships​

Let's imagine, you have a Image model that is a polymorphic relationship (Post -> morphMany -> Image). Besides the regular join, you would also need to apply the where imageable_type = Post::class condition, otherwise you could get messy results.

Turns out, if you join a polymorphic relationship, Eloquent Power Joins automatically applies this condition for you. You simply need to call the same method.

Post::joinRelationship('images');

You can also join MorphTo relationships.

Image::joinRelationship('imageable', morphable: Post::class);

Note: Querying morph to relationships only supports one morphable type at a time.

Applying conditions & callbacks to the joins

Now, let's say you want to apply a condition to the join you are making. You simply need to pass a callback as the second parameter to the joinRelationship method.

User::joinRelationship('posts', fn ($join) => $join->where('posts.approved', true))->toSql();

You can also specify the type of join you want to make in the callback:

User::joinRelationship('posts', fn ($join) => $join->left());

For nested calls, you simply need to pass an array referencing the relationship names.

User::joinRelationship('posts.comments', [
    'posts' => fn ($join) => $join->where('posts.published', true),
    'comments' => fn ($join) => $join->where('comments.approved', true),
]);

For belongs to many calls, you need to pass an array with the relationship, and then an array with the table names.

User::joinRelationship('groups', [
    'groups' => [
        'groups' => function ($join) {
            // ...
        },
        // group_members is the intermediary table here
        'group_members' => fn ($join) => $join->where('group_members.active', true),
    ]
]);

Using model scopes inside the join callbacks 🤯​

We consider this one of the most useful features of this package. Let's say, you have a published scope on your Post model:

    public function scopePublished($query)
    {
        $query->where('published', true);
    }

When joining relationships, you can use the scopes defined in the model being joined. How cool is this?

User::joinRelationship('posts', function ($join) {
    // the $join instance here can access any of the scopes defined in Post 🤯
    $join->published();
});

When using model scopes inside a join clause, you can't type hint the $query parameter in your scope. Also, keep in mind you are inside a join, so you are limited to use only conditions supported by joins.

Using aliases​

Sometimes, you are going to need to use table aliases on your joins because you are joining the same table more than once. One option to accomplish this is to use the joinRelationshipUsingAlias method.

Post::joinRelationshipUsingAlias('category.parent')->get();

In case you need to specify the name of the alias which is going to be used, you can do in two different ways:

  1. Passing a string as the second parameter (this won't work for nested joins):
Post::joinRelationshipUsingAlias('category', 'category_alias')->get();
  1. Calling the as function inside the join callback.
Post::joinRelationship('category.parent', [
    'category' => fn ($join) => $join->as('category_alias'),
    'parent' => fn ($join) => $join->as('category_parent'),
])->get()

For belongs to many or has many through calls, you need to pass an array with the relationship, and then an array with the table names.

Group::joinRelationship('posts.user', [
    'posts' => [
        'posts' => fn ($join) => $join->as('posts_alias'),
        'post_groups' => fn ($join) => $join->as('post_groups_alias'),
    ],
])->toSql();

Select * from table​

When making joins, using select * from ... can be dangerous as fields with the same name between the parent and the joined tables could conflict. Thinking on that, if you call the joinRelationship method without previously selecting any specific columns, Eloquent Power Joins will automatically include that for you. For instance, take a look at the following examples:

User::joinRelationship('posts')->toSql();
// select users.* from users inner join posts on posts.user_id = users.id

And, if you specify the select statement:

User::select('users.id')->joinRelationship('posts')->toSql();
// select users.id from users inner join posts on posts.user_id = users.id

Soft deletes​

When joining any models which uses the SoftDeletes trait, the following condition will be also automatically applied to all your joins:

and "users"."deleted_at" is null

In case you want to include trashed models, you can call the ->withTrashed() method in the join callback.

UserProfile::joinRelationship('users', fn ($join) => $join->withTrashed());

You can also call the onlyTrashed model as well:

UserProfile::joinRelationship('users', ($join) => $join->onlyTrashed());

Extra conditions defined in relationships​

If you have extra conditions in your relationship definitions, they will get automatically applied for you.

class User extends Model
{
    public function publishedPosts()
    {
        return $this->hasMany(Post::class)->published();
    }
}

If you call User::joinRelationship('publishedPosts')->get(), it will also apply the additional published scope to the join clause. It would produce an SQL more or less like this:

select users.* from users inner join posts on posts.user_id = posts.id and posts.published = 1

Global Scopes​

If your model have global scopes applied to it, you can enable the global scopes by calling the withGlobalScopes method in your join clause, like this:

UserProfile::joinRelationship('users', fn ($join) => $join->withGlobalScopes());

There's, though, a gotcha here. Your global scope cannot type-hint the Eloquent\Builder class in the first parameter of the apply method, otherwise you will get errors.