WHERE NOT IN using an Array

Question

My issue here is that I would like to execute Query 1, return an array of values from Query 1, and then exclude those values from Query 2. The reason for this is I will be performing an insert function in between (and want to leave my insert function untouched).

Query 1:

$notquery = db_select('uc_packages', 'p')
           ->fields('p', array('package_id'))
           ->condition('p.order_id', $order_id)
           ->condition('p.sid', NULL)
           ->execute()
           ->fetchAll();

foreach($notquery as $foo) {
  $notarr->pid = $foo;
}

Insert function:

uc_shipping_package_save($package);
...

Query 2:

$query = db_select('uc_packages', 'p')
             ->fields('p', array('package_id'))
             ->condition('p.order_id', $order_id)
             ->condition('p.sid', NULL)
             ->condition('p.order_id', $notarr, 'NOT IN')
             ->execute();

This returns an error:

Recoverable fatal error: Object of class stdClass could not be converted to string in DatabaseStatementBase->execute() (line 2139 of /var/www/seven/includes/database/database.inc).

It is understandable. My question is how do I pass my array through that query.

Answer 1

Try for the first query:

$notarr = db_select('uc_packages', 'p')
           ->fields('p', array('package_id'))
           ->condition('p.order_id', $order_id)
           ->condition('p.sid', NULL)
           ->execute()
           ->fetchCol();

That way, you'll get the array of package_id, without needing the additional foreach, and avoid the stdClass string conversion error

Presumably also query 2 should be checking package_id instead

Answer 2

The problem is with query 2:

$query = db_select('uc_packages', 'p')
         ->fields('p', array('package_id'))
         ->condition('p.order_id', $order_id)
         ->condition('p.sid', NULL)
         ->condition('p.order_id', $notarr, 'NOT IN')
         ->execute();

The variable $notarr is an object, however you need order_id over here instead. May be you should try with

$notarr->order_id or something..