SQL error in Module

Question

I'm trying to query the database inside my module, but I get this error.

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM "commerce_product_variation" "cpv" WHERE ("variation_id" NOT IN (NULL)) AND' at line 1: SELECT FROM "commerce_product_variation" "cpv" WHERE ("variation_id" NOT IN (:db_condition_placeholder_0)) AND ("status" = :db_condition_placeholder_1); Array ( [:db_condition_placeholder_0] => [:db_condition_placeholder_1] => 1 ) in deactivateProductVariantsNotInList() (regel 816 van /data/sites/web/pcreathingbe/subsites/staging.protectasolutions.be/web/modules/custom/importproducts/importproducts.module).

The code I am using is the following.

function deactivateProductVariantsNotInList($variant_type) {
  global $milieu_data;

  if ($variant_type == 'milieutechniek') {
    $database = \Drupal::service('database');
    $query = $database->select('commerce_product_variation', 'cpv');
    $query->condition('variation_id', $milieu_data, 'NOT IN')
      ->condition('status', 1)
      ->execute();
  }
}

Answer 1

If you take a closer look at your error message, your query probably fails because your $milieu_data variable is null.

FROM "commerce_product_variation" "cpv" WHERE ("variation_id" NOT IN (NULL)) AND where (NULL) is your $milieu_data variable value.

In a NOT IN condition, it should be formatted like NOT IN (value1, value2, ...). If $milieu_data is allowed to be null, then you should add a condition to have your query condition modified to $query->isNotNull("variation_id");

Answer 2

I cannot speak to misterdidi's answer it may apply also . . . but

Your error message points specifically to a syntax error. As I said in the comments it appears to be a " ; " - the semicolon at

$query = $database->select('commerce_product_variation', 'cpv');

which should be

$query = $database->select('commerce_product_variation', 'cpv')

A PHP code checker will report no issue with either but Drupal is sensitive to it

For instance in Drupal 7 this code below

   $weight = db_select('system', 's')
     ->fields('s', array('weight'))
     ->condition('name', 'uc_quote', '=')
     ->execute()
     ->fetchField();
   db_update('system')
     ->fields(array('weight' => $weight + 1))
     ->condition('name', 'uc_addresses', '=')
     ->execute();

ONLY has the semicolon at the end or execute and in Drupal 8/9 Drupalmoduleupgrader changed it to

   $weight = \Drupal::database()->select('system', 's')
     ->fields('s', array('weight'))
     ->condition('name', 'uc_quote', '=')
     ->execute()
     ->fetchField();
   // TODO: Drupal Rector Notice: Please delete the following comment after you've made any necessary changes.
   // You will need to use 
  \Drupal\core\Database\Database::getConnection()` if you 
 do not yet have access to the container here.
   \Drupal::database()->update('system')
     ->fields(array('weight' => $weight + 1))
     ->condition('name', 'uc_addresses', '=')
     ->execute();

While there is no "system" table any longer in Drupal 8 / 9 the purpose of DrupalModuleUpgader and Drupal Rector is to correct syntax and apply coding standards

You will notice whether it is $weight or your $query variable there is no semicolon behind the statement.

The semicolon needs to be removed