How to make an upsert query with two keys?

Question

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.

Answer 1

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->keys([
  'quizid' => $this->quizid,
  'question' => $number,
])
->fields([
  'quizid' => $this->quizid,
  'question' => $number,
  'nid' => $nid,
  'time_asked' => $log_time,
  'correct' => $correct,
])
->execute();

Answer 2

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?