Eloquent power joins with Laravel

If you have some experience using databases, it is very likely you have used joins at least once in your career. Joins can be used for a bunch of different reasons, from selecting data from other tables to limiting the matches of your query.

I'm going to give a few examples on this post, so, in order to contextualize the examples, imagine we have the following database/models structure.

User -> hasMany -> Post
Post -> hasMany -> Comment
Post -> morphMany -> Image

On Laravel, using eloquent, joining the posts table would look something like this:

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

In case you want to join the posts and the comments table, your query would look something like this:

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

This is fine and we can understand, but we can do better. We already have all these relationships defined in our models, but we are repeating some of the implementation details when we write the join statements. So, instead of doing this, wouldn't be cool if you could just do the following?

// example 1
User::joinRelationship('posts');
 
// example 2
User::joinRelationship('posts.comments');

This is less code to read, and more importantly, easier code to read. It also hides any implementation details on how your relationships work. So, if your relationship changes, your joins will be automatically updated.

Introducing the Eloquent Power Joins package

We felt the way we did joins in our applications wasn't really the “Laravel way”, so we decided to introduce some of the Laravel fine touch into the way we do joins.

joinRelationship is a method introduced by the Eloquent Power Joins package. It works with any type of the existing Laravel relationships.

The installation of the package is as simple as just running the following composer command, and you should already have access to everything that will be mentioned on this post.

composer require kirschbaum-development/eloquent-joins-with-extra-powers

Joining polymorphic relationships

The joinRelationship method also works polymorphic relationships. Besides performing the regular join, it also performs the {morph}_type == Model::class check, as you can see below.

Post::joinRelationship('images')->toSql();
 
// select * from posts
// inner join images on images.imageable_id = posts.id AND images.imageable_id = 'App\\Post'

Joining nested relationships

And, it also works with nested relationships.

User::joinRelationship('posts.images')->toSql();
 
// select * from users
// inner join posts on posts.user_id = users.id
// inner join images on images.imageable_id = posts.id AND images.imageable_id = 'App\\Post'

It works with any relationship

The package will work with any of the native relationship types provided from Laravel.

BelongsToMany will make 2 joins considering the pivot table as well. HasManyThrough also makes the 2 necessary joins.

Eloquent Power Joins also applies any soft deletes clauses in case the related model uses the SoftDeletes trait.

But, the package also provides you with a few other very useful features, as you can see below.

Applying extra conditions to the joins

You can apply any extra condition you need to the joins, as well.

User::joinRelationship('posts', function ($join) {
    $join->where('posts.published', true);
});

For nested calls, and/or BelongsToMany or HasManyThrough relationships, you need to pass an array with the relationship as the key.

User::joinRelationship('posts.images', [
    'posts' => function ($join) {
        $join->where('posts.published', true);
    },
    'images' => function ($join) {
        $join->where('images.cover', true);
    },
]);

Using model scopes inside the 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::joinRelationshio('posts', function ($join) {
    // the $join instance here can access any of the scopes defined in the Post model 🤯
    $join->published();
});

Querying relationship existence

Querying relationship existence is a very powerful and convenient feature of Eloquent. However, it uses the where exists syntax which is not always the best and more performant choice, depending on how many records you have or the structure of your table.

This package also implements almost all Laravel methods for querying relationship existence using joins instead of where exists.

Performance

First thing to be aware here, is that the below example is one use-case where using joins over where exists is a lot more performant. You shouldn't assume this is true for every query, and you should use tools like Laravel Debugbar , Laravel Telescope or any tool of your choice to figure out what's best for YOUR use-case.

That said, below you can see one example of the MySQL CPU usage after deploying a change to use hasUsingJoins instead of has, in one of our client's application. MySQL was running on RDS, and this image was took from AWS CloudWatch.

Power joins metrics

Show me the code

Below, you can see the methods this package implements and also the Laravel equivalent.

User::has('posts');
User::has('posts.comments');
User::has('posts', '>', 3);
User::whereHas('posts', function ($query) {
    $query->where('posts.published', true);
});
User::doesntHave('posts');

Package implementations using joins

User::hasUsingJoins('posts');
User::hasUsingJoins('posts.comments');
User::hasUsingJoins('posts.comments', '>', 3);
User::whereHasUsingJoins('posts', function ($query) {
    $query->where('posts.published', true);
});
User::doesntHaveUsingJoins('posts');

Sorting your query results

Another useful feature os to sort your query results using a column from another table using the orderByUsingJoins method.

User::orderByUsingJoins('profile.city')->toSql();
// select "users".* from "users"
// inner join "user_profiles" on "user_profiles"."user_id" = "users"."id"
// order by "user_profiles"."city" asc

You can also sort your results by aggregations (COUNT, SUM, AVG, MIN or MAX).

For instance, to sort users with the highest number of posts, you would do this:

$users = User::orderByCountUsingJoins('posts.id', 'desc')->get();

Or, to get the list of posts sorted by the ones with comments which contain the highest average of votes.

$posts = Post::orderByAvgUsingJoins('comments.votes', 'desc')->get();

And you also have methods for SUM, MIN and MAX:

Post::orderBySumUsingJoins('…');
Post::orderByMinUsingJoins('…');
Post::orderByMaxUsingJoins('…');

Joins, the Laravel way

IMO, one of the advantages of the package is being able to write code in a more “Laravel way”. So, below you can see a few examples of how much better the code looks after using it. Any examples described here produces the EXACT same result.

Example 1

BuilderFile::select('builder_detail_builder_file.*')
    ->join('builder_detail_builder_file', 'builder_files.id', '=', 'builder_detail_builder_file.builder_file_id')
    ->join('builder_details', 'builder_details.id', '=', 'builder_detail_builder_file.builder_detail_id')
    ->join('documents', 'builder_details.document_id', '=', 'ces_documents.id');

With Eloquent Power Joins

BuilderFile::joinRelationship('details.document');

Example 2

Document::query()
    ->join('term_relations', function ($join) {
        $join
            ->on('term_relations.relationable_id', '=', 'ces_documents.id')
            ->where('term_relations.relationable_type', '=', Document::class);
    })
    ->join('terms', 'term_relations.term_id', '=', 'terms.id')
    ->join('vocabularies', 'terms.vocabulary_id', '=', 'vocabularies.id')
    ->get();