Create database query in Drupal 8

The system was based on functions, for example, db_select, db_update, db_insert, and so on. Direct inquiry composing dependent on the db_query and db_query_range functions was likewise upheld. The primary focal points of this campaign are the absence of authoritative to a specific database and the accommodation of code support. In Drupal 8, every one of these functions are set apart as deprecated (with the arrival of Drupal 9 they will be expelled from the bit), and it is suggested that database inquiries be performed dependent on the comparing class system.

• The simplest sample with condition:

$select_query = \Drupal::database()->select('node_field_data', 'cnfd');
$select_query ->fields('cnfd', ['uid', 'title']);
$select_query ->condition ('cnfd.nid', 1);
$result = $select_query ->еxecute()->fetchAll();

• Selection of one value:

$select_query = \Drupal::database()->select('node_field_data', 'cnfd');
$select_query ->addField('cnfd', 'title');
$select_query ->condition('cnfd.nid', 1);
$result = $select_query ->еxecute()->fetchField();

• Sampling of the first record:

$select_query = \Drupal::database()->select('node_field_data', 'cnfd');
$select_query -> fields('cnfd', ['nid', 'title']);
$select_query ->condition('cnfd.type', 'article');
$result = $select_query ->еxecute()->fetchAssoc();

• Selection of the first column as a simple array:

$select_query = \Drupal::database()->select('node_field_data', 'cnfd');
$select_query -> addField('cnfd', 'title');
$select_query -> condition('cnfd.type', 'article');
$result = $select_query ->еxecute()->fetchCol();

• Combining the tables in the sample:

$select_query = \Drupal::database()->select('node_field_data', 'cnfd');
$select_query ->addField('cnfd', 'title');
$select_query ->addField('cnfd', 'name');
$select_query ->join('users_field_data', 'ufd', 'ufd.uid = cnfd.uid');
$select_query ->condition('nfd.type', 'article');
$result = $select_query ->еxecute()->fetchAll();

• Selecting a certain range of records:

$select_query = \Drupal::database()->select('node_field_data', 'cnfd');
$select_query ->fields('cnfd', ['nid', 'title']);
$select_query ->condition('cnfd.type', 'article');
$select_query ->range(0, 10);
$result = $select_query ->еxecute()->fetchAll();

• Using the OR conditions in the sample

$condition_or = new \Drupal\Core\Database\Query\Condition ('OR');
$condition_or->condition('cnfd.nid', 5);
$condition_or->condition('cnfd.nid', 7);
$select_query = \Drupal::database()->select('node_field_data', 'cnfd');
$select_query ->fields('cnfd', ['nid', 'title']);
$select_query ->condition($condition_or);   
$result = $select_query ->еxecute()->fetchAll();

• Counting the number of records in the sample

$select_query = \Drupal::database()->select('node_field_data', 'cnfd');
$select_query ->fields('cnfd', ['nid', 'title']);
$select_query ->condition('cnfd.type', 'article');
$result = $select_query ->countQuery()->еxecute()->fetchField();

• Checking values for NULL

$select_query = \Drupal::database()->select('example', 'ce');
$select_query ->fields('ce');
$select_query ->isNull('ce.field_null');
$select_query ->isNotNull('ce.field_not_null');
$result = $select_query ->еxecute()->fetchAll();

• Application of complex expressions in the sample

$select_query = \ Drupal::database()->select('node_field_data', 'cnfd');
$select_query ->fields('cnfd', ['nid', 'title']);
$select_query ->addExpression("DATE_FORMAT (FROM_UNIXTIME (cnfd.created), '% e% b% Y')", 'node_created');
$result = $select_query ->еxecute()->fetchAll();

• Grouping of sampling records

$select_query = \Drupal::database()->select('node_field_data', 'cnfd');
$select_query ->addField('cnfd', 'type');
$select_query ->addExpression('COUNT (*)', 'count');
$select_query ->groupBy('cnfd.type');
$result = $select_query ->еxecute()->fetchAll();

• Applying complex conditions in a query

$select_query = \Drupal::database()->select('node_field_data', 'cnfd');
$select_query ->fields('cnfd', ['nid', 'title', 'type']);
$select_query ->where('DAY (FROM_UNIXTIME (cnfd.created)) =: day', [': day' => 7]);
$result = $select_query ->еxecute()->fetchAll();

• Sort selected records

$select_query = \Drupal::database()->select('node_field_data', 'cnfd');

$select_query ->fields('cnfd', ['nid', 'title', 'type']);

$select_query ->orderBy('cnfd.title');

$result = $select_query ->еxecute()->fetchAll();

Insert and edit

• Update records

$select_query = \Drupal::database()->update('example');
$select_query ->fields([
  'field_1' => $value_1,
  'field_2' => $value_2,
  'field_3' => $value_3,
]);
$select_query ->condition('field_4', $value_4);
$select_query ->еxecute();

• Using complex expressions when updating

$select_query = \Drupal::database()->update('example');
$select_query ->expression('field_1', 'field_1 + :amount', [':amount' => 100]);
$select_query ->expression('field_3', 'field_2');
$select_query ->еxecute();

• Adding one record

$select_query = \Drupal::database()->insert('example');
$select_query ->fields([
  'field_1' => $value_1,
  'field_2' => $value_2,
  'field_3' => $value_3,
]);
$select_query ->еxecute();

• Adding multiple entries

$values = [
  [$value_1, $value_2, $value_3],
  [$value_4, $value_5, $value_6],
  [$value_7, $value_8, $value_9],
];
$select_query = \Drupal::database()->insert('example');
$select_query ->fields(['field_1', 'field_2', 'field_3']);
foreach ($values as $record) {
  $select_query ->values($record);
}
$select_query ->еxecute();

• Add or update depending on the availability of the record

$select_query = \Drupal::database()->upsert('example');
$select_query ->fields(['field_id', 'field_1']);
$select_query ->values([$id, $value_1]);
$select_query ->key('field_id');
$select_query ->еxecute();

• Delete: To delete data from your tables safely use the DELETE-request object, which can be obtained using the delete() method.

$select_query = \Drupal::database()->delete('example');
$select_query ->condition('field', $value);
$select_query ->еxecute();