Querying and Eager Loading complex relations in Laravel

Laravel is a PHP framework that uses Eloquent, a powerful and amazing ORM that allows you to do complex SQL queries in a very easy way. But sometimes you need more, and here I’m gonna give you an interesting tip that can bring you a lot of flexibility.

When dealing with simple database queries in Laravel maybe you don’t have so many challenges, because it’s really very easy to use. But when your app starts growing or even your boss needs some type of reports, then you start thinking “How can I do that with Laravel”?

In some cases, you know how to do that using pure SQL, but it’s not so good to write a beautiful code and suddenly a SQL query in the middle of everything. Then you start trying to find how you can do that with Eloquent.

Sometimes you have to query and use the matching results, the same ones, actually. This is a case where the same logic you use for filtering is used for the relation itself. I’m gonna explain it better below.

The Scenario

I like to use the simple case, a blog app. It isn’t new, I know, but I think it is simple enough to make you understand how things work, actually, how relations work, in this case.

Thinking the Laravel/Eloquent way, we have a Post model that hasMany() Comment and then the Comment model belongsTo() a Post.

use Illuminate\Database\Eloquent\Model as Eloquent;

class Post extends Eloquent
{
    /**
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

class Comment extends Eloquent
{
    /**
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}

So it’s easy to get comments from a given post:

$post = Post::find(1);
$comments = $post->comments;

Nothing new here.

Querying

According to the Laravel’s documentation:

If you need even more power, you may use the whereHas and orWhereHas methods to put “where” conditions on your has queries. These methods allow you to add customized constraints to a relationship constraint, such as checking the content of a comment:

// Retrieve all posts with at least one comment containing words like foo%
$posts = App\Post::whereHas('comments', function ($query) {
    $query->where('content', 'like', 'foo%');
})->get();

The Relation

The problem is that after querying all those posts according to the comments they have, the relation itself is not filtered, it contains all comments related to that post:

$post = Post::whereHas(...)->first();
$post->comments; // all post comments

And sometimes you want to use $post->comments with only those ones that contain words like foo%, not every comment that post has. In other words, you’d like to use the same logic that you have been using for filtering posts in the comments relation.

The Solution

Dealing with relations is very easy in Laravel, even when “eager loading” them. This is done using the with() method, and every Laravel developers already know that. Nothing new again.

The tip here is to reuse the same function that you used with the whereHas() method with the with() one. So let’s save that anonymous function to a local variable called $filter, for example:

$posts = Post::whereHas('comments', $filter = function ($query) {
    $query->where('content', 'like', 'foo%');
})->get();

And then reuse it with the with() call:

$posts = Post::whereHas('comments', $filter = function ($query) {
    $query->where('content', 'like', 'foo%');
})->with(['comments' => $filter])->get();

So we’re using the same anonymous function for querying and eager loading the same comments() relation, through the $filtered variable, which is a function, a callable.

Now if you take a look on $post->comments you have only those comments that contain words like foo%. So, for example, if you have a post with 2 comments:

$post = Post::find(1);

$post->comments()->saveMany([
    new Comment(['content' => 'foo is good']),
    new Comment(['content' => 'bar is better']),
]);

When querying using the same function for the whereHas() and with() methods you have only one comment in the relation:

$post = Post::whereHas(...)->with(...)->get()->first();
$this->assertCount(1, $post->comments);
$this->assertEquals('foo is good', $post->comments->first()->content);

Conclusion

Working with complex queries in Laravel is not difficult but they can bring some complexity to your logic because you’re not thinking in SQL, but in objects and relations, which is good, in my opinion.

This solution I gave is very interesting when dealing with reports because usually, you have to filter models and dealing with relations that have the same filter you have just applied, like the example above.

It’s easy to think and this can bring you a lot of flexibility, because you can encapsulate a different logic inside the “same query”, reusing anonymous functions, which is faster to implement.

If you have any other interesting Eloquent approach let me know in the comments. Eloquent is an ORM that I like a lot and it would be amazing to understand more example of complex queries.

Talking about Eloquent, take a look on my open source project Corcel, that maps the WordPress database into Laravel models, using Eloquent, of course.