3

I have a custom table with a primary key made from two fields (route, uid) and I want to use the upsert() method to insert or edit a record. The problem is that the key() method only accept one key which will be used to identify the existing row. But I have two (route and uid)

Sets the unique / primary key field to be used as condition for this query.

public function key($field) {
  $this->key = $field;

  return $this;
}

Maybe I'm missing something or I misunderstand the upsert() method. So, Can I make an upsert query with two keys?

Edit: I'm using mysql.

4

At this moment I don't know why but if I use only one key the query works as expected (using the two database keys as a key in the upsert, when I only says $query->key('route');):

$route = \Drupal::routeMatch()->getRouteName();
$uid = $this->currentUser->id();

$query = $this->connection->upsert('forcontu_database_counter')
          ->fields([
    'route' => $route,
    'uid' => $uid,
    'user_count' => 1,
    'lastcount' => time(),
  ]);
$query->key('route');
$query->execute();

If I change the uid (using a diferent user in the site) in the same route (page) Drupal insert a new record (don't make an update in the existing route) and I only use one key, in this case route.

EDIT:

This always will work because if you see the code inside the __toString() function the key property is never used:

public function __toString() {
    // Create a sanitized comment string to prepend to the query.
    $comments = $this->connection->makeComment($this->comments);

    // Default fields are always placed first for consistency.
    $insert_fields = array_merge($this->defaultFields, $this->insertFields);

    $query = $comments . 'INSERT INTO {' . $this->table . '} (' . implode(', ', $insert_fields) . ') VALUES ';

    $values = $this->getInsertPlaceholderFragment($this->insertValues, $this->defaultFields);
    $query .= implode(', ', $values);

    // Updating the unique / primary key is not necessary.
    unset($insert_fields[$this->key]);

    $update = [];
    foreach ($insert_fields as $field) {
      $update[] = "$field = VALUES($field)";
    }

    $query .= ' ON DUPLICATE KEY UPDATE ' . implode(', ', $update);

    return $query;
  }

}

But always you need to set the key, if you don't set the key property you will get an exception:

protected function preExecute() {
  // Confirm that the user set the unique/primary key of the table.
  if (!$this->key) {
    throw new NoUniqueFieldException('There is no unique field specified.');
  }
...

Read more about this at: Why we need the key property in the Upsert class if it is never used?

2

Upsert - This class can only be used with a table with a single unique index. To be able to use several keys, you need to use Merge. Like this :

$query = \Drupal::database()->merge('question');
$query->key([
  'quizid' => $this->quizid,
  'question' => $number,
])
->fields([
  'quizid' => $this->quizid,
  'question' => $number,
  'nid' => $nid,
  'time_asked' => $log_time,
  'correct' => $correct,
])
->execute();

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.