A basic select() with a couple of condition() calls gets you far, but real queries often need to reach across tables, filter with ranges instead of exact matches, and cap how many rows come back — all decided at runtime, not hardcoded. That's what "dynamic" means here: the same query-builder object, extended with a few more fluent method calls.
What you'll learn in this lesson
- How to join another table onto your query with
join() - How to select specific columns from specific tables with
addField() - How to filter with comparison operators, not just equality
- How to cap results with
range()for scalability
A real dynamic query, piece by piece
public function advancedLoad() {
$select = $this->connection->select('dbtng_example', 'e');
$select->join('users_field_data', 'u', 'e.uid = u.uid');
$select->addField('e', 'pid');
$select->addField('u', 'name', 'username');
$select->addField('e', 'name');
$select->addField('e', 'surname');
$select->addField('e', 'age');
$select->condition('e.name', 'John');
$select->condition('e.age', 18, '>');
$select->range(0, 50);
return $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
}Aliasing and joining
select('dbtng_example', 'e') gives the table a short alias, e, so later references stay readable. join('users_field_data', 'u', 'e.uid = u.uid') adds an inner join to Drupal's own user table, matching each entry's uid to the user who created it — exactly like a SQL JOIN, because under the hood, it is one.
Picking exact columns with addField()
addField('u', 'name', 'username') is worth pausing on: the three arguments are table alias, column name, and an output alias. Since both the entry table and the joined users table have a column literally called name, without renaming one of them the result set would have a column collision. Aliasing u.name to username resolves that cleanly.
Comparison operators and range()
condition('e.age', 18, '>') shows the third argument to condition() — an explicit operator, defaulting to = when omitted (as in the previous lesson). range(0, 50) is Drupal's portable equivalent of SQL's LIMIT/OFFSET, and its comment in the real source is worth taking seriously: "for scalability reasons." Any query that could return an unbounded number of rows on a growing site needs a range or a pager — never assume a result set stays small forever.
PDO::FETCH_ASSOC here instead of the default object mode? fetchAll() defaults to returning an array of objects ($row->name), but you can pass any PDO fetch-style constant to get a different shape back — here, plain associative arrays ($row['name']). Neither is more "correct"; pick whichever shape the calling code will find easiest to work with.Quick check: if you removed the
range(0, 50)call entirely, what would change? (Nothing about correctness — the query would still return every matching row — but on a table with millions of entries, it could return an enormous result set and consume far more memory and time than the page actually needs.range()is a performance safeguard, not a correctness requirement.)
Key takeaways
- Alias tables in
select($table, $alias)so joins and field references stay readable. join()adds an inner join between two tables using a plain SQL-style condition string.addField($alias, $column, $output_alias)both picks specific columns and resolves name collisions between joined tables.condition()accepts an explicit third-argument operator (>,<,LIKE, etc.) beyond the default equality check.- Always cap potentially large result sets with
range()— it's a scalability safeguard, not optional polish.
Coming up next
Dynamic queries are the everyday tool, but Drupal also supports writing genuinely static SQL when you need it. Next, you'll see when and how to do that safely.