9.13. Working with the database in Drupal

03/10/2019, by Ivan

It's common case to execute in developing Drupal modules. You should use entityQuery where it's possible, but sometimes it's needed to use SQL query to get more sophisticated data.

You can add to bookmarks this page to always have a cheat sheet on hand how to execute MySQL queries select, insert, update, delete in Drupal.

Select

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');

Below are examples of other database queries insert, update, upsert, and delete. These queries can be useful for working with custom tables. But better think twice before using custom tables. It would better to use Drupal API or contrib moudules. But if nevertheless you decide to write custom queries to the database, then below you will find examples:

Insert

$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.

Update

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

Upsert

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

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.

Delete

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

That's all. If you need more examples, write in the comments. In the next tutorials, we will look at how to use custom Drupal database queries in practice.

Extra Block Types (EBT) - New Layout Builder experience❗

❗Extra Paragraph Types (EPT) - New Paragraphs experience