3 namespace Drupal\Core\Database\Query;
5 use Drupal\Core\Database\Database;
6 use Drupal\Core\Database\Connection;
7 use Drupal\Core\Database\IntegrityConstraintViolationException;
10 * General class for an abstracted MERGE query operation.
12 * An ANSI SQL:2003 compatible database would run the following query:
15 * MERGE INTO table_name_1 USING table_name_2 ON (condition)
17 * UPDATE SET column1 = value1 [, column2 = value2 ...]
18 * WHEN NOT MATCHED THEN
19 * INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...
22 * Other databases (most notably MySQL, PostgreSQL and SQLite) will emulate
23 * this statement by running a SELECT and then INSERT or UPDATE.
25 * By default, the two table names are identical and they are passed into the
26 * the constructor. table_name_2 can be specified by the
27 * MergeQuery::conditionTable() method. It can be either a string or a
30 * The condition is built exactly like SelectQuery or UpdateQuery conditions,
31 * the UPDATE query part is built similarly like an UpdateQuery and finally the
32 * INSERT query part is built similarly like an InsertQuery. However, both
33 * UpdateQuery and InsertQuery has a fields method so
34 * MergeQuery::updateFields() and MergeQuery::insertFields() needs to be called
35 * instead. MergeQuery::fields() can also be called which calls both of these
36 * methods as the common case is to use the same column-value pairs for both
37 * INSERT and UPDATE. However, this is not mandatory. Another convenient
38 * wrapper is MergeQuery::key() which adds the same column-value pairs to the
39 * condition and the INSERT query part.
41 * Several methods (key(), fields(), insertFields()) can be called to set a
42 * key-value pair for the INSERT query part. Subsequent calls for the same
43 * fields override the earlier ones. The same is true for UPDATE and key(),
44 * fields() and updateFields().
46 class Merge extends Query implements ConditionInterface {
48 use QueryConditionTrait;
51 * Returned by execute() if an INSERT query has been executed.
53 const STATUS_INSERT = 1;
56 * Returned by execute() if an UPDATE query has been executed.
58 const STATUS_UPDATE = 2;
61 * The table to be used for INSERT and UPDATE.
68 * The table or subquery to be used for the condition.
70 protected $conditionTable;
73 * An array of fields on which to insert.
77 protected $insertFields = [];
80 * An array of fields which should be set to their database-defined defaults.
86 protected $defaultFields = [];
89 * An array of values to be inserted.
93 protected $insertValues = [];
96 * An array of fields that will be updated.
100 protected $updateFields = [];
103 * Array of fields to update to an expression in case of a duplicate record.
105 * This variable is a nested array in the following format:
107 * <some field> => array(
108 * 'condition' => <condition to execute, as a string>,
109 * 'arguments' => <array of arguments for condition, or NULL for none>,
115 protected $expressionFields = [];
118 * Flag indicating whether an UPDATE is necessary.
122 protected $needsUpdate = FALSE;
125 * Constructs a Merge object.
127 * @param \Drupal\Core\Database\Connection $connection
128 * A Connection object.
129 * @param string $table
130 * Name of the table to associate with this query.
131 * @param array $options
132 * Array of database options.
134 public function __construct(Connection $connection, $table, array $options = []) {
135 $options['return'] = Database::RETURN_AFFECTED;
136 parent::__construct($connection, $options);
137 $this->table = $table;
138 $this->conditionTable = $table;
139 $this->condition = new Condition('AND');
143 * Sets the table or subquery to be used for the condition.
146 * The table name or the subquery to be used. Use a Select query object to
147 * pass in a subquery.
149 * @return \Drupal\Core\Database\Query\Merge
152 protected function conditionTable($table) {
153 $this->conditionTable = $table;
158 * Adds a set of field->value pairs to be updated.
161 * An associative array of fields to write into the database. The array keys
162 * are the field names and the values are the values to which to set them.
164 * @return \Drupal\Core\Database\Query\Merge
167 public function updateFields(array $fields) {
168 $this->updateFields = $fields;
169 $this->needsUpdate = TRUE;
174 * Specifies fields to be updated as an expression.
176 * Expression fields are cases such as counter = counter + 1. This method
177 * takes precedence over MergeQuery::updateFields() and it's wrappers,
178 * MergeQuery::key() and MergeQuery::fields().
183 * The field will be set to the value of this expression. This parameter
184 * may include named placeholders.
186 * If specified, this is an array of key/value pairs for named placeholders
187 * corresponding to the expression.
189 * @return \Drupal\Core\Database\Query\Merge
192 public function expression($field, $expression, array $arguments = NULL) {
193 $this->expressionFields[$field] = [
194 'expression' => $expression,
195 'arguments' => $arguments,
197 $this->needsUpdate = TRUE;
202 * Adds a set of field->value pairs to be inserted.
205 * An array of fields on which to insert. This array may be indexed or
206 * associative. If indexed, the array is taken to be the list of fields.
207 * If associative, the keys of the array are taken to be the fields and
208 * the values are taken to be corresponding values to insert. If a
209 * $values argument is provided, $fields must be indexed.
211 * An array of fields to insert into the database. The values must be
212 * specified in the same order as the $fields array.
214 * @return \Drupal\Core\Database\Query\Merge
217 public function insertFields(array $fields, array $values = []) {
219 $fields = array_combine($fields, $values);
221 $this->insertFields = $fields;
226 * Specifies fields for which the database-defaults should be used.
228 * If you want to force a given field to use the database-defined default,
229 * not NULL or undefined, use this method to instruct the database to use
230 * default values explicitly. In most cases this will not be necessary
231 * unless you are inserting a row that is all default values, as you cannot
232 * specify no values in an INSERT query.
234 * Specifying a field both in fields() and in useDefaults() is an error
235 * and will not execute.
238 * An array of values for which to use the default values
239 * specified in the table definition.
241 * @return \Drupal\Core\Database\Query\Merge
244 public function useDefaults(array $fields) {
245 $this->defaultFields = $fields;
250 * Sets common field-value pairs in the INSERT and UPDATE query parts.
252 * This method should only be called once. It may be called either
253 * with a single associative array or two indexed arrays. If called
254 * with an associative array, the keys are taken to be the fields
255 * and the values are taken to be the corresponding values to set.
256 * If called with two arrays, the first array is taken as the fields
257 * and the second array is taken as the corresponding values.
260 * An array of fields to insert, or an associative array of fields and
261 * values. The keys of the array are taken to be the fields and the values
262 * are taken to be corresponding values to insert.
264 * An array of values to set into the database. The values must be
265 * specified in the same order as the $fields array.
267 * @return \Drupal\Core\Database\Query\Merge
270 public function fields(array $fields, array $values = []) {
272 $fields = array_combine($fields, $values);
274 foreach ($fields as $key => $value) {
275 $this->insertFields[$key] = $value;
276 $this->updateFields[$key] = $value;
278 $this->needsUpdate = TRUE;
283 * Sets the key fields to be used as conditions for this query.
285 * This method should only be called once. It may be called either
286 * with a single associative array or two indexed arrays. If called
287 * with an associative array, the keys are taken to be the fields
288 * and the values are taken to be the corresponding values to set.
289 * If called with two arrays, the first array is taken as the fields
290 * and the second array is taken as the corresponding values.
292 * The fields are copied to the condition of the query and the INSERT part.
293 * If no other method is called, the UPDATE will become a no-op.
296 * An array of fields to set, or an associative array of fields and values.
298 * An array of values to set into the database. The values must be
299 * specified in the same order as the $fields array.
303 public function keys(array $fields, array $values = []) {
305 $fields = array_combine($fields, $values);
307 foreach ($fields as $key => $value) {
308 $this->insertFields[$key] = $value;
309 $this->condition($key, $value);
315 * Sets a single key field to be used as condition for this query.
317 * Same as \Drupal\Core\Database\Query\Merge::keys() but offering a signature
318 * that is more natural for the case of a single key.
320 * @param string $field
321 * The name of the field to set.
322 * @param mixed $value
323 * The value to set into the database.
327 * @see \Drupal\Core\Database\Query\Merge::keys()
329 public function key($field, $value = NULL) {
330 // @todo D9: Remove this backwards-compatibility shim.
331 if (is_array($field)) {
332 $this->keys($field, isset($value) ? $value : []);
335 $this->keys([$field => $value]);
341 * Implements PHP magic __toString method to convert the query to a string.
343 * In the degenerate case, there is no string-able query as this operation
344 * is potentially two queries.
347 * The prepared query statement.
349 public function __toString() {
352 public function execute() {
353 // Default options for merge queries.
354 $this->queryOptions += [
355 'throw_exception' => TRUE,
359 if (!count($this->condition)) {
360 throw new InvalidMergeQueryException(t('Invalid merge query: no conditions'));
362 $select = $this->connection->select($this->conditionTable)
363 ->condition($this->condition);
364 $select->addExpression('1');
365 if (!$select->execute()->fetchField()) {
367 $insert = $this->connection->insert($this->table)->fields($this->insertFields);
368 if ($this->defaultFields) {
369 $insert->useDefaults($this->defaultFields);
372 return self::STATUS_INSERT;
374 catch (IntegrityConstraintViolationException $e) {
375 // The insert query failed, maybe it's because a racing insert query
376 // beat us in inserting the same row. Retry the select query, if it
377 // returns a row, ignore the error and continue with the update
379 if (!$select->execute()->fetchField()) {
384 if ($this->needsUpdate) {
385 $update = $this->connection->update($this->table)
386 ->fields($this->updateFields)
387 ->condition($this->condition);
388 if ($this->expressionFields) {
389 foreach ($this->expressionFields as $field => $data) {
390 $update->expression($field, $data['expression'], $data['arguments']);
394 return self::STATUS_UPDATE;
397 catch (\Exception $e) {
398 if ($this->queryOptions['throw_exception']) {