Laravel whereIn 在每个数组项上都有一个 where 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31467285/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel whereIn with a where clause on each array item
提问by askilondz
Say I have a user object (which belongsToMany groups) and I'm doing a whereIn with an array of their respected ids like so:
假设我有一个用户对象（它属于多个组），并且我正在使用他们受尊重的 id 数组执行 whereIn，如下所示：
whereIn('user_id', $group->users->modelKeys())
I need to, however, set a condition where I only pull data from each array item based on a condition of the group_user pivot table, "created_at" (which is basically a timestamp of when that user was added to the group).
但是，我需要设置一个条件，在该条件下，我仅根据 group_user 数据透视表“created_at”（基本上是将该用户添加到该组时的时间戳）的条件从每个数组项中提取数据。
So I need something like this:
所以我需要这样的东西：
whereIn('user_id', $group->users->modelKeys())->whereRaw('visits.created_at > group_user.created_at')
That doesn't work though because it's not doing the whereRaw for each array item but it's doing it once for the query as a whole. I might need to do something like a nested whereIn but not sure if that'll solve it either. Thoughts?
但这不起作用，因为它没有为每个数组项执行 whereRaw，而是为整个查询执行一次。我可能需要做一些类似嵌套 whereIn 的事情，但不确定这是否能解决它。想法？
My full query as it is now:
我现在的完整查询：
$ids = $group->users->modelKeys();
return DB::table('visits')->whereIn('user_id', function($query) use ($ids) {
$query->select('user_id')->from('group_user')->whereIn('group_user.user_id', $ids)->whereRaw('visits.created_at > group_user.created_at');
})->sum("views");
Ok got it to work using nested loops instead:
好的，它可以使用嵌套循环来代替：
$visits = DB::table('visits')->whereIn('user_id', $group->users->modelKeys())->get();
$sum = 0;
foreach($group->users as $user) {
foreach($visits as $visit) {
if($visit->user_id == $user->id) {
if($visit->created_at >= $user->pivot->created_at) {
$sum += $visit->views;
}
}
}
}
return $sum;
Would still like to see if it's possible to do it in a single query, no array looping.
仍然想看看是否可以在单个查询中完成它，没有数组循环。
回答by Raphael Rafatpanah
Have you considered using a foreach?
您是否考虑过使用foreach?
$users = whereIn('user_id', $group->users->modelKeys());
foreach ($users as $user) {
// do your comparison here
}
回答by Dharmesh Patel
I guess you need to use JOINS for this query, following code may take you in right direction:
我猜您需要为此查询使用 JOINS，以下代码可能会带您走向正确的方向：
$ids = $group->users->modelKeys();
return DB::table('visits')->join('group_user', function ($query) use ($ids) {
$query->on('visits.user_id', '=', 'group_user.user_id')
->whereIn('group_user.user_id', $ids)
->whereRaw('visits.created_at > group_user.created_at');
})->sum("views");
EDIT
编辑
$ids = $group->users->modelKeys();
return DB::table('visits')->join('group_user', function ($query) use ($ids) {
$query->on('visits.user_id', '=', 'group_user.user_id');
})->whereIn('group_user.user_id', $ids)
->whereRaw('visits.created_at > group_user.created_at')->sum("views");
EDIT
编辑
$ids = $group->users->modelKeys();
$ids = $group->users->modelKeys();
return DB::table('visits')->join('group_user', function ($query) use ($ids) {
$query->on('visits.user_id', '=', 'group_user.id') // group_user.id from group_user.user_id as per the loop
->on('visits.created_at', '>=', 'group_user.created_at');
})->whereIn('group_user.user_id', $ids)
->sum("views");
回答by askilondz
Solved it! The foreach loop approach was making calls take waaaay too long. Some queries had over 100k records returning (that's a lot to loop through) causing the server to hang up. The answer is in part a big help from Dharmesh Patel with his 3rd edit approach. The only thing I had to do differently was add a where clause for the group_id.
解决了！foreach 循环方法使调用花费太长时间。一些查询返回了超过 10 万条记录（需要循环很多）导致服务器挂断。答案部分来自 Dharmesh Patel 的第三种编辑方法的巨大帮助。我唯一需要做的不同是为 group_id 添加一个 where 子句。
Here's the final query (returns that 100k results query in milliseconds)
这是最终查询（以毫秒为单位返回 100k 结果查询）
//Eager loading. Has overhead for large queries
//$ids = $group->users->modelKeys();
//No eager loading. More efficient
$ids = DB::table('group_user')->where('group_id', $group->id)->lists('user_id');
return DB::table('visits')->join('group_user', function ($query) use ($ids) {
$query->on('visits.user_id', '=', 'group_user.user_id')->on('visits.created_at', '>=', 'group_user.created_at');
})->whereIn('group_user.user_id', $ids)->where('group_id', $group->id)->sum('views');
