D8: database queries

README.md

Work with PDO

@see http://www.eilyin.name/note/database-queries-drupal-8-7

• Get single value:

  $query = \Drupal::database()->select('node_field_data', 'nfd');
  $query->addField('nfd', 'nid');
  $query->condition('nfd.title', 'Potato');
  $query->range(0, 1);
  
  // With pager
  $query = $query->extend('Drupal\Core\Database\Query\PagerSelectExtender')
    	->limit(10);
    
  $nid = $query->execute()->fetchField();
  

• Get single row:

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

• Get several rows with JOIN:

  $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', 'vegetable');
  $vegetable = $query->execute()->fetchAllAssoc('nid');
  

• Count

  $query = \Drupal::database()->select('test', 't');
  $count = $query->countQuery()->execute()->fetchField();
  

• Insert

  $query = \Drupal::database()->insert('flood');
  $query->fields([
    'event',
    'identifier'
  ]);
  $query->values([
    'My event',
    'My indentifier'
  ]);
  $query->execute();
  

• 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();
  

• Delete

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