Working with Database in Drupal 7 - Lesson 11 - MERGE Queries

Merge queries are a special hybrid type of database query. While the syntax for these queries was defined in SQL 2003, few databases actually support it. However, most databases offer an alternative implementation using specific syntax. The merge query builder in Drupal abstracts the concept of a merge query into an object structure, so it can be compiled appropriately for each database based on its own syntax.

In general, a merge query is a combination of an insert and an update query. If the condition is met—i.e., if a row with a specific key already exists—then one query (usually UPDATE) is executed. If not, then another (usually INSERT) is run. This is similar to:

 $id)))->fetchField()) {
  // Run an update using WHERE id = $id
}
else {
  // Run an insert, inserting $id for id
}
?>

In practice, the implementation varies from database to database. Note that while merge queries are conceptually atomic, they may or may not actually be atomic depending on the specific database implementation. MySQL’s implementation is atomic, for example, but the above PHP code snippet is not.

Basic Example

key(array('name' => $name))
  ->fields(array(
      'field1' => $value1,
      'field2' => $value2,
  ))
  ->execute();
?>

In this example, we operate on the "example" table. We define a unique key field name with the value $name, and provide an array of field values to be set.

If a row with name = $name already exists, field1 and field2 will be updated with $value1 and $value2. If such a row does not exist, a new row will be inserted with the same values.

Setting Conditions

In some cases, you may want to set different values depending on whether a record already exists or not. There are two ways to do this.

key(array('name' => $name))
  ->fields(array(
      'field1' => $value1,
      'field2' => $value2,
  ))
  ->updateFields(array(
    'field1' => $alternate1,
  ))
  ->execute();
?>

This example behaves like the previous one, except that if the record already exists, only field1 is updated with $alternate1. If the record does not exist, it is created with field1 = $value1 and field2 = $value2. The updateFields() method accepts an associative array (or two ordered numeric arrays for keys and values).

key(array('name' => $name))
  ->fields(array(
      'field1' => $value1,
      'field2' => $value2,
  ))
  ->expression('field1', 'field1 + :inc', array(':inc' => 1))
  ->execute();
?>

In this case, if a row already exists, field1 will be incremented by 1. This is useful for counter-like behaviors where you want to increment a field each time an event occurs. If the row does not exist, field1 and field2 are set to the specified values. The expression() method can be used multiple times—once per field—to define SQL expressions for existing rows. It takes three parameters: the field name, the SQL fragment to use, and an optional array of placeholder values.

Note: A field used in expression() does not need to be present in fields().

Selective Field Updates

key(array('name' => $name))
  ->fields(array(
      'field1' => $value1,
      'field2' => $value2,
  ))
  ->updateExcept('field1')
  ->execute();
?>

The updateExcept() method accepts either an array of field names or a comma-separated list of field names. The fields specified will not be updated if the row already exists. For instance, if a row with name = $name exists, only field2 will be updated and field1 will be ignored. If the row does not exist, both field1 and field2 will be inserted.

Priority Rules

The API allows for combinations that may not make logical sense. For example, using expression() and updateExcept() on the same field. To avoid ambiguity, Drupal applies the following rules:

• If a field is set using expression(), it takes precedence over update() and updateExcept().
• If a value is specified in both update() and updateExcept(), the field is ignored.
• If update() is used, only the fields listed in update() will be updated in existing rows. All other fields will be ignored.

Note that it is possible to define nonsensical queries, so use this API carefully.