Wrap where clauses

Laravel relationship methods are great to abstract database relationships but they also work really well to abstract complex relationships that span through multiple tables.

❡An example of increased relationship

Let's say an Article has some Images and some Comments which also have some Images. The Image model is linked to the Article and Comment models via a polymorphic relationship.

class Article extends Model {
    // ...
    
    public function images() {
        return $this->morphMany(Image::class);
    }
    
    public function comments() {
        return $this->hasMany(Comment::class);
    }
}

Now, how would we go about fetching all images from an article including the images of its comments?

// App\Article
public function getAllImagesAttribute() {
    $commentImages = $this->comments->flatMap->images;
    return $this->images->merge($commentImages);
}

While this works fine, we have to fetch all comments and then for each of those comments fetch their images. If we have 100 comments, thats 101 queries just to fetch some images. How can we transform this into a simple Laravel relationship method that can be cached and requires only one database query?

// App\Article
public function allImages() {
    return $this->images()->orWhere(function ($query {        
        $query->where('imageable_type', 'App\Comment')
              ->whereExists(function ($query) {
                  $query->select(\DB::raw(1))
                        ->from('comments')
                        ->whereRaw('comments.id = image.imageable_id')
                        ->where('article_id', $this->id);
              });
    });
}

Let's run through that code:

1. We use the original images() relationship and add a "OR" where clause to extend it.
2. In our "OR" where clause, we only want images that are linked to comments. Note that we already have the images associates with our article within the first part of the "OR".
3. Finally we only want images where there exists a row from the comments table such that its id equals our imageable_id and such that its article_id matches the id of our article.

Easy right? Now we can fully leverage the allImages() method as if it was a regular relationship method:

$article->allImages; // Get them all.
$article->allImages()->latest()->first(); // Get the last once.
$article->allImages()->count(); // Count them.

❡The filtering problem

Similarly we should be able to filter our relationship method like so:

$article->allImages()->where('size', '<', 1000);

However, the filtering above will not work as expected. To understand why, we need to have a look at the SQL query it generates.

// This first part is generated by `$this->images()`
select * 
from `images` 
where `images`.`imageable_id` = ?             // (A)
    and `images`.`imageable_id` is not null   // (B)
    and `images`.`imageable_type` = ?         // (C)
    
    // This second part is our `orWhere` clause. (D)
    or (
        `imageable_type` = ? 
        and exists (
            select 1 
            from `comments` 
            where images.imageable_id = comments.id and `article_id` = ?
        )
    )
    
    // This third part is our size filtering.    (E)
    and `size` < ?

Because of the precedence of and over or, our size filter (E) only gets applied to our "OR" where clause (D), i.e. we are only filtering the images of the comments.

// instead of
A and B and C or D and E
// which translates to
(A and B and C) or (D and E)
// we want
(A and B and C or D) and E

❡Macro to the rescue

To fix this issue, we basically need to wrap all where clauses of our query builder into one nested where clause. I have created a macro that does that for you. I'll just paste it here with some comments if you're interested.

// App\Providers\AppServiceProvider
Relation::macro('wrapWhereClauses', function($boolean = 'and') {

    // Get the query of the relation.
    $query = $this->getBaseQuery();
    
    // Create a new nested where group.
    $whereGroup = $query->forNestedWhere();
    
    // Add all the query where clauses to the new where group.
    $whereGroup->wheres = $query->wheres;
    
    // Replace the query where clauses to include only this whereGroup.
    $query->wheres = [[
        'type' => 'Nested', 
        'query' => $whereGroup, 
        'boolean' => $boolean
    ]];

    // Return this to continue chaining.
    return $this;
});

Now just append this to your allImages() method.

// App\Article
public function allImages() {
    return $this->images()
                ->orWhere(...)
                ->wrapWhereClauses();
}

And now, any new where clause will be applied to all of the images as expected.