Database APIs

by Gisle Hannemyr

The Database abstraction layer and the EntityFieldQuery class is in the core of Drupal 7 and let your application interact with the database. This chapter shows how to use these APIs.

Table of contents

Introduction

Both the EntityFieldQuery class and the database abstraction layer (the db_* functions) let a developer interact with the database. The EntityFieldQuery provides a more high level entry point and can be used without knowing about the lower-level structure. It also support a pluggable field storage. The default implementation of the EntityFieldQuery class stores field data in a separate table per field in the database. However, this can be overridden, there is for example an implementation which allows to store field data in MongoDB. To make the code to be portable and not just work on your specific site configuration, use the EntityFieldQuery class.

This chapter provides some examples on how to use both.

Database abstraction layer

Drupals database abstraction layer allow the use of different database servers (e.g. MySQL, PostgreSQL, MS SQL Server), using the same code base. 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.

The system is based upon PHP's PDO (PHP Data Objects) database API and inherits much of its syntax and semantics.

See alsoSee documentation on Drupal.org: Database abstraction layer.

List of all 40 procedural functions in database.inc:

Related functions:

Using db_query and db_query_range()

Traditionally, most Drupal SELECT queries are performed by a call to db_query() or db_query_range().

This executes an arbitrary SQL query string against the active database.

Basic SELECT statements

To fetch all node titles you would normally do this kind of SQL statement:

SELECT n.title FROM node n;
In Drupal: 
$result = db_query('SELECT n.title FROM {node} n');

This is very similar to SQL, except the curly brackets used around the table. These add table prefix to your tables so that you can share your database across multiple sites.

foreach($result as $item) {
  print $item->title;
}

Order by value of field

To order the result of an entity by some value that lives in a field (which is stored in a separate tale, use INNER JOIN like this:

$result = db_query("SELECT n.nid FROM {field_data_field_myfield} mf
  INNER JOIN {node} n ON mf.entity_id = n.nid
  WHERE type = :type
  ORDER BY mf.field_myfield_value ASC",
    array(':type' => $type));
if ($result) {
  foreach ($result as $record) {
    // Do something.
  }
}

The above example slso demonstrates how to use a a parameterized query. The parameter :type is passed as parameter to guard agains SQL injection (the value of $type may be tainted).

Limit range

The MySQL statement:

SELECT n.title FROM node n LIMIT 0,20;

is in Drupal 7 abstracted to using a different function:

$result = db_query_range('SELECT n.title FROM {node} n',0,20);

The second argument is the offset (the first record from the result set to return), while the third argument is the number of records to return from the result set.

In Drupal LIMIT is abstracted in db_query_range() because of syntax differences between different databases. For instance, in MS SQL you have to write TOP(2) to get top two rows, while in MySQL you would use LIMIT 2.

Using db_select

Returns a new SelectQuery object for the active database. This can be adjusted before executed.

// SQL statement:
SELECT n.title FROM node n;

// Drupal style:
$result = db_select('node', 'n')
  ->fields('title')
  ->execute()
  ->fetchAssoc();

To do a wildcard select:

// SQL statement:
SELECT * FROM contact c;

// Drupal style:
$result = db_select('contact', 'c')
  ->fields('c')
  ->execute()
  ->fetchAssoc();

In the above examples, the SelectQuery object is not adjusted extended, so db_query should have been used.

Module authors should also consider using the PagerDefault Extender for queries that return results that need to be presented on multiple pages (see Extenders, and the TableSort Extender for generating appropriate queries for sortable tables (see Table sorting).

Using EntityFieldQuery

The EntityFieldQuery class lets you fetch information about entities (nodes, users, taxonomy terms et cetera) from Drupal without actually building SQL queries.

EntityFieldQuery is a class that allows retrieval of a set of entities based on specified conditions. It allows the finding of entities based on entity properties, field values, and other generic entity metadata. The syntax is really compact and easy to follow, as well. And, best of all, it's core Drupal; no additional modules are necessary to use it.

See alsoSee documentation on Drupal.org: How to use EntityFieldQuery for D7.

Counting items

Assuming that field_year exists, this will count the number of published nodes with that field set to 2017.

$query =  new EntityFieldQuery();
$query->entityCondition('entity_type', 'node')
 ->entityCondition('bundle', 'article')
 ->propertyCondition('status', NODE_PUBLISHED)
 ->fieldCondition('field_year', 'value', '2017', '=');
$count = $query->count()->execute();

Final word

[TBA]

Last update: 2019-11-29 [gh].