Laravel 'whereNotIn' 查询难度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25251879/
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 'whereNotIn' query difficulty
提问by twilco
I'm trying to run the following queries and running into this error:
我正在尝试运行以下查询并遇到此错误：
preg_replace(): Parameter mismatch, pattern is a string while replacement is an array
When I remove the 'whereNotIn' part the query works. I know for a fact that the first query works, as I tested that individually. How can I fix this error? Here's the code:
当我删除“whereNotIn”部分时，查询有效。我知道第一个查询有效，因为我单独测试了它。我该如何解决这个错误？这是代码：
$alreadyCheckedOutDevicesQuery = DB::connection('NEWSTAFFPORTAL')->table('DeviceCheckout_checkout')->select('deviceID')->where('inBy', '=', '')->get();
$alreadyCheckedOutDevices = GlobalModel::convertDBObjectsToArray($alreadyCheckedOutDevicesQuery);
$deviceTableInformation = DB::connection('NEWSTAFFPORTAL')->table('DeviceCheckout_deviceListTestingTable')->select('deviceID', 'name', 'type', 'brand', 'model')->whereNotIn('deviceID', $alreadyCheckedOutDevices)->orderBy('name', 'ASC')->get();
回答by Joseph Silber
Try doing it in a subquery:
尝试在子查询中执行此操作：
$info = DB::connection('NEWSTAFFPORTAL')
->table('DeviceCheckout_deviceListTestingTable')
->select('deviceID', 'name', 'type', 'brand', 'model')
->orderBy('name', 'asc')
->whereNotIn('deviceID', function ($query)
{
$query->from('DeviceCheckout_checkout')
->select('deviceID')
->where('inBy', '');
})
->get();
回答by Jarek Tkaczyk
This will work:
这将起作用：
$alreadyCheckedOutDevices = DB::connection('NEWSTAFFPORTAL')
->table('DeviceCheckout_checkout')
->where('inBy', '=', '')
->lists('deviceID');
$deviceTableInformation = DB::connection('NEWSTAFFPORTAL')
->table('DeviceCheckout_deviceListTestingTable')
->select('deviceID', 'name', 'type', 'brand', 'model')
->whereNotIn('deviceID', $alreadyCheckedOutDevices)
->orderBy('name', 'ASC')
->get();
Also it should be better in terms of performance, than using subquery.
此外，它在性能方面应该比使用子查询更好。
Simplified explain:
简化explain：
+----+--------------------+-----------------+---------+------+-------------+
| id | select_type | type | key | rows | Extra |
+----+--------------------+-----------------+---------+------+-------------+
| 1 | PRIMARY | ALL | NULL | 100 | Using where |
| 2 | DEPENDENT SUBQUERY | unique_subquery | PRIMARY | 1 | Using index |
+----+--------------------+-----------------+---------+------+-------------+
+----+-------------+------+------+------+-------------+
| id | select_type | type | key | rows | Extra |
+----+-------------+------+------+------+-------------+
| 1 | SIMPLE | ALL | NULL | 100 | Using where |
+----+-------------+------+------+------+-------------+
