Skip to main content
Pownraj G.

Simplifying Database Queries: Drupal 7 vs Drupal 8


Database Queries in Drupal 8

With Drupal 8, significant changes has been brought for developers: new features and backwards-compatible changes, new APIs, deprecated modules, and more. One of the biggest changes is Drupal 8's unified database query API that fundamentally alters the way most Drupal developers will build database queries in Drupal.

The following database types are supported in Drupal 7 & Drupal 8:

Drupal 7

  • MySQL, MariaDB, or equivalent
  • PostgreSQL
  • SQLite

Drupal 8

  • MySQL, MariaDB, Percona Server, or equivalent
  • PostgreSQL
  • SQLite

Database abstraction layer - One query for all type of database

Allow the use of different database servers using the same code base.
Drupal provides a database abstraction layer to provide developers with the ability to support multiple database servers easily. 

The intent of this layer is to preserve the syntax and power of SQL as much as possible, but also allow developers a way to leverage more complex functionality in a unified way. 

It also provides a structured interface for dynamically constructing queries when appropriate, and enforcing security checks and similar good practices.

It’s PHP’s PDO (recursive acronym of PHP Data Object. Definition: The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP).

Drupal query

Query Types:

  • Static Query
  • Dynamic Query

Static and Dynamic queries

Static queries 

The most common form of query in Drupal is a static query. Only very simple queries should use the static query mechanism.
db_query

Dynamic queries

Dynamic queries refer to queries that are built dynamically by Drupal rather than provided as an explicit query string. All Insert, Update, Delete, and Merge queries must be dynamic.
db_select, db_insert, db_delete, db_update, db_merge

D7 db_query 

Executes an arbitrary query string against the active database.

db_query($query, array $args = array(), array $options = array())

// Fetch the results from node table where the node belongs to specified ids

Example

db_query("SELECT * FROM {node} WHERE nid IN (13, 42, 144)");

Using placeholders  (:placeholder_name)

db_query("SELECT * FROM {node} WHERE nid IN (:nids)", array(':nids' => array(13, 42, 144));
or
$nids = array(13, 42, 144);
db_query("SELECT * FROM {node} WHERE nid IN (:nids)", array(':nids' => $nids);

Note: Curly braces ( { } ) are not mandatory in either case

D8 db_query

Executes an arbitrary query string against the active database.

db_query($query, array $args = array(), array $options = array())

Note: Drupal8 discourages us to use db_query as it is marked as deprecated in Drupal8 and will be definitely removed from Drupal 9.

D7 db_select
Returns a new SelectQuery object for the active database.

db_select($table, $alias = NULL, array $options = array())  

// Fetch the results from node table where the node belongs to specified ids

Example

$result = db_select(node, 'n')
    ->fields('n')
    ->execute()
    ->fetchAssoc();

Select particular column 

$result = db_select(node, 'n')
    ->fields('n', array('nid', 'title'))
    ->execute()
    ->fetchAssoc();

Note: Curly braces ( { } ) are not mandatory in either case

D8 db_select
Returns a new SelectQuery object for the active database.

db_select($table, $alias = NULL, array $options = []) 

Tips: [] is supported since PHP version 5.4, it can be used instead of specifying array().

Example

$db = \Drupal::database();
$result = $db->select('node', 'n');
    $query->fields('n');
    $query->condition('type', "page", "=");
    $result = $query->execute()->fetchAll();
     print_r($result);

D7 db_insert
Returns a new InsertQuery object for the active database.

db_insert($table, array $options = array()) 

Example

$nid = db_insert('node')
->fields(array(
  'title' => 'Example',
  'uid' => 1,
  'created' => REQUEST_TIME,
))
->execute();

/** similar to **/

$result = db_query("INSERT INTO {node} (title, uid, created) VALUES (%s, %d, %d)", 'Example', 1, time());

D8 db_insert
Returns a new InsertQuery object for the active database.

db_insert($table, array $options = array()) 

Example

$conn = Database::getConnection();
$conn->insert('contact_form')->fields(
  array(
    'sender_name' => $name,
    'sender_email' => $email,
    'sender_subject' => $recipient,
    'sender_message' => $message,
  )
)->execute();

D7 db_update
Returns a new UpdateQuery object for the active database.

db_update($table, array $options = array()) 

Example

$num_updated = db_update('node')
  ->fields(array(
    'status' => 1,
  ))
  ->condition('uid', 5, '=')
  ->execute(); 

// Above Example is Equivalent to the following:

$result = db_query("UPDATE {node} SET status = %d WHERE uid = %d", 1, 5);

D8 db_update
Returns a new UpdateQuery object for the active database.

db_update($table, array $options = array())

 Example

$query = \Drupal::database()->update('file_managed');
$query->fields([
'uri' =>$uri,
]);
$query->condition('fid', $fid, ‘=’);
$query->execute();

D7 db_delete

Returns a new DeleteQuery object for the active database.

db_delete($table, array $options = array())

Example

$num_deleted = db_delete('node')
  ->condition('uid', 5)
  ->execute();

equivalent to →DELETE FROM {node} WHERE uid=5;

D8 db_delete
Returns a new DeleteQuery object for the active database.

db_delete($table, array $options = []) 

Example

db_delete('block')
    ->condition('module', 'menu')
    ->condition('delta', $delta, ‘=’)
    ->execute();

D7 and D8 db_merge
Returns a new MergeQuery object for the active database.

db_merge($table, array $options = array()) 

Example

db_merge('example')
  ->key(array('name' => $name))
  ->fields(array(
      'field1' => $value1,
      'field2' => $value2,
  ))
  ->execute();

db_truncate
Returns a new TruncateQuery object for the active database.

db_truncate($table, array $options = []) 

Example

db_truncate('mytable')
->execute();

Both D7 and D8 shares the same syntax, but in D8 its mentioned as deprecated and advised to use dependency injection for such operations.

db_set_active
Sets a new active database.

db_set_active($key = ‘default’) 

Example

db_set_active('yourdatabasekey');

Note: Deprecated as of Drupal 8.0.x, will be removed in Drupal 9.0.0. Use \Drupal\Core\Database\Database::setActiveConnection() instead.

EntityFieldQuery
Retrieves entities matching a given set of conditions.

  • When building complex web applications, you’ll eventually have to interact with a database. 
  • To retrieve data in Drupal one can use the database abstraction layer provided, which requires some SQL knowledge to be used properly. 
  • When Drupal 7 EntityFieldQuery API is provided, it lets you fetch information about entities from Drupal without actually building SQL queries. 


D7 example

$taxonomnyQuery = new EntityFieldQuery();
  $terms = $taxonomnyQuery->entityCondition('entity_type', 'taxonomy_term')
    ->propertyCondition('vid', 2)
    ->fieldCondition('field_status', 'value', 1)
    ->propertyOrderBy('tid', 'DESC')
    ->range(0, 5)
    ->execute();

Entity API

Describes how to define and manipulate content and configuration entities.

D8

$cids = \Drupal::entityQuery('comment')

    ->condition('entity_id', $nodeID)
    ->condition('entity_type', 'node')
    ->sort('cid', 'DESC')
    ->execute();

Another example, using the core File entity:

 

$fids = Drupal::entityQuery('file')
  ->condition('status', FILE_STATUS_PERMANENT, '<>')
  ->condition('changed', REQUEST_TIME - $age, '<')
  ->range(0, 100)
  ->execute();

 

Comments

Leave Your Comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
 

start with anubavam today

You have an idea we have engineers to convert your ideas into reality

Request Quote