Laravel Eloquent join with composite keys

2 min readSep 9, 2021

Imagine we have a relationship where a post can have many comments, a relation method will be written Eloquent model as:

class Post extends Model
{
    /**
     * Get the comments for the blog post.
     */
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

Then we can retrieve comments by a post like following

$post->comments()->get();

Despite we could define all tables relationship in the model, however, to write a join query in Laravel would look like this:

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

We have to repeat the relationship detail every time we need a join.

Eloquent Power Join

Thanks to Kirschbaum, we could utilize the package Eloquent Power Join to simplify the above join code into:

Post::joinRelationship('comments');

How about multi-condition join?

SELECT posts.* FROM posts INNER JOIN comments 
    ON posts.id = comments.post_id
    AND posts.user_id = comments.user_id

To produce the same result, Eloquent Power Join provides an extra join callback, allow us to append conditions like this:

Post::joinRelationship('comments', function ($join) {
    $join->on('posts.user_id', '=', 'comments.user_id');
});

You may have noticed, aren’t we still repeating ourselves to write multi-condition join? Why not define the multi-condition in the relationship in the model?

We need to solve 2 issues from here.

Firstly, Laravel doesn’t support composite keys in Eloquent relationships. This issue however could be solved with this package: Compoships.

Second, Eloquent Power Join doesn’t work together with Compoships.

Issue: https://github.com/kirschbaum-development/eloquent-power-joins/issues/60

And this is where the extension package jump in.

An Extension for Power Join

For that, I have written a package called eloquent-power-joins-with-compoships.

This package uses Eloquent Power Join and Compoships behind the scene, offer the ability to support both packages to work together.

Like many other composer packages, to install, all you need to do is run:

composer require kitloong/eloquent-power-joins-with-compoships

Now we are able define a multi-condition relationship in the model:

class Post extends Model
{
    /**
     * Get the comments for the blog post.
     */
    public function comments()
    {
        return $this->hasMany(
            Comment::class,
            ['id', 'user_id'],
            ['post_id', 'user_id'],
        );
    }
}

And still, write join in “Laravel way”:

Post::joinRelationship('comments');

We could save a lot of redundant join details from our code. I have successfully reduced a total 30% codes in one of my project.

Conclusion

With eloquent-power-joins-with-compoships, not only we able reduce code size, but more importantly, you can now define all relationships in a single place: Eloquent models. By doing that, when one day you need to revise or check your model relationships, you can save yourself from doing a global search against some column keywords.

Laravel Eloquent join with composite keys

Eloquent Power Join

An Extension for Power Join

Conclusion

References

Written by Kit Loong