CODEWITHSUNDEEP
phplaravellaravel-4
kibowki
kibowki

Reputation: 4376

Laravel 'whereNotIn' query difficulty

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:

$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();

Upvotes: 2

Views: 9033

Answers (2)

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81167

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:

+----+--------------------+-----------------+---------+------+-------------+
| 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 |
+----+-------------+------+------+------+-------------+

Upvotes: 4

Joseph Silber
Joseph Silber

Reputation: 219938

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();

Upvotes: 5

Related Questions