Work with Database

In the Database API of Drupal we have next variants of action:

<?php

$database = \Drupal::database();
// Select.
$database->select('table', 't');
// Update.
$database->update('table', 't');
// Delete.
$database->delete('table', 't');
// Insert.
$database->insert('table', 't');
// Merge it is combination of Insert action and Update action.
// You may use it if you need Insert or Update some value.
$database->merge('table');
// Static query.
$database->query('SOME SQL WHERE value = :arg', [':arg' => 'value']);
// Static query with range.
$database->queryRange('SOME SQL WHERE value = :arg', [':arg' => 'value'], 0, 10);

Get node title by node ID

<?php

$title = \Drupal::database()
  ->select('node', 'n')
  ->fields('n', ['title'])
  ->condition('n.nid', 123)
  ->execute()
  ->fetchField();

Get all fields of a node by node ID

<?php

$fields = \Drupal::database()
  ->select('node', 'n')
  ->fields('n')
  ->condition('n.nid', 123)
  ->execute()
  ->fetchObject();

Get "title" and "type" of all published nodes

<?php

use Drupal\node\NodeInterface;

$nodes = \Drupal::database()
  ->select('node', 'n')
  ->fields('n', ['title', 'type'])
  ->condition('n.status', NodeInterface::PUBLISHED)
  ->execute()
  ->fetchAll();

Get "title" of all published nodes and sort by date

<?php

use Drupal\node\NodeInterface;

$nodes = \Drupal::database()
  ->select('node', 'n')
  ->fields('n', ['title'])
  ->condition('n.status', NodeInterface::PUBLISHED)
  ->orderBy('n.created', 'DESC')
  ->execute()
  ->fetchAll();

Get a couple fields of all published nodes and sort by date plus group by "uid"

<?php

use Drupal\node\NodeInterface;

$nodes = \Drupal::database()
  ->select('node', 'n')
  ->fields('n', ['nid', 'title', 'status', 'created'])
  ->condition('n.status', NodeInterface::PUBLISHED)
  ->orderBy('n.created', 'DESC')
  ->groupBy('n.uid');
  ->execute()
  ->fetchAll();

Get only 10 node titles

<?php

$nodes = \Drupal::database()
  ->select('node', 'n')
  ->fields('n', ['title'])
  ->range(0, 10)
  ->execute()
  ->fetchAll();

Use "JOIN"

<?php

$query = \Drupal::database()->select('node', 'n');
$query->join('users', 'u', 'n.uid = u.uid');
// Also, you can use "innerJoin", "leftJoin" or "rightJoin".
// $query->innerJoin('users', 'u', 'n.uid = u.uid');
// $query->leftJoin('users', 'u', 'n.uid = u.uid');
// $query->rightJoin('users', 'u', 'n.uid = u.uid');
$query->fields('u', ['name']);
$query->fields('n', ['title']);
$query->condition('u.mail', '[email protected]');
$result = $query->execute()->fetchAll();

Use "OR"

<?php

$nodes = \Drupal::database()
  ->select('node', 'n')
  ->fields('n')
  ->condition(
    db_or()
      ->condition('n.title', 'Some node title')
      ->condition('n.nid', 123)
  )
  ->execute()
  ->fetchAll();

Use "IN"

<?php

$nodes = \Drupal::database()
  ->select('node', 'n')
  ->fields('n')
  ->condition('n.nid',[123, 456, 789], 'IN')
  ->execute()
  ->fetchAll();

Use "LIKE"

<?php

$database = \Drupal::database();

$nodes = $database->select('node', 'n')
  ->fields('n')
  ->condition('n.title', '%' . $database->escapeLike('Some node title') . '%', 'LIKE')
  ->execute()
  ->fetchAll();

Use "BETWEEN"

<?php

$nodes = \Drupal::database()
  ->select('node', 'n')
  ->fields('n')
  ->condition('n.nid', [123, 789], 'BETWEEN')
  ->execute()
  ->fetchAll();

Use "NULL"

<?php

$data = \Drupal::database()
  ->select('table', 't')
  ->fields('t')
  ->isNull('t.field')
  // ->isNotNull('t.field')
  ->execute()
  ->fetchAll();

Use "DISTINCT"

<?php

$data = \Drupal::database()
  ->select('table', 't')
  ->distinct()
  ->fields('t', ['field'])
  ->execute()
  ->fetchAll();

Use "RANDOM"

<?php

$nodes = \Drupal::database()
  ->select('node', 'n')
  ->fields('n', ['nid', 'title'])
  ->orderRandom();
  ->execute()
  ->fetchAll();

Use "WHERE"

<?php

use Drupal\node\NodeInterface;

$nodes = \Drupal::database()->select('node', 'n');
$nodes->fields('n', ['nid', 'title', 'status']);
$nodes->condition('n.status', NodeInterface::PUBLISHED);
$nodes->where('YEAR(FROM_UNIXTIME(n.created)) = :created', [':created' => 2012]);
$result = $year->execute()->fetchAll();

Use "TRUNCATE" (cleaning table)

<?php

\Drupal::database()->truncate('table')->execute();

Use "TRANSACTION"

<?php

$transaction = \Drupal::database()->startTransaction();

try {
  // Some important SQL query or actions.
}
catch (Exception $e) {
  $transaction->rollback();
  throw $e;
}

unset($transaction);

Use "NOT EXISTS"

<?php

$nodes = \Drupal::database()
  ->select('node', 'n')
  ->fields('n', ['nid'])
  ->notExists(
    db_select('field_data_body', 'b')
      ->fields('b', ['entity_id'])
      ->where('b.entity_id = n.nid')
   )
   ->execute()
   ->fetchAll();

Use the same field name on couple tables.

Sometime you have to select field like "title" in both tables.

Well, Drupal will set separate name for these double fields like "title" and "title_1". But you can set any name for these fields.

<?php

$query = \Drupal::database()->select('node', 'n');
$query->join('table', 't', 'n.uid = t.uid');
$query->fields('n', ['title']);
$query->fields('t', ['title']);
$query->addField('n', 'title', 'node_title');
$query->addField('t', 'title', 'table_title');
$result = $query->execute()->fetchAll();

Use date format in the Query

<?php

$query = \Drupal::database()->select('node', 'n');
$query->fields('n', ['created']);
$query->addExpression("FROM_UNIXTIME(n.created, '%Y/%m/%d')", 'new_date_format');
$date = $date->execute()->fetchAll();

Count the number of records in the table

<?php

$count = \Drupal::database()
  ->select('node')
  ->countQuery()
  ->execute()
  ->fetchField();

Get max value of the field

<?php

$query = \Drupal::database()->select('node');
$query->addExpression('MAX(nid)');
$max = $query->execute()->fetchField();

Get associative array where key "nid" and value "title"

<?php

$nodes = \Drupal::database()
  ->select('node', 'n')
  ->fields('n', ['nid', 'title'])
  ->execute()
  ->fetchAllKeyed();

Get two-dimensional associative array

<?php

$nodes = \Drupal::database()
  ->select('node', 'n')
  ->fields('n', ['nid', 'title'])
  ->execute()
  ->fetchAllAssoc('nid');

Update record

<?php

use Drupal\node\NodeInterface;

\Drupal::database()
  ->update('node')
  ->fields(['status' => NodeInterface::PUBLISHED])
  ->condition('nid', 123)
  ->execute();

Delete record

<?php

\Drupal::database()
  ->delete('node')
  ->condition('nid', 123)
  ->execute();

Insert record

<?php

\Drupal::database()
  ->insert('table')
  ->fields(['value' => 123])
  ->execute();

Merge record

<?php

\Drupal::database()
  ->merge('table')
  ->key(['id' => 123])
  ->insertFields(['field' => 'some_value'])
  ->updateFields(['field' => 'some_value'])
  ->execute();

Check table

<?php

if (\Drupal::database()->schema()->tableExists('table')) {
  // Actions ...
}

Static query

<?php

$title = db_query('SELECT title FROM {node} WHERE nid = :nid', [':nid' => 123])->fetchField();