Some time we need to apply custom query to fetch the record from database in Drupal 8. below are several examples how to get data of node through select query.

Get single value:

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

Method addField() is used to add the db table field, we can add as many fields given in the db table.
Method condition() is used to apply the condition in query to fetch the selected records.
to get the limited records we can use range() Method.
Get single row:

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

Using db like:

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

Get several rows with JOIN:
We can fetch the records from more then one database table using join() Method, Please see below example:

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

Related posts:

Categories: Drupal, How to, PHP

Leave a Reply