To group by a given field, use the groupBy() method.

$query->groupBy('uid');

The code above instructs the query to group by the uid field. Note that the field name here must be the alias created by the addField() or addExpression() methods, so in most cases you will want to use the return value from those methods to ensure the correct alias is used.

To get the count of rows grouped by a field, such as uid, you can do the following:

Merge queries are a special type of hybrid query. Although the syntax for them is defined in the SQL 2003 specification, virtually no database supports the standard syntax. However, most provide an alternative implementation using database-specific syntax. Drupal’s merge query builder abstracts the concept of a merge query into a structured object that can be compiled into the appropriate syntax for each database. They are sometimes referred to as "UPSERT" queries, a combination of UPDATE and INSERT.

Select queries always return a result set object containing zero or more records. There are several ways to retrieve data from this result set depending on the use case. Records are fetched as objects by default unless you change the fetch mode (see: setFetchMode).

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

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

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

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

Condition Parameters

The condition() method takes three parameters:

Queries can be fetched into objects based on custom classes. For example, if we have a class named ExampleClass, the following query will return objects of type ExampleClass.

$result = $connection->query("SELECT id, title FROM {example_table}", [], [
  'fetch' => 'ExampleClass',
]);

If the class has a __construct() method, objects will be created, properties will be added to the object, and then the __construct() method will be called. For example, if you have the following class and query:

Insert queries should always use the query builder object. Some databases require special handling for LOB (Large OBject, e.g., TEXT in MySQL) and BLOB (Binary Large OBject) fields, so an abstraction layer is necessary for individual database drivers to implement any required special handling.

Insert queries are initiated using the insert() method like this:

Delete queries should always use the query builder object. They are initiated using the delete() method as follows:

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

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

Drupal also supports transactions, including a transparent fallback for databases that do not support transactions. However, transactions can be quite tricky if you try to run two transactions simultaneously. Behavior in such cases also depends on the database.

A similar issue exists with nested locks in C / C++. If code has already acquired lock A and tries to acquire lock A again, the code will block. If you write code that checks whether it already holds the lock and avoids acquiring it again, you can prevent deadlocks, but you might release the lock prematurely.

The Drupal database layer does not provide cross-database abstraction for SQL functions. To ensure portability across supported database engines, your code should use only those functions that are known to be part of the ANSI standard and supported in all databases supported by Drupal. The following is still an incomplete list. The form used here is recommended, as other syntax variants may not work across all databases.

The Drupal 8 Entity System

Entities are typed classes with methods.