3

I recently tried to use an Upsert query in a table with a composite primary key, but when I go to see the key() method it only allows one parameter and not an array, so we only can only have a singular key:

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

  return $this;
}

I just tried to use one key and the query works fine! How is posible this? Let see in the core files.

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;
  }

}

Ok, If the key property is never used I will try to made my upsert without it.

$upsert = $this->database->upsert('forcontu_node_votes')
        ->fields(['nid', 'uid', 'vote'])
        ->values([
          'nid' => $nid,
          'uid' => $uid,
          'vote' => $node_vote,
        ]);
dpm($upsert . '');
// Using dpm() because dpq() doesn't works with upsert https://www.drupal.org/node/2847155
$upsert->execute();

The code will throw an exception because 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.');
  }
...

If you put comments in the lines

  // if (!$this->key) {
  //  throw new NoUniqueFieldException('There is no unique field specified.');
  //}

the code will works fine, and you will see that if you set or not the key property always the query is the same, in my case:

INSERT INTO {forcontu_node_votes} (nid, uid, vote) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2) ON DUPLICATE KEY UPDATE nid = VALUES(nid), uid = VALUES(uid), vote = VALUES(vote)

So, Why we need the key property in the Upsert class if it is never used?

Note: This is just in the mysql and sqlite clases that the key is not used, but in the postgre clases is used (at this moment I don't have postgre to made some test).

Database Schema:

CREATE TABLE `forcontu_node_votes` (
  `nid` int(11) NOT NULL COMMENT 'Node ID',
  `uid` int(11) NOT NULL COMMENT 'User ID',
  `vote` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1 to 5',
  PRIMARY KEY (`nid`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Stores Node votes';

forcontu_blocks.install:

/**
 * Implements hook_schema().
 */
function forcontu_blocks_schema() {
  $schema['forcontu_node_votes'] = [
    'description' => 'Stores Node votes',
    'fields' => [
      'nid' => [
        'type' => 'int',
        'not null' => TRUE,
        'description' => 'Node ID',
      ],
      'uid' => [
        'type' => 'int',
        'not null' => TRUE,
        'description' => 'User ID',
      ],
      'vote' => [
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
        'size' => 'tiny',
        'description' => '1 to 5',
      ],
    ],
    'primary key' => ['nid', 'uid'],
  ];
  return $schema;
}
  • This is an interesting question, and made a few minor edits to reflect some better terminology. I think I know the answer, but am going to wait to see if someone else answers first. – mpdonadio Feb 20 '17 at 18:28
  • @mpdonadio thanks for the edit, I wait for the answer ;-) – Adrian Cid Almaguer Feb 20 '17 at 18:29
3

You basically said it yourself. The check is there to enforce that Upsert is used in ways that is compatible with all databases, including PostgreSQL, which needs it, as it does not actually support an UPSERT-like operation.

On MySQL, the key doesn't need to be specified as it is only supported on a table with a single unique key anyway:

https://api.drupal.org/api/drupal/core!lib!Drupal!Core!Database!Query!Upsert.php/class/Upsert/8.2.x

This class can only be used with a table with a single unique index. Often, this will be the primary key. On such a table this class works like Insert except the rows will be set to the desired values even if the key existed before.

  • I can't get the point here "On MySQL, the key doesn't need to be specified..." if you don't specify the key you will get an exception. – Adrian Cid Almaguer Feb 20 '17 at 19:31
  • Yes, because drupal forces you to write code that is compatible with postgresql, as you said yourself, you can comment that out and it still works. But only on MySQL/SQLite – Berdir Feb 20 '17 at 19:33
  • Yes, I get the point now, we need this to be able to change our database without problems. I will wait to accept to see if @mpdonadio comes with another answer. – Adrian Cid Almaguer Feb 20 '17 at 19:37
  • Yeah. You changing the database for a specific project is obviously a very unlikely thing to happen. This is more targeted towards core, contrib and other re-usable code that you might never use on PostgreSQL yourself but other people possibly will. Drupal enforces for example MySQL strict mode for the same reason, so developers write correct group by queries that work everywhere. – Berdir Feb 20 '17 at 19:40
  • @AdrianCidAlmaguer If there is the option of an answer from Berdir or me, chose him :) That pretty much sums it up; UPSERTs in PGSQL are currently simulated with transactions. PG9.5+ has UPSERT capabilities, but the min for Drupal 8 is PG9.1.2. Read the PG version of Upsert to see what goes on. – mpdonadio Feb 20 '17 at 22:02

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.