When retrieving model records, you may wish to limit your results based on the existence of a relationship. For example, imagine you want to retrieve all blog posts that have at least one comment. To do so, you may pass the name of the relationship to the has and orHas methods:
use App\Models\Post;
// Retrieve all posts that have at least one comment...
$posts = Post::has('comments')->get();
You may also specify an operator and count value to further customize the query:
// Retrieve all posts that have three or more comments...
$posts = Post::has('comments', '>=', 3)->get();
Nested has statements may be constructed using "dot" notation. For example, you may retrieve all posts that have at least one comment that has at least one image:
// Retrieve posts that have at least one comment with images...
$posts = Post::has('comments.images')->get();
If you need even more power, you may use the whereHas and orWhereHas methods to define additional query constraints on your has queries, such as inspecting the content of a comment:
use Illuminate\Database\Eloquent\Builder;
// Retrieve posts with at least one comment containing words like code%...
$posts = Post::whereHas('comments', function (Builder $query) {
$query->where('content', 'like', 'code%');
})->get();
// Retrieve posts with at least ten comments containing words like code%...
$posts = Post::whereHas('comments', function (Builder $query) {
$query->where('content', 'like', 'code%');
}, '>=', 10)->get();
If you would like to query for a relationship's existence with a single, simple where condition attached to the relationship query, you may find it more convenient to use the whereRelation, orWhereRelation, whereMorphRelation, and orWhereMorphRelation methods. For example, we may query for all posts that have unapproved comments:
use App\Models\Post;
$posts = Post::whereRelation('comments', 'is_approved', false)->get();
Of course, like calls to the query builder's where method, you may also specify an operator:
$posts = Post::whereRelation(
'comments', 'created_at', '>=', now()->subHour()
)->get();
When retrieving model records, you may wish to limit your results based on the absence of a relationship. For example, imagine you want to retrieve all blog posts that don't have any comments. To do so, you may pass the name of the relationship to the doesntHave and orDoesntHave methods:
use App\Models\Post;
$posts = Post::doesntHave('comments')->get();
If you need even more power, you may use the whereDoesntHave and orWhereDoesntHave methods to add additional query constraints to your doesntHave queries, such as inspecting the content of a comment:
use Illuminate\Database\Eloquent\Builder;
$posts = Post::whereDoesntHave('comments', function (Builder $query) {
$query->where('content', 'like', 'code%');
})->get();
You may use "dot" notation to execute a query against a nested relationship. For example, the following query will retrieve all posts that do not have comments; however, posts that have comments from authors that are not banned will be included in the results:
use Illuminate\Database\Eloquent\Builder;
$posts = Post::whereDoesntHave('comments.author', function (Builder $query) {
$query->where('banned', 0);
})->get();
Sometimes you may want to count the number of related models for a given relationship without actually loading the models. To accomplish this, you may use the withCount method. The withCount method will place a {relation}_count attribute on the resulting models:
use App\Models\Post;
$posts = Post::withCount('comments')->get();
foreach ($posts as $post) {
echo $post->comments_count;
}
By passing an array to the withCount method, you may add the "counts" for multiple relations as well as add additional constraints to the queries:
use Illuminate\Database\Eloquent\Builder;
$posts = Post::withCount(['votes', 'comments' => function (Builder $query) {
$query->where('content', 'like', 'code%');
}])->get();
echo $posts[0]->votes_count;
echo $posts[0]->comments_count;
You may also alias the relationship count result, allowing multiple counts on the same relationship:
use Illuminate\Database\Eloquent\Builder;
$posts = Post::withCount([
'comments',
'comments as pending_comments_count' => function (Builder $query) {
$query->where('approved', false);
},
])->get();
echo $posts[0]->comments_count;
echo $posts[0]->pending_comments_count;
Using the loadCount method, you may load a relationship count after the parent model has already been retrieved:
$book = Book::first();
$book->loadCount('genres');
If you need to set additional query constraints on the count query, you may pass an array keyed by the relationships you wish to count. The array values should be closures which receive the query builder instance:
$book->loadCount(['reviews' => function (Builder $query) {
$query->where('rating', 5);
}])
If you're combining withCount with a select statement, ensure that you call withCount after the select method:
$posts = Post::select(['title', 'body'])
->withCount('comments')
->get();
In addition to the withCount method, Eloquent provides withMin, withMax, withAvg, withSum, and withExists methods. These methods will place a {relation}_{function}_{column} attribute on your resulting models:
use App\Models\Post;
$posts = Post::withSum('comments', 'votes')->get();
foreach ($posts as $post) {
echo $post->comments_sum_votes;
}
If you wish to access the result of the aggregate function using another name, you may specify your own alias:
$posts = Post::withSum('comments as total_comments', 'votes')->get();
foreach ($posts as $post) {
echo $post->total_comments;
}
Like the loadCount method, deferred versions of these methods are also available. These additional aggregate operations may be performed on Eloquent models that have already been retrieved:
$post = Post::first();
$post->loadSum('comments', 'votes');
If you're combining these aggregate methods with a select statement, ensure that you call the aggregate methods after the select method:
$posts = Post::select(['title', 'body'])
->withExists('comments')
->get();
