Hello,
I am looking for a possibility to get a list of users including the maximum article price per user. If I do it like this, it is working:
$users=User::withMax('articles','price')->get();
However, I only want to consider articles which are active, so I am thinking of doing something like that, but this is failing
$users=User::withMax('articles','price')->where('articles.active',true)->get();
When I am looking at the generated subquery it is obvious, why it is failing:
select `users`.* (select max(`articles`.`price`) from `articles` where `user`.`id` = `article`.`user_id`) as `article_max_price` from `users` where `articles`.`active` = 1
The where-clause has to be in the subquery, not in the main query. How can I change my code so that the where clause will be in the subquery?
I think, this will work.
$users = User::withCount(['articles as article_max_price' => function ($query) {
$query->selectRaw('max(price)')->where('active', true);
}])->get();
or I checked source and you can pass an array to the withMax method as well.
$users = User::withMax(['articles' => function ($query) {
$query->where('active', true);
}], 'price')->get();
@MichalOravec thank you, even two years later! The array method works perfectly and reads nicely.
Please or to participate in this conversation.