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:
In case you want to join the posts and the comments table, your query would look something like this:
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?
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.
Table of Contents
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.
On any model you want to be able to use the methods described below, you should use the following trait:
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.
Joining nested relationships
And, it also works with nested relationships.
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.
For nested calls, and/or BelongsToMany or HasManyThrough relationships, you need to pass an array with the relationship as the key.
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:
When joining relationships, you can use the scopes defined in the model being joined. How cool is this?
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.
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 powerJoinHas instead of has, in one of our client’s application. MySQL was running on RDS, and this image was took from AWS CloudWatch.
Show me the code
Below, you can see the methods this package implements and also the Laravel equivalent.
Laravel Native Methods
Package implementations using joins
Sorting your query results
Another useful feature os to sort your query results using a column from another table using the orderByPowerJoins method.
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:
Or, to get the list of posts sorted by the ones with comments which contain the highest average of votes.
And you also have methods for SUM, MIN and MAX:
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
With Eloquent Power Joins
Example 2
With Eloquent Power Joins
That’s it. Hopefully this package is going to be as useful to you as it is to us. Happy joining!
This content was originally published here.