Database API

Editor Notes

Drupal.org gives us good HELP and documentation, but even so, I find myself searching for suitable examples, so I decided to copy the documentation I need and use and update them with more examples with time.
If you have an important example, please be a member of this site, and add your example in the comments.

This article includes the following subjects:

Database API Overview
General Concepts
Result Sets
Static Queries
Dynamic Queries
Insert Queries
Update Queries
Merge Queries
Upsert Queries
Delete Queries
Select Queries examples
References And Additional Resources

Database API Overview

The Drupal Database API provides a standard, vendor-agnostic abstraction layer for accessing database servers.
You should almost never be making database calls directly unless you are developing core APIs.

The API is designed to preserve the syntax and power of SQL as much as possible, but also:

To support multiple database servers easily;
To allow developers to leverage more complex functionality, such as transactions;
To provide a structured interface for the dynamic construction of queries;
To enforce security checks and other good practices;
To provide modules with a clean interface for intercepting and modifying a site's queries.

The main Database API documentation is derived directly from comments in the code.
This Handbook section augments those API docs by providing a tutorial for module authors who wish to interact with the database system, as well as an overview of the system from an administrator's point of view.

The Database API was built with object-oriented design concepts, and this document therefore assumes at least a partial familiarity with those concepts.
Common operations also have a procedural style available for use, but those procedural styles are deprecated.
It is recommended to use a connection object for database interaction.

Please note that the Database API may not always be the best option for interacting with data.
API use in Drupal 8 is usually situational, e.g. using the Node API for Node CRUD operations, the Entity API for Entity creation, etc.
Please view the API docs to determine which API best fits your needs.

General concepts

The Drupal database layer is built on top of the PHP's PDO library.
PDO provides a unified, object-oriented API for accessing different databases but it does not provide an abstraction for the different dialects of SQL used by different databases.

Drivers

Because different databases require different sorts of interaction, the Drupal database layer requires a driver for each database type.
A driver consists of a series of files located in includes/database/driver, where a driver is a string representing the unique key for that driver.
In most cases, the driver key is the lowercase version of the database name, such as "mysql", "pgsql", or "mycustomdriver".

Each driver consists of several classes derived from parent classes in the core database system.
These driver-specific classes may override whatever behavior is needed to properly support that database type.
Driver-specific classes are always named for their parent class followed by an underscore followed by the driver name.
For example, the MySQL specific version of InsertQuery is named InsertQuery_mysql.

Connections

A connection is an object of class DatabaseConnection, which inherits from the PDO class.
Every database to which Drupal connects has a single connection object associated with it.
That connection object must be subclassed for each individual driver.

To access (and open if necessary) a connection object, use:

$database = \Drupal::database(); // Or $database = \Drupal::service('database');

If services are not yet available, \Drupal\Core\Database\Database::getConnection() can get a database connection.

For more information on Target and Connection Key, please see the documentation page on Database configuration.

To access the currently active connection, use:

$conn = \Drupal\Core\Database\Database::getConnection();

This will get the default target of the active connection.

Note that in the vast majority of cases you will not need to request the connection object directly. Rather, the procedural wrappers will do so for you. The only reason you would ever need to access a connection object directly is if you are doing complex manipulation of more than one database and you do not want to change the active database.

To set the active connection, use:

$conn = \Drupal\Core\Database\Database::setActiveConnection('external');

See the next section, Database configuration, for details of connection keys and targets.

Queries

A query is an SQL statement that will be sent to a database connection.
There are six types of queries supported by the database system: Static, Dynamic, Insert, Update, Delete, and Merge.
Some queries are written as SQL string templates (prepared statements) while others use object-oriented query builders.
A "query object" refers to an instance of a query builder for one of the various query types.

Statements

A statement object is the result of a Select query.
It will always be of type DatabaseStatement, or possibly a subclass of DatabaseStatement.
DatabaseStatement extends the PDOStatement class.

Drupal uses prepared statements for all queries.
A prepared statement is a template for a query into which values will be inserted for execution.
Think of a prepared statement as the SQL equivalent of a function, which is then called with parameters to use.

In normal PDO, one must explicitly prepare a statement object and then execute it with certain values bound to placeholders in the query. The statement can then be iterated as a result set.
Effectively a statement and a result set are synonymous, but only after a statement has been executed.

Drupal does not expose the prepared statement directly.
Instead, a module developer will use a query object or a one-off SQL string to execute a query, and the statement object for that query is returned.
The terms "statement object" and "result set object" are therefore more or less synonymous.

Go To Editor Notes

Result Sets

A Select query will always return a result set object of zero or more records.
There are several ways to then retrieve data from that result set, depending on the use case.
The records are fetched as objects by default unless you change the fetch mode (See: setFetchMode)

The most common case is to iterate over the result set with a foreach() loop.

// Static query:
$result = $connection->query("SELECT field1, field2 FROM {mytable}");
foreach ($result as $record) {
  // Do something with each $record
}

// Dynamic query
$result = $query->execute();
foreach ($result as $record) {
  // Do something with each $record.
}

Depending on what the results are needed for, however, there are a number of other ways to retrieve the records.

To explicitly fetch the next record, use:

$record = $result->fetch();            // Use the default fetch mode.
$record = $result->fetchObject();  // Fetch as a stdClass object.
$record = $result->fetchAssoc();   // Fetch as an associative array.

If there is no next record, FALSE will be returned. fetch() should generally be avoided in favor of fetchObject() and fetchAssoc(), as the latter are more self-documenting. If you need to use some other PDO-supported fetch mode, then use fetch().

To fetch just a single field out of the result set, use:

$record = $result->fetchField($column_index);

The default value of $column_index is 0, for the first field.

To count the number of rows returned from a DELETE, INSERT or UPDATE statement use:

$number_of_rows = $result->rowCount();

To count the number of rows returned from a SELECT statement use:

$number_of_rows = $connection->select('mytable')->countQuery()->execute()->fetchField();

To fetch all records at once into a single array, use one of the following:

// Retrieve all records into an indexed array of stdClass objects.
$result->fetchAll();

// Retrieve all records into an associative array keyed by the field in the result specified.
$result->fetchAllAssoc($field);

// Retrieve a 2-column result set as an associative array of field 0 => field 1.
$result->fetchAllKeyed();
// You can also specify which two fields to use by specifying the column numbers for each field
$result->fetchAllKeyed(0,2); // would be field 0 => field 2
$result->fetchAllKeyed(1,0); // would be field 1 => field 0
// If you need an array where keys and values contain the same field (e.g. for creating a 'checkboxes' form element), the following is a perfectly valid method:
$result->fetchAllKeyed(0,0); // would be field 0 => field 0, e.g. [article] => [article]

// Retrieve a 1-column result set as one single array.
$result->fetchCol();
// Column number can be specified otherwise defaults to first column
$result->fetchCol($column_index);

Note that fetchAll() and fetchAllAssoc() will by default fetch using whatever fetch mode was set on the query (numeric array, associative array, or object). That can be modified by passing in a new fetch mode constant.

For fetchAll(), it is the first parameter. For fetchAllAssoc(), it is the second parameter.

Examples:

// Get an array of arrays keyed on the field 'id'.
$result->fetchAllAssoc('id', PDO::FETCH_ASSOC);
// Get an array of arrays with both numeric and associative keys.
$result->fetchAll(PDO::FETCH_BOTH);

Because PHP supports chaining method calls on returned objects, it is very common to skip the $result variable entirely, like so:

// Get an associative array of ids to titles.
$examples = $connection->query("SELECT id, title FROM {mytable}")->fetchAllKeyed();

// Get a single record out of the database.
$myobj = $connection->query("SELECT * FROM {mytable} WHERE example = :example", [':example' => $example])->fetchObject();

// Get a single value out of the database.
$myval = $connection->query("SELECT example FROM {mytable} WHERE id = :id", [':id' => $id])->fetchField();

If what you want is a simple array like [1, 2, 3, 4, 5] you will have to settle for something more like [1=>1, 2=>2, 3=>3, 4=>4, 5=>5]. You can get this by using

$ids = $connection->query("SELECT id FROM {example}")->fetchAllKeyed(0,0);

Go To Editor Notes

Static Queries

The most common SELECT queries in Drupal are static queries using the query() method of a database connection object.
Static queries are passed to the database nearly verbatim.

Example

$database = \Drupal::database();
$query = $database->query("SELECT id, example FROM {mytable}");
$result = $query->fetchAll();

Only very simple SELECT queries should use the static query() method. You should use a dynamic query if you need more complex queries, dynamic query generation or alterability.

Do not use this function for simple INSERT, UPDATE, or DELETE queries. Those should be handled via insert(), update() and delete() respectively. For more complex DELETE queries on multiple tables, see Complex DELETE queries.

Arguments

The query() method of a database connection object takes three arguments:

$query: the query to run. Use placeholders where appropriate and denote all table names with curly braces.
$args: an array of placeholder values to substitute into the query.
$options: an array of options to control how the query operates (optional).

Table name prefixing

In static queries, all table names must be wrapped in curly braces {...}.

Wrapping table names in curly braces flags them so that the database system can attach a prefix string to them if appropriate. Prefixing allows for running multiple sites from the same database or, in limited cases, for sharing selected tables between sites. It is also necessary to avoid data from the host site to leak into tests.

Placeholders

Placeholders mark where a literal will be inserted into a query for execution. By separating them out from the query itself, we allow the database to differentiate between SQL syntax and user-provided values, thus avoiding SQL injection attacks.

$query = $database->query("SELECT id, example FROM {mytable} WHERE created > :created", [
  ':created' => REQUEST_TIME - 3600,
]);

The above code will select all mytable ids and examples created within the past hour (3600 seconds). The placeholder :created will be dynamically replaced by whatever the value of REQUEST_TIME - 3600 is at the point the query is run.

A query may have any number of placeholders, but all must have unique names even if they have the same value. Depending on the use case, the placeholders array may be specified inline (as above) or may be built beforehand and passed in. The order of the array does not matter.

Placeholders beginning with "db_" are reserved for internal system use and should never be specified explicitly.

Note that placeholders should not be escaped or quoted regardless of their type. Because they are passed to the database server separately, the server is able to differentiate between the query string and the value on its own.

// WRONG (quotes around the :type placeholder)
$result = $database->query("SELECT example FROM {mytable} WHERE type = ':type'", [
  ':type' => 'mytype',
]);

// CORRECT (no quotes around the :type placeholder)
$result = $database->query("SELECT example FROM {mytable} WHERE type = :type", [
  ':type' => 'mytype',
]);

Placeholders cannot be used for column and table names. Instead, if these are derived from unsafe input, they should be run through $database->escapeTable().

Placeholder arrays

Drupal's database layer includes an extra feature of placeholders. If the value passed in for a placeholder is an array, it will be automatically expanded into a comma separated list as will the corresponding placeholder. That means developers do not need to worry about counting how many placeholders they will need.

An example should make this behavior more clear:

$result = $database->query("SELECT * FROM {mytable} WHERE id IN (:ids[])", [':ids[]' => [13, 42, 144]]);

The following two statements are both equivalent to the statement above:

$result = $database->query("SELECT * FROM {mytable} WHERE id IN (:ids_1, :ids_2, :ids_3)", [
  ':ids_1' => 13, 
  ':ids_2' => 42, 
  ':ids_3' => 144,
]);

$result = $database->query("SELECT * FROM {mytable} WHERE id IN (13, 42, 144)");

Query options

The third parameter to the query() method of the database connection object is an array of options that direct how the query will behave. There are typically only two directives that will be used by most queries. The other values are mostly for internal use.

The "target" key specifies the target to use. If not specified, it defaults to "default". At present, the only other valid value is "replica", to indicate that a query should run against a replica server if one exists.

The "fetch" key specifies how records returned from that query will be retrieved. Legal values include PDO::FETCH_OBJ, PDO::FETCH_ASSOC, PDO::FETCH_NUM, PDO::FETCH_BOTH, or a string representing the name of a class. If a string is specified, each record will be fetched into a new object of that class. The behavior of all other values is defined by PDO, and will retrieve records as a stdClass object, an associative array, a numerical array, or an array keyed both numerically and associatively, respectively. See http://php.net/manual/en/pdostatement.fetch.php. The default is PDO::FETCH_OBJ, which for consistency should be used unless there is a specific reason to do otherwise.

The following example will execute a query against a replica server if available and fetch records from the result set as an associative array.

$database = \Drupal::service('database.replica');
$result = $database->query("SELECT id, example FROM {mytable}", [], [
  'fetch' => PDO::FETCH_ASSOC,
]);

The result object returned by a call to the query() method can be used to get each of the rows returned and then columns. In the following example, the $result variable has all the rows of the query returned, and then the individual rows are pulled one at a time into the $row variable using fetchAssoc():

$sql = "SELECT name, quantity FROM {goods} WHERE vid = :vid";
$result = $database->query($sql, [':vid' => $vid]);
if ($result) {
  while ($row = $result->fetchAssoc()) {
    // Do something with:
    // $row['name']
    // $row['quantity']
  }
}

Complex DELETE queries

Using a static query is a simple and compact way of expressing a delete query which involves deleting from multiple tables in a single expression.

Example:

$database = \Drupal::database();
$database->query("DELETE {table1}, {table2} FROM {table1} INNER JOIN {table2} ON {table1}.id = {table2}.id WHERE {table1}.id=:recno", [":recno" => 2]);

(Deletes a row from both table1 and table2)

Tested Example

    public function watchdogInformation( $type, $visit_thrushold=5 ) {
       
        $sql = "SELECT COUNT(wid) as visits, hostname, type ";
        $sql.= "FROM {watchdog}  ";
        $sql.= "WHERE `type` = :typ  " ;
        $sql.= "GROUP By hostname  ";
        $sql.= "HAVING visits > :vis  " ;
        $sql.= "ORDER BY visits DESC ; ";        
        $results = $this->connection->query($sql, [
                                ':typ' => $type,
                                ':vis' => $visit_thrushold
                ]);
        
        return $results;
    }

    public function watchdogReports() {
        //SELECT COUNT(wid) as log FROM `watchdog`
        $sql = "SELECT COUNT(wid) as log FROM {watchdog}; " ;
        $query = $this->connection->query($sql);        
        $reports = $query->fetchField();     
        return $reports;        
    }

Go To Editor Notes

Dynamic Queries

Dynamic queries refer to queries that are built dynamically by Drupal rather than provided as an explicit query string.
All Insert, Update, Delete, and Merge queries must be dynamic.
Select queries may be either static or dynamic.
Therefore, "dynamic query" generally refers to a dynamic Select query.

In 90% of the use cases, you will have a static query.
When performance is critical, query() should be used instead of select().
Dynamic queries should be used when the query parts vary or when they should be alterable.

All dynamically built queries are constructed using a query object, requested from the appropriate database connection object.
As with static queries, in the vast majority of cases, the procedural wrapper may be used to request the object.
Subsequent directives to the query, however, take the form of methods invoked on the query object.

Dynamic select queries are started using the select() method like in the following code.

$database = \Drupal::database();
$query = $database->select('mytable', 'mt', $options);
$query->fields('mytable', ['field_1', 'field_2']);

In this case, mytable is the base table for the query, the table used from the FROM statement.
Note that it should not have brackets around it.
The query builder will handle that automatically.
The second parameter is the table alias; if not specified, the name of the table will be used as an alias.

The value returned by $database->select() is an instance of Select.

Dynamic select queries can be very simple or very complex.
The next sections will describe the individual parts that make simple queries;
The following pages will describe more advanced techniques.

Options Array

The $options parameter is optional and it's identical to the $options array for static queries.
See Static Queries Options.

The Big Picture

This is a relatively simple query for the user's table.

Let's say we want to create a dynamic query that is roughly equivalent to the following static query.

$result = $database->query("SELECT uid, name, status, created, access FROM {users_field_data} u WHERE uid <> 0 LIMIT 50 OFFSET 0");

The dynamic query would use the following code.

// Create an object of type Select and directly add extra detail
// to this query object: a condition, fields and a range.
$query = $database->select('users_field_data', 'u')
  ->condition('u.uid', 0, '<>')
  ->fields('u', ['uid', 'name', 'status', 'created', 'access'])
  ->range(0, 50);

Executing the query

Once the query is built, call execute() to compile and run the query.

$result = $query->execute();

The execute() method will return a result set/statement object that is identical to that returned by $database->query();
It may be iterated or fetched in exactly the same way.

$result = $query->execute();
foreach ($result as $record) {
  // Do something with each $record.
  // A field named `field_1` in $record is accessible via `$record->field_1`.
}

See the section on result sets for more details.

Be careful when using the following methods with a multi-column, dynamic query:

fetchField()
fetchAllKeyed()
fetchCol()

These methods currently require numeric column indices instead of table aliases.
However, the query builder does not currently guarantee any specific order for the returned fields;
The data columns may not be in the order that you expect.
In particular, expressions are always added after fields, even if you add them to your query first.
(This issue does not apply to static queries, which always return the data columns in the order you specify.)

Debugging

To examine the SQL query the query object produces, print the query object.
To examine the arguments, look at the array returned by arguments().

echo $query;
print_r((string) $query);
print_r($query->arguments());

Go To Editor Notes

Expressions

The Select query builder supports the use of expressions in the field list.
Examples of expressions include "twice the age field", "a count of all name fields", and a substring of the title field.
Be aware that many expressions may use SQL functions, and not all SQL functions are standardized across all databases.
It is up to the module developer to ensure that only cross-database compatible expressions are used.
(Refer to the list of functions and operators)

To add an expression to a query, use the addExpression() method.

$count_alias = $query->addExpression('COUNT(uid)', 'uid_count');
$count_alias = $query->addExpression('created - :offset', 'timestamp', array(':offset' => 3600));

The first line above will add "COUNT(uid) AS uid_count" to the query.
The second parameter is the alias for the field.
In the rare case, that alias is already in use, a new one will be generated and the return value of addExpression() will be the alias used.
If no alias is specified, a default of "expression" (or expression_2, expression_3, etc.) will be generated.

The optional third parameter is an associative array of placeholder values to use as part of the expression.

Note that some SQL expressions may not function unless accompanied by a GROUP BY clause added with $query->groupBy().
It is up to the developer to ensure that the query that is generated is in fact valid.

MAX, MIN, AVG, etc

Expressions are useful to do aggregate functions, like MAX, MIN, AVG

$select = $this->database->select('node__body', 't');
$select->addExpression('MAX(entity_id)');
$last = $select->execute()->fetchField();

Count Queries

Any query may have a corresponding "count query". The count query returns the number of rows in the original query. To obtain a count query from an existing query (which is a select query object implementing SelectInterface), use the countQuery() method.

$count_query = $query->countQuery();

$count_query is now a new Dynamic Select query with no ordering restrictions that when executed will return a result set with only one value, the number of records that would be matched by the original query. Because PHP supports chaining methods on returned objects, the following idiom is a common approach:

$num_rows = $query->countQuery()->execute()->fetchField();

For an entity query (implementing QueryInterface), the code is slightly different:

$num_rows = $query->count()->execute();

Verified Examples

        $groups = $this->connection->select(self::IPGROUPS, 'b')
                ->fields('b', ['id'])
                ->condition('status', $status)
                ->countQuery()
                ->execute()
                ->fetchField();
        return $groups;

$query = $this->connection->select(self::IPGROUPS, 'b') 
    ->fields('b', ['id']) 
    ->condition('status', $status); 
$query->addExpression('COUNT(id)', 'groups'); 
$groups = $query->groupBy('id') 
   ->execute() 
   ->fetchField();

Go To Editor Notes

Joins

To join against another table, use the join(), innerJoin(), leftJoin(), or addJoin() methods, like so:

// Example of a SELECT query which joins the {node} table to both the {node_field_data} and {users} tables.
$query = $connection->select('node', 'n', $options);
$query->join('node_field_data', 'nfd', 'n.nid = nfd.nid AND nfd.status = :status', array(':status' => 1));
$table_alias = $query->join('users', 'u', 'n.uid = u.uid AND u.uid = :uid', array(':uid' => 5));

The above directive will add an INNER JOIN (the default join type) against the "user" table, which will get an alias of "u".
The join will be ON the condition " n.uid = u.uid AND u.uid = :uid", where :uid has a value of 5.
Note the use of a prepared statement fragment.
That allows for the addition of variable join statements in a secure fashion.
Never put a literal value or variable directly into a query fragment, just as literals and variables should never be placed into a static query directly (they can lead to SQL injection vulnerabilities).
The innerJoin(), leftJoin(), and addJoin() methods operate identically for their respective join types.

rightJoin is removed in Drupal 9.0.0

rightJoin() as of Drupal 8.1.x, will be removed in Drupal 9.0.0.
Instead, change the query to use leftJoin() or addJoin($joinType, $table, $alias, $condition).

The return value of a join method is the alias of the table that was assigned.
If an alias is specified it will be used except in the rare case that alias is already in use by a different table.
In that case, the system will assign a different alias.

Note that in place of a literal such as 'user' for the table name, all of the join methods will accept a select query as their first argument.

Example:

$myselect = $query = $connection->select('mytable', 'mt')
  ->fields('mt', ['myfield1', 'myfield2'])
  ->condition('myfield1', 'myvalue');
$alias = $query->join($myselect, 'myalias', 'n.nid = myalias.nid');

Joins cannot be chained, so they have to be called separately (see Chaining).
If you are chaining multiple functions together do it like this:

$query = $connection->select('node', 'n', $options);
$query->join('node_field_data', 'nfd', 'n.nid = nfd.nid');
$query
  ->fields('n', array('nid'))
  ->fields('nfd', array('title'))
  ->condition('nfd.type', 'page')
  ->condition('nfd.status', '1')
  ->orderBy('nfd.created', 'DESC')
  ->addTag('node_access');

Go To Editor Notes

Conditions

To add a WHERE clause to a dynamic query, use the condition() method:

$query->condition('bundle', 'article', '=');

The above code will instruct the query to filter results to the article bundle.
Note that the field name here should be the alias created by the addField() or addExpression() methods.

Condition Parameters

The condition() method accepts three parameters:

$field - The field on which to make the comparison (required).
$value - The value to compare (optional, defaults to NULL).
$operator - The comparison operator (optional, defaults to ‘=’)

Supported Operators

Common comparison operators of '=', '<>', '<', '<=', '>', '>=' are supported in all supported database types.

Using IN, NOT IN

The operators IN and NOT IN accept an array in $value and will compare the field value with the values of the array.

$users = [2,5,17,22];
$query->condition('uid', $users, 'IN');
//
$query->condition('uid', $users, 'NOT IN');

In the first example, the query will return records where uid is one of 2, 5, 17, or 22.

In the second example, the query will return records where uid is NOT one of 2, 5, 17, or 22.

Using BETWEEN and NOT BETWEEN

The operators BETWEEN and NOT BETWEEN accept an array of two values for the comparison.

$query->condition('count', [5,10], 'BETWEEN');

In this example, the query will return records where count is between 5 and 10.

Using IS NULL, IS NOT NULL, EXISTS, and NOT EXISTS

By convention, use the following rather using the condition() method:

$query->isNull($field);
$query->isNotNull($field);
$query->exists($field);
$query->notExists($field);

While a condition such as $query->condition($field, NULL, 'IS NOT NULL');
should work, the above convention is recommended.

Other Operators

Other operators such as BINARY or others specific to the database system in use, may or may not work. Be aware of this when developing modules that may be used on different systems.

Using Multiple Conditions

Multiple condition() methods may be added to further filter the query.

$query->condition('bundle', 'article', '=');
$query->condition('status', 1, '=');

When multiple condition()s exist, as in the example above, all conditions must be met for a record to appear in the query result (i.e. The conditions are ANDed together).

Tip: To achieve a query with only OR conditions, use one orConditionGroup.

Condition Groups

Condition groups can be used to create more complex where clauses (including ORs) in the condition of the query.
There are two condition group types:

orConditionGroup - returns an object of conditions joined with ORs
andConditionGroup - returns an object of conditions joined with ANDs

Conditions groups return an object, which can then be added as a condition to the query.

// Create the orConditionGroup
$orGroup = $query->orConditionGroup()
  ->condition('promoted', 1)
  ->condition('uid', [2,4,7,22], 'IN');
  
// Add the group to the query.
$query->condition($orGroup);

In this example, the conditions for promoted and uid are added to the query’s WHERE, grouped together with OR. This would result in a WHERE which looks like:

WHERE ( promoted = 1 OR uid IN (2,4,7,22) )

Multiple orConditionGroup and andConditionGroups can be added as needed.
Condition groups can be nested for more complex WHERE clause needs.

Given the following example WHERE clause:

WHERE   ( a = 1 OR b = 1) AND ( (c = 1 AND d = 1) OR (e = 1) )

The query conditions could be written with condition groups as:

$orGroup1 = $query->orConditionGroup()
  ->condition('a', 1)
  ->condition('b', 1);
  
$andGroup1 = $query->andConditionGroup()
  ->condition('c', 1)
  ->condition('d', 1);
  
$orGroup2 = $query->orConditionGroup()
  ->condition($andGroup1)
  ->condition('e', 1);
  
$query->condition($orGroup1);
$query->condition($orGroup2);

Go To Editor Notes

Insert Queries

Insert queries must always use a query builder object.
Certain databases require special handling for LOB (Large OBject, such as TEXT in MySQL) and BLOB (Binary Large OBject) fields, so a layer of abstraction is required to allow individual database drivers to implement whatever special handling they require.

Insert queries are started using the insert() method as follows:

/** @var \Drupal\Core\Database\Connection $connection */
$connection = \Drupal::service('database');
$query = $connection->insert('mytable', $options);

That creates an insert query object that will insert one or more records to the mytable table.
Note that braces are not required around the table name as the query builder will handle that automatically.

The insert query object uses a fluent API.
That is, all methods (except execute()) return the query object itself allowing method calls to be chained.
In many cases, that means the query object will not need to be saved to a variable at all.

The insert query object supports a number of different usage patterns to support different needs.
In general, the workflow consists of specifying the fields that the query will insert into, specifying the values the query will insert for those fields, and executing the query.
The most common recommended usage patterns are listed below.

Compact form

The preferred form for most Insert queries is the compact form:

$result = $connection->insert('mytable')
  ->fields([
    'title' => 'Example',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ])
  ->execute();

This will result in the equivalent of the following query:

INSERT INTO {mytable} (title, uid, created) VALUES ('Example', 1, 1221717405);

The above snippet chains together the key parts of the insert process.

$connection->insert('mytable')

This line creates a new insert query object for the mytable table.

  ->fields([
    'title' => 'Example',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ])

The fields() method takes several forms of parameters, but a single associative array is the most common.
The keys of the array are the table columns into which to insert and the values are the corresponding values to insert.
That will result in a single insert query against the specified table.

  ->execute();

The execute() method tells the query to run.
Unless this method is called, the query does not execute.

Unlike other methods on the Insert query object, which return the query object itself, execute() returns the value of an auto-increment (serial type in hook_schema()) field that was populated by the Insert query, if any.
That's why the return value from it is assigned to $result in the example above.
If there is no auto-increment field, the return value from execute() is undefined and should not be trusted.

In the typical case, this is the preferred format for Insert queries.

Degenerate form

$result = $connection->insert('mytable')
  ->fields(['title', 'uid', 'created'])
  ->values([
    'title' => 'Example',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ])
  ->execute();

This is the somewhat more verbose equivalent of the previous query, and will have the exact same result.

  ->fields(['title', 'uid', 'created'])

When fields() is called with an indexed array instead of an associative array, it sets only the fields (database columns) that will be used in the query without setting any values for them.
That is useful for running a multi-insert query later.

  ->values([
    'title' => 'Example',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ])

This method call specifies an associative array of field names to values to insert into those fields.
The values() method may also take an indexed array instead.
If an indexed array is used, the order of values must match the order of fields in the fields() method.
If an associative array is used, it may be in any order.
Generally, the associative array is preferred for readability.

This query form is rarely used, as the compact form is preferred.
In most cases, the only reason to separate fields() and values() is when running a multi-insert query.

Multi-insert form

The Insert query object may also take multiple value sets.
That is, values() may be called multiple times to enqueue several insert statements together.
Exactly how that happens will depend on the capabilities of the database in question.
On most databases, multiple insert statements will be executed together inside a transaction for greater data integrity and speed.
In MySQL, it will use MySQL's multi-value insert syntax.

$values = [
  [
    'title' => 'Example',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ],
  [
    'title' => 'Example 2',
    'uid' => 1,
    'created' => \Drupal::time()->getRequestTime(),
  ],
  [
    'title' => 'Example 3',
    'uid' => 2,
    'created' => \Drupal::time()->getRequestTime(),
  ],
];
$query = $connection->insert('mytable')->fields(['title', 'uid', 'created']);
foreach ($values as $record) {
  $query->values($record);
}
$query->execute();

The above example will execute three insert statements together as a single unit, using the most efficient method for the particular database driver in use.
Note that here we have saved the query object to a variable so that we can loop on $values and call the values() method repeatedly.

In the degenerate case, the above example is equivalent to the following three queries:

INSERT INTO {mytable} (title, uid, created) VALUES ('Example', 1, 1221717405);
INSERT INTO {mytable} (title, uid, created) VALUES ('Example2', 1, 1221717405);
INSERT INTO {mytable} (title, uid, created) VALUES ('Example3', 2, 1221717405);

Note that on a multi-insert query the return value from execute() is undefined and should not be trusted, as it may vary depending on the database driver.

Inserting based on the results of a select query

If you want to populate a table with results from other tables, you either need to SELECT from the source tables, iterate over the data in PHP and insert it into the new table, or you can do an INSERT INTO...SELECT FROM query in which every record which is returned from the SELECT query gets fed into the INSERT query.

In this example, we want to build a table mytable which has a nid and a user name for all nodes in the system which are of the page type.

<?php
// Build the SELECT query.
$query = $connection->select('node', 'n');
// Join to the users table.
$query->join('users', 'u', 'n.uid = u.uid');
// Add the fields we want.
$query->addField('n','nid');
$query->addField('u','name');
// Add a condition to only get page nodes.
$query->condition('type', 'page');

// Perform the insert.
$connection->insert('mytable')
  ->from($query)
  ->execute();
?>

Default values

In normal circumstances, if you do not specify a value for a given field and a default value is defined by the table's schema then the database will silently insert that default value for you.
In some cases, however, you need to explicitly instruct the database to use a default value.
That includes if you want to use all default values for the entire record.
To explicitly tell the database to use the default value for a given field, there is a useDefaults() method.

$query->useDefaults(['field1', 'field2']);

This line instructs the query to use the database-defined defaults for fields field1 and field2.
Note that it is an error to specify the same field in both useDefaults() and fields() or values(), and an exception will be thrown.

$connection->insert() or $connection->query()

This is a commonly asked question. (See the comments on this page.) What are the difference between insert() and query()?

insert() has each column specified as a separate entry in the fields array and the code can clean each column value.
query() has an SQL string with no way of checking individual columns.
If you use query() with placeholders, the code can check the column values but placeholders are just an option, there is no way to ensure your SQL does not contain values not passed through placeholders.

insert() passes the request through a set of hooks to let other modules check and modify your requests.
This is the right way to work with other modules.
query() is slightly faster because query() does not pass the request through the hooks.
You might save processing time but your code will not let other modules help your code.

insert() is more likely to work with other databases and future versions of Drupal.

more examples

$query = \Drupal::database()->insert('flood');
$query->fields([
  'event',
  'identifier'
]);
$query->values([
  'My event',
  'My indentifier'
]);
$query->execute();

You can call values() several times to insert multiple records at a time.

Go To Editor Notes

Update Queries

Update queries must always use a query builder object.
Certain databases require special handling for LOB (Large OBject, such as TEXT on MySQL) and BLOB (Binary Large OBject) fields, so a layer of abstraction is required to allow individual database drivers to implement whatever special handling they require.

Update queries are started using the update() method as follows:

$query = $connection->update('mytable', $options);

This creates an update query object that will modify one or more records to the 'mytable' table.
Note that braces are not required around the table name as the query builder will handle that automatically.

The update query object uses a fluent API. That is, all methods (except execute()) return the query object itself allowing method calls to be chained.
In many cases, that means the query object will not need to be saved to a variable at all.

Update queries are conceptually simple, consisting of a set of key/value pairs to set and a WHERE clause.
The full structure of the WHERE clause is detailed in the section on Conditional clauses, and will only be touched on here.

The typical Update query is as follows.

$num_updated = $connection->update('mytable')
  ->fields([
    'field1' => 5,
    'field2' => 1,
  ])
  ->condition('created', REQUEST_TIME - 3600, '>=')
  ->execute();

The above query will update all records in the mytable table created within the last hour and set their field1 field to 5 and field2 field to 1.
The fields() method takes a single associative array that specifies what fields to set to what values when the specified conditions are met.
Note that unlike Insert queries, UpdateQuery::fields() only accepts an associative array.
Also, the order of fields in the array and the order in which methods are called are irrelevant.

The above example is equivalent to the following query:

UPDATE {mytable} SET field1=5, field2=1 WHERE created >= 1221717405;

The execute() method will return the number of rows affected by the query.
Note that affected is not the same as matched.
In the above query, an existing record that already has a field1 of 5 and field2 of 1 will be matched, but since the data in it does not change it will not be affected by the query and therefore not be counted in the return value.
As a side effect, that makes Update queries ineffective for determining if a given record already exists.

<?php
$query = $connection->update('mytable');
// Conditions etc.
$affected_rows = $query->execute();
?>

To apply Where conditions:

$query = $connection->update('mytable')
  ->condition('module', 'my_module')
  ->where(
    'SUBSTR(delta, 1, 14) <> :module_key',
    ['module_key' => 'my_module-key_']
  )
  ->expression('delta', "REPLACE(delta, :old_value, :new_value)", [
    ':old_value' => 'my_module-other_',
    ':new_value' => 'my_module-thing_',
  ])
  ->execute();

To use string functions in conditions:

$query = $connection->update('mytable')
  ->condition('module', 'my_module')
  ->condition('SUBSTR(delta, 1, 14)', 'my_module-key_', '<>') // causes error.
  ->expression('delta', "REPLACE(delta, :old_value, :new_value)", [
    ':old_value' => 'my_module-other_',
    ':new_value' => 'my_module-thing_',
  ])
  ->execute();

More examples:

$query = \Drupal::database()->update('flood');
$query->fields([
  'identifier' => 'My new identifier'
]);
$query->condition('event', 'My event');
$query->execute();

Go To Editor Notes

Merge Queries

Merge queries are a special type of hybrid query.
Although a syntax is defined for them in the SQL 2003 specification, virtually no database supports the standard syntax.
Most, however, provide some alternate implementation using a database-specific syntax.
The Merge query builder in Drupal abstracts the concept of a Merge query out into a structured object that can be compiled down to the appropriate syntax for each database.
These are sometimes called "UPSERT" queries, a combination of UPDATE and INSERT.

In the general sense, a Merge query is a combination of an Insert query and an Update query.
If a given condition is met, such as a row with a given primary key already existing, then an Update query is run.
If not, an Insert query is run.
In the most common case, it is equivalent to:

if ($connection->query("SELECT COUNT(*) FROM {example} WHERE id = :id", [':id' => $id])->fetchField()) {
  // Run an update using WHERE id = $id
}
else {
  // Run an insert, inserting $id for id 
}

The actual implementation varies widely from database to database.
Note that while Merge queries are conceptually an atomic operation, they may or may not be truly atomic depending on the implementation for a specific database.
The MySQL implementation is a single atomic query, for example, but the degenerate case (above) is not.

The most common idioms for Merge queries are listed below.

Just Set It

$connection->merge('example')
  ->key('name', $name)
  ->fields([
      'field1' => $value1,
      'field2' => $value2,
  ])
  ->execute();

In the above example, we instruct the query to operate on the "example" table.
We then specify one key field, 'name', with a value of $name.
We then specify an array of values to set.

If a row already exists in which the field "name" has the value $name, then fields field1 and field2 will be set to the corresponding values in that existing row.
If such a row does not exist, one will be created in which name has the value $name, field1 has the value $value1, and field2 has the value $value2.
Thus at the end of the query, the end result is the same regardless of whether or not the row already existed.

Conditional Set

In some cases, you may want to set values differently depending on whether or not the record, as identified by the key() fields, already existed.
There are two ways to do that.

$connection->merge('example')
  ->insertFields([
      'field1' => $value1,
      'field2' => $value2,
  ])
  ->updateFields([
    'field1' => $alternate1,
  ])
  ->key('name', $name)
  ->execute();

The above example will behave the same as the first, except that if the record already exists and we are updating it, field1 will be set to $alternate1 instead of $value1 and field2 will not be affected.
The updateFields() method accepts either a single associative array of values or two parallel numeric arrays, one of fields, one of values, that must be in the same order.

$connection->merge('example')
  ->key('name', $name)
  ->fields([
      'field1' => $value1,
      'field2' => $value2,
  ])
  ->expression('field1', 'field1 + :inc', [':inc' => 1])
  ->execute();

In this example, if the record already exists then field1 will be set to its current value plus 1.
That makes it very useful for "counter queries", where you want to increment some counter in the database every time a certain event happens.
field2 will still be set to the same value regardless of whether the record exists or not.

Note that expression() may be called multiple times, once for each field that should be set to an expression if the record already exists.
The first parameter is the field, the second is an SQL fragment indicating the expression the field should be set to, and the optional third parameter is an array of placeholder values to insert into the expression.

There is also no requirement that a field used in expression() be already present in fields().

Precedence

Given the above API it is quite possible to define queries that do not logically make sense, say if a field is set to both be ignored and to be set to an expression if the record already exists.
To minimize potential errors, the following rules apply:

If a field is set to an expression(), that takes priority over updateFields().
If values are specified in updateFields(), only those fields will be altered if the record already exists.
Fields not specified in updateFields() will not be affected.

Note that it may still be possible to define queries that do not make sense.
It is up to the developer to ensure that a nonsensical query is not specified as the behavior in that case is undefined.

Go To Editor Notes

Upsert Queries

Upsert queries must always use a query builder object.
Certain databases require special handling for LOB (Large OBject, such as TEXT on MySQL) and BLOB (Binary Large OBject) fields, so a layer of abstraction is required to allow individual database drivers to implement whatever special handling they require.

Upsert queries are started using the upsert() method as follows:

/** @var \Drupal\Core\Database\Connection $connection */
$connection = \Drupal::service('database');
$query = $connection->upsert('mytable', $options);

This creates an upsert query object that will insert or modify one or more records to the 'mytable' table.
Note that braces are not required around the table name as the query builder will handle that automatically.

The upsert query object uses a fluent API.
That is, all methods (except execute()) return the query object itself allowing method calls to be chained.
In many cases, that means the query object will not need to be saved to a variable at all.

Upsert queries are conceptually simple, consisting of a set of keys and then one or more key/value pairs for each key.
Each set of key/value pairs is for a single row in the table.

The typical Upsert query is as follows.

$upsert = $connection->upsert('mytable')
  ->fields(['field1', 'field2'])
  ->key('id');

  $upsert->values([
    'field1' => 3,
    'field2' => 5,
    'id' => 2,
  ]);

  $upsert->values([
    'field1' => 4,
    'field2' => 5,
    'id' => 3,
  ]);

  $result = $upsert->execute();

The above query will insert or update 2 records in the mytable table.
The fields() method takes a single associative array that specifies which fields to set.
The key() method takes a single associative array that specifies what fields are the key fields of the query.

The key fields are used in the query to determine whether to INSERT or UPDATE a row in the table.

This is done for each set of values.

Editor notes:
Key, or one of the keys, should be set as the primary key in the table (or unique, for me it didn't work correctly without it and instead of updating the record it always created new

The values() method takes a single associative array that specifies what field values to set.
Each field for the key() method and the fields() method must be set.

The above example is equivalent to the following query for MySQL:

INSERT INTO {mytable} SET (field1, field2, id) VALUES (3,5,2), (4,5,3) ON DUPLICATE KEY UPDATE field1 = VALUES(field1), field2 = VALUES(field2)

The above example is equivalent to the following query for PostgreSQL:

INSERT INTO {mytable} SET (field1, field2, id) VALUES (3,5,2), (4,5,3) ON CONFLICT (id) DO UPDATE SET field1 = EXCLUDED.field1, field2 = EXCLUDED.field2

The execute() method will return the number of rows affected by the query.
Note that for MySQL the calculated number of rows is a bit different.
Every inserted row counts as 1 and every updated row counts as 2 (every updated row has had an insert that failed).
For more info: https://dev.mysql.com/doc/c-api/8.0/en/mysql-affected-rows.html

More Examples

The key() method should be used to describe the name of the field that will be used to determine the existing record. You do not need to write condition(), you can use key() method. The field used in the key() method must be unique, for example, as nid, uid, tid, fid or others IDs of entities. If the key does not exist, then instead of updating the record, a new record will be inserted with the specified key.

$query = \Drupal::database()->upsert('flood');
$query->fields([
  'fid',
  'identifier',
]);
$query->values([
  1,
  'My indentifier for upsert'
]);
$query->key('fid');
$query->execute();

How to use with a single key

// Use an upsert query which is atomic and optimized for multiple-row
// merges.
$query = $this->connection
             ->upsert($this->bin)
             ->key('cid')
             ->fields(array('cid', 'expire', 'created', 'tags', 'checksum', 'data', 'serialized'));
foreach ($values as $fields) {
    // Only pass the values since the order of $fields matches the order of
    // the insert fields. This is a performance optimization to avoid
    // unnecessary loops within the method.
    $query->values(array_values($fields));
}
$query->execute();

How to use with multiple keys

 private function save_sitemap()  {
     $this->db
         ->upsert('custom_sitemap')
         ->key(array('language_code', $this->language->getId()))
         ->fields( array(
             'language_code' => $this->language->getId(), 
             'sitemap_string' => $this->sitemap
         ))
         ->execute();
 }

Go To Editor Notes

Delete Queries

Delete queries must always use a query builder object.
They are started using the delete() method as follows:

$query = $connection->delete('mytable', $options);

That creates a delete query object that will delete records from the mytable table.
Note that braces are not required around the table name as the query builder will handle that automatically.

The delete query object uses a fluent API.
That is, all methods (except execute()) return the query object itself allowing method calls to be chained.
In many cases, this means the query object will not need to be saved to a variable at all.

Delete queries are conceptually very simple, consisting of only a WHERE clause.
The full structure of the WHERE clause is detailed in the section on Conditional clauses, and will only be touched on here.

A full Delete query will take the following form:

$num_deleted = $connection->delete('mytable')
  ->condition('myfield', 5)
  ->execute();

The above query will delete all rows from the {mytable} table where the myfield column is 5.
It is equivalent to the following query:

DELETE FROM {mytable} WHERE myfield=5;

The execute() method will return the number of records that were deleted as a result of the query.

More Examples

$query = \Drupal::database()->delete('flood');
$query->condition('event', 'My event');
$query->execute();

Go To Editor Notes

Select Queries

Get single value:

$query = \Drupal::database()->select('node_field_data', 'n');
$query->addField('n', 'nid');
$query->condition('n.title', 'About Us');
$query->range(0, 1);
$nid = $query->execute()->fetchField();

Get entry in array:

$query = \Drupal::database()->select('node_field_data', 'n');
$query->fields('n', ['nid', 'title']);
$query->condition('n.type', 'page');
$query->range(0, 1);
$vegetable = $query->execute()->fetchAssoc();

You also can use ->fetchObject(), ->fetchAll() to get entry in object.

Using LIKE in query:

$query = \Drupal::database()->select('node_field_data', 'n');
$query->fields('n', ['nid', 'title']);
$query->condition('n.type', 'page');
$query->condition('n.title', $query->escapeLike('About') . '%', 'LIKE');
$vegetable = $query->execute()->fetchAllKeyed();

Select query with JOIN:

$query = \Drupal::database()->select('node_field_data', 'n');
$query->fields('n', ['nid', 'title']);
$query->addField('u', 'name');
$query->join('users_field_data', 'u', 'u.uid = n.uid');
$query->condition('n.type', 'page');
$vegetable = $query->execute()->fetchAllAssoc('nid');

Select all fields

$query = \Drupal::database()->select('node_field_data', 'n');
$query->fields('n');
$records = $query->execute()->fetchAll();

Result Sets

// Retrieve all records into an indexed array of stdClass objects.
$result->fetchAll();

// Retrieve all records into an associative array keyed by the field in the result specified.
$result->fetchAllAssoc($field);

// Retrieve a 2-column result set as an associative array of field 0 => field 1.
$result->fetchAllKeyed();

// You can also specify which two fields to use by specifying the column numbers for each field
$result->fetchAllKeyed(0,2); // would be field 0 => field 2
$result->fetchAllKeyed(1,0); // would be field 1 => field 0

// If you need an array where keys and values contain the same field (e.g. for creating a 'checkboxes' form element), //the following is a perfectly valid method:
$result->fetchAllKeyed(0,0); // would be field 0 => field 0, e.g. [article] => [article]

// Retrieve a 1-column result set as one single array.
$result->fetchCol();

// Column number can be specified otherwise defaults to first column
$result->fetchCol($column_index);

Code Snippet

Database API overview

Result sets

Luvcite > Web Development Company > Drupal

PHP Drupal\Core\Database Connection::upsert Examples

Work with the database in Drupal 8

Working with the database in Drupal

public function Select::fields

Database API

Editor Notes

Database API Overview

General concepts

Drivers

Connections

Queries

Statements

Result Sets

Static Queries

Arguments

Table name prefixing

Placeholders

Placeholder arrays

Query options

Complex DELETE queries

Tested Example

Dynamic Queries

Options Array

The Big Picture

Executing the query

Debugging

Expressions

MAX, MIN, AVG, etc

Count Queries

Verified Examples

Joins

rightJoin is removed in Drupal 9.0.0

Conditions

Condition Parameters

Supported Operators

Using IN, NOT IN

Using BETWEEN and NOT BETWEEN

Using IS NULL, IS NOT NULL, EXISTS, and NOT EXISTS

Other Operators

Using Multiple Conditions

Condition Groups

Insert Queries

Compact form

Degenerate form

Multi-insert form

Inserting based on the results of a select query

Default values

$connection->insert() or $connection->query()

more examples

Update Queries

More examples:

Merge Queries

Just Set It

Conditional Set

Precedence

Upsert Queries

More Examples

Delete Queries

More Examples

Select Queries

Result Sets

We Can Assist You

Drupal Shared Space

Site Management