3

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.

5

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).

  • Just to reinforce for others, the ->merge() method in this answer (particularly #3) replaces all of the code in my example above. There is no need for ->select(). Though I am using key() instead of keys(). – Prestosaurus Mar 8 at 18:00
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.

  • I see what you're saying about the redundancy. I had started just using $queryDB[0]->value1 != $value1 style PHP checks, everything worked, however, I did get a notice of undefined index from $queryDB[0], is that something I can ignore? – Prestosaurus Mar 7 at 19:00
  • 1
    Yeah, it's a warning you can igore. ->fetchAll() can return an empty array so $queryDB[0] can be undefined. If you want to hide it, do something like check !empty($queryDB). – Shawn Conn Mar 7 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.