Dynamic Queries & Joins in Drupal: Advanced DBTNG Techniquesfor Drupal 11 , and 10

Home » Drupal Module Development (Examples-Based) » Dynamic Queries & Joins in Drupal: Advanced DBTNG Techniques
Last updated :  
11.x , 10.x

You've now read and written data through DbtngExampleRepository. In this final database lesson, we'll slow down and focus specifically on the query-building techniques that make DBTNG genuinely powerful — building conditions at runtime, joining across tables, and controlling exactly how much data comes back. These are the tools you'll reach for once your module's data needs get more interesting than a single flat table.

What you'll learn in this lesson

  • How a single method can answer completely different questions depending on what's passed into it, without writing a new query for each case
  • How to JOIN two tables together and alias columns to avoid naming collisions
  • Why range() is a scalability safeguard, not just a pagination convenience
  • The three different ways Drupal lets you express the same SELECT query, and when to reach for each one

The source file

Path: modules/dbtng_example/src/DbtngExampleRepository.php — the same repository service from the last two lessons. This time we're focusing on load() and advancedLoad() specifically.

/**
 * Read from the database using a filter array.
 *
 * @param array $entry
 *   An array containing all the fields used to search the entries in the
 *   table.
 *
 * @return object
 *   An object containing the loaded entries if found.
 *
 * @see Drupal\Core\Database\Connection::select()
 */
public function load(array $entry = []) {
  // Read all the fields from the dbtng_example table.
  $select = $this->connection
    ->select('dbtng_example')
    // Add all the fields into our select query.
    ->fields('dbtng_example');

  // Add each field and value as a condition to this query.
  foreach ($entry as $field => $value) {
    $select->condition($field, $value);
  }
  // Return the result in object format.
  return $select->execute()->fetchAll();
}

/**
 * Load dbtng_example records joined with user records.
 *
 * DBTNG also helps processing queries that return several rows, providing the
 * found objects in the same query execution call.
 *
 * This function queries the database using a JOIN between users table and the
 * example entries, to provide the username that created the entry, and
 * creates a table with the results, processing each row.
 *
 * SELECT
 *  e.pid as pid, e.name as name, e.surname as surname, e.age as age
 *  u.name as username
 * FROM
 *  {dbtng_example} e
 * JOIN
 *  users u ON e.uid = u.uid
 * WHERE
 *  e.name = 'John' AND e.age > 18
 *
 * @see Drupal\Core\Database\Connection::select()
 * @see http://drupal.org/node/310075
 */
public function advancedLoad() {
  // Get a select query for our dbtng_example table. We supply an alias of e
  // (for 'example').
  $select = $this->connection->select('dbtng_example', 'e');
  // Join the users table, so we can get the entry creator's username.
  $select->join('users_field_data', 'u', 'e.uid = u.uid');
  // Select these specific fields for the output.
  $select->addField('e', 'pid');
  $select->addField('u', 'name', 'username');
  $select->addField('e', 'name');
  $select->addField('e', 'surname');
  $select->addField('e', 'age');
  // Filter only persons named "John".
  $select->condition('e.name', 'John');
  // Filter only persons older than 18 years.
  $select->condition('e.age', 18, '>');
  // Make sure we only get items 0-49, for scalability reasons.
  $select->range(0, 50);

  $entries = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);

  return $entries;
}

How it works

One method, any number of questions

load() is the clearest example of a genuinely dynamic query in this repository:

foreach ($entry as $field => $value) {
  $select->condition($field, $value);
}

Because every WHERE condition comes from looping over whatever array the caller passes in, this one method can serve an unlimited number of different queries: load([]) returns every row in the table; load(['name' => 'Ada']) returns only rows named Ada; load(['name' => 'Ada', 'age' => 36]) returns rows matching both, since repeated condition() calls combine with AND. You never had to write a second method, or a second SQL string — the shape of the query is entirely decided at runtime by whoever calls it.

Three equivalent ways to write the same SELECT

The original docblock for load() in the Examples module (worth reading in the full source file) lays out three approaches side by side, all producing the same result — SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John':

// 1. A static query with named placeholders — fixed structure, known in advance.
\Drupal::database()->query(
  "SELECT * FROM {dbtng_example} WHERE uid = :uid and name = :name",
  [':uid' => 0, ':name' => 'John']
)->execute();

// 2. A dynamic query built with condition() — structure can vary at runtime.
\Drupal::database()->select('dbtng_example')
  ->fields('dbtng_example')
  ->condition('uid', 0)
  ->condition('name', 'John')
  ->execute();

// 3. A dynamic query using where() for a raw fragment with named placeholders.
\Drupal::database()->select('dbtng_example')
  ->fields('dbtng_example')
  ->where('uid = :uid AND name = :name', [':name' => 'John', ':uid' => 0])
  ->execute();

Reach for option 1 (a raw query string) only when the query never changes shape. Reach for option 2 (chained condition() calls) for the vast majority of cases — it's what you saw in load(). Reach for option 3 (where() with a raw fragment) only when your condition logic is too complex to express as a series of simple field/value/operator triples — for example, an OR grouping that condition() alone can't easily express.

Building a JOIN, one piece at a time

advancedLoad() demonstrates the full toolkit together. Table aliasing first:

$select = $this->connection->select('dbtng_example', 'e');

The second argument, 'e', is a short alias for the table — every later reference uses e instead of repeating dbtng_example. This becomes necessary, not just convenient, the moment a second table enters the query.

$select->join('users_field_data', 'u', 'e.uid = u.uid');

join() performs an INNER JOIN — a row only appears in the results if it has a match in both tables. The three arguments are the table to join, its alias, and the raw ON condition connecting the two. If you wanted rows from dbtng_example even when there's no matching user, you'd use leftJoin() instead.

$select->addField('e', 'pid');
$select->addField('u', 'name', 'username');
$select->addField('e', 'name');

Once two tables are joined, ->fields('e') alone isn't enough — you need to say precisely which column comes from which table. addField() does that column by column, and its optional third argument renames the output column. Here u.name (the joined user's account name) becomes username in the result, so it can't be confused with e.name (the example table's own name column) — without that rename, PHP would only be able to keep one of the two name keys.

$select->condition('e.name', 'John');
$select->condition('e.age', 18, '>');

Conditions on an aliased table always use dot notation — e.name, not just name — for the same reason: once two tables are involved, an unqualified column name is ambiguous.

Why range() is a safety net, not a nicety

$select->range(0, 50);

This single line adds a SQL LIMIT 50 OFFSET 0 to the query. It's easy to skip when you're testing against a table with a handful of rows — but imagine this same query running against a table with two million rows and no range() call. PHP would try to fetch, allocate memory for, and process every single one of those rows, on every page load. That's not a hypothetical: unbounded queries are one of the most common causes of a Drupal site grinding to a halt as its data grows. Get in the habit of adding range() to any query where the result set size isn't already guaranteed to be small.

$entries = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);

One last detail: passing \PDO::FETCH_ASSOC here returns each row as a plain associative array rather than the default stdClass object. Neither is "more correct" — pick whichever shape is more convenient for what happens to the data next. Associative arrays are often handy when the data is about to be serialized to JSON or passed into another array-based API.

See it for yourself

Visit /examples/dbtng-example/advanced on your DDEV site to see advancedLoad() in action.

The DBTNG advanced list page showing a joined query result with username, name, surname, and age columns

Look closely at the "Created by" column — that value doesn't live in the dbtng_example table at all. It's pulled from Drupal's own users_field_data table via the JOIN you just read, proving the alias, addField(), and condition() calls all worked together correctly.

Quick check: if you removed the ->condition('e.age', 18, '>') line entirely, would the query still run? What would change about the results? (It would still run fine — you'd simply see every person named "John," regardless of age, since that condition is what filters them out.)

Key takeaways

  • Register database logic as a repository service injected with Connection via the constructor — this keeps controllers thin, avoids static \Drupal::database() calls, and makes the code unit-testable with mock connections.
  • Use Connection::select()->fields()->condition()->execute()->fetchAll() to build dynamic SELECT queries where the number and type of WHERE clauses is determined at runtime from an input array, rather than writing a different query for every case.
  • Chain ->condition($field, $value, $operator) calls to stack AND conditions; the default operator is =, but you can pass >, <, >=, <=, <>, LIKE, IN, NOT IN, and others as the third argument.
  • Use ->join(), ->leftJoin(), or ->rightJoin() to combine tables, and pair it with addField() to selectively retrieve and rename columns from each table in the joined result.
  • Always call ->range($start, $length) on any query that could return a large result set — it's a scalability safeguard, not just a pagination convenience.
  • Wrap INSERT and UPDATE operations in try/catch to handle constraint violations gracefully, reported through MessengerInterface instead of letting uncaught exceptions crash the page.

Coming up next

You've now covered the full database toolkit: reading, writing, filtering, and joining, all through a hand-written custom table. But most Drupal modules need somewhere to store their own settings too — a simple on/off flag, an API key, a display option — and building a custom table for that would be overkill. In the next topic, Configuration, you'll learn Drupal's purpose-built Config API: a much simpler way to store a module's own settings, complete with a settings form and export/import support built in for free.