Laravel雄辩-总在哪里 [英] Laravel Eloquent - Where In All
问题描述
在 Laravel 4.2 中,我试图实现一个查询,该查询返回具有所有特定活动的所有用户.到目前为止,我有一个查询,该查询返回具有许多活动之一的所有用户:
In Laravel 4.2, I am trying to achieve a query that returns all users, that have all of certain activities. As of now, I have a query that returns all users that have one of many activities:
//$selectedActivities being an array
$userByActivities = User::with('activities')
->whereHas('activities', function($query) use($selectedActivities){
$query->whereIn('id', $selectedActivities);
})->get();
更明确一点:给定活动 a,b,c .我正在寻找具有活动 a AND b AND c 的所有用户.我的查询返回活动为 a OR b OR c 的所有用户.
To be more clear: given activities a,b,c. I am looking for all users that have activity a AND b AND c. My query returns all users that have activity a OR b OR c.
谢谢您的帮助.
lukasgeiter 提供的解决方案会导致以下查询:
The solution offered by lukasgeiter results in following query:
select * from `users` where
(select count(*) from `activities` inner join `activity_user` on `activities`.`id` = `activity_user`.`activity_id` where `activity_user`.`user_id` = `users`.`id` and `id` = '7') >= 1
and (select count(*) from `activities` inner join `activity_user` on `activities`.`id` = `activity_user`.`activity_id` where `activity_user`.`user_id` = `users`.`id` and `id` = '3') >= 1
and (select count(*) from `activities` inner join `activity_user` on `activities`.`id` = `activity_user`.`activity_id` where `activity_user`.`user_id` = `users`.`id` and `id` = '1') >= 1
and (select count(*) from `activities` inner join `activity_user` on `activities`.`id` = `activity_user`.`activity_id` where `activity_user`.`user_id` = `users`.`id` and `id` = '2') >= 1
Jarek Tkaczyk 提供的解决方案:
$userByActivities = User::with('activities')
->whereHas('activities', function($query) use($selectedActivities) {
$query->selectRaw('count(distinct id)')->whereIn('id', $selectedActivities);
}, '=', count($selectedActivities))->get();
对于类似的请求,将导致以下查询:
for a similar request, results in following query:
select * from `users` where (select count(distinct id) from `activities`
inner join `activity_user` on `activities`.`id` = `activity_user`.`activity_id`
where `activity_user`.`user_id` = `users`.`id` and `id` in ('7', '3', '1', '2')) = 4
推荐答案
您必须为此添加多个whereHas
:
$query = User::with('activities');
foreach($selectedActivities as $activityId){
$query->whereHas('activities', function($q) use ($activityId){
$q->where('id', $activityId);
});
}
$userByActivities = $query->get();
这篇关于Laravel雄辩-总在哪里的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!