\Drupal::database() ->insert - Error: Call to undefined method Using ->condition()?

Question

I am trying to insert a new row based on conditions. The ->condition() in $database->update() works fine, in $database->insert() they do not.

$database = \Drupal::database();

// Query our DB.
$queryDB = $database
  ->select('table_name', 'value3')
  ->fields('value3')
  ->condition('value1', $value1, "=")
  ->condition('value2', $value2, "=")
  ->execute()->fetchAll();

if (isset($queryDB[0])) {
  // Check if table row exists and overwrite.
  if ($queryDB[0]->value1 == $value1 && $queryDB[0]->value2 == $value2) {
    $database
      ->update('table_name')
      ->fields([
        'value3' => $value3,
      ])
      ->condition('value1', $value1, '=')
      ->condition('value2', $value2, '=')
      ->execute();
  }
}
// Create table row.
else {
  $database
    ->insert('table_name')
    ->fields([
      'value1' => $value1,
      'value2' => $value2,
      'value3' => $value3,
    ])
    // Without these conditions, I get a duplicate entry integrity restraint as value1 and/or value2 may already exist.
    // With them I get Error: Call to undefined method Drupal\Core\Database\Driver\mysql\Insert::condition().
    ->condition('value1', $value1, '!=')
    ->condition('value2', $value2, '!=')
    ->execute();
}

Very similar questions: 1. EntityQuery condition "is not equal to..." 2. How do I add a condition to a query?

But I am having no luck. Is it different with an insert?

What I want is that if value1 AND value2 don't both match, create an entirely new table row with all three values.

Answer 1

As described in Insert Queries, the object returned from \Drupal::database()->insert() doesn't have an condition() method. If the purpose of the code is creating a new database row if it doesn't exist, or update it if it already exists, the code should use the object returned from \Drupal::database()->merge(). See Merge Queries.

For example, the following code will create a new row if none of the existing rows contains the value $name for the name database column; otherwise, it will update the existing matching row. ($connection is the object returned from \Drupal::database() or a Connection object obtained via dependency injection.)

$connection->merge('example')
  ->key(['name' => $name])
  ->fields([
      'field1' => $value1,
      'field2' => $value2,
  ])
  ->execute();

It is also possible to use a set of values when the row is created, and another set when the row is updated.

$connection->merge('example')
  ->insertFields([
      'field1' => $value1,
      'field2' => $value2,
  ])
  ->updateFields([
    'field1' => $alternate1,
  ])
  ->key(['name' => $name])
  ->execute();

What that documentation page doesn't show is how to use multiple keys, as the following code does, using Merge::keys() instead of Merge::key().

$connection->merge('example')
  ->insertFields([
      'field1' => $value1,
      'field2' => $value2,
  ])
  ->updateFields([
    'field1' => $alternate1,
  ])
  ->keys(['name' => $name, 'nickname' => $nickname])
  ->execute();

The only limitation is that the values passed to keys() are all used to match a single database row. In other words, the last snippet I shown doesn't look for a row containing $name in the name database column or a row containing $nickname in the nickname column.

In cases more complex, where the Merge class doesn't help, you need to first check what the database table contains and take the necessary action basing on the table content (which involves writing more PHP code to check the database content).

Answer 2

Yes, it is different. A SQL INSERT statement doesn't have WHERE clause, thus the condition() mentioned l method doesn't exist for the insert object.

You can add your conditional check in PHP code (e.g. $queryDB[0]->value1 != $value1) just as you've redundantly done for your UPDATE query.