TechStar Asia - Tech News for Builders and Operators

I've put together this quick QueryBuilder cheat sheet so you don't have to dig through Doctrine docs or source code every time you need a method reference.

Contents Overview:

SELECT: Field selection and DISTINCT options;
FROM & JOIN: Table setup and join types (INNER, LEFT, RIGHT);
WHERE/HAVING: Conditions with AND/OR logic;
GROUP/ORDER: Grouping, sorting with reset controls;
CRUD: INSERT, UPDATE, DELETE operations;
Advanced: UNION (DBAL 4.x), CTE, LIMIT/OFFSET, caching.

1. SELECT

select(string ...$expressions) — Defines SELECT fields, overwriting any previous selection.

// SELECT u.id, u.name, u.email FROM ...
$qb->select('u.id', 'u.name', 'u.email');

addSelect(string $expression, string ...$expressions) — Appends fields to existing SELECT clause.

// SELECT u.id, p.title, p.content FROM ...
$qb->select('u.id')->addSelect('p.title', 'p.content');

distinct(bool $distinct = true) — Adds/removes DISTINCT to eliminate duplicate rows.

// SELECT DISTINCT u.city FROM ...
$qb->select('u.city')->distinct();

2. FROM & JOIN

from(string $table, ?string $alias = null) — Defines the primary table in the FROM clause, optionally assigning it a table alias for cleaner query syntax.

// FROM users u
$qb->from('users', 'u');

join(string $fromAlias, string $join, string $alias, ?string $condition = null) — Creates INNER JOIN between tables, returning only matching rows from both sides based on the ON condition.

// INNER JOIN posts p ON p.user_id = u.id
$qb->from('users', 'u')->join('u', 'posts', 'p', 'p.user_id = u.id');

innerJoin(string $fromAlias, string $join, string $alias, ?string $condition = null) — Explicit INNER JOIN with the same behavior as join() - only matched rows are returned.

// INNER JOIN posts p ON p.user_id = u.id  
$qb->from('users', 'u')->innerJoin('u', 'posts', 'p', 'p.user_id = u.id');

leftJoin(string $fromAlias, string $join, string $alias, ?string $condition = null) — LEFT JOIN that includes all rows from the left table (FROM table) even if no matches exist on the right.

// LEFT JOIN comments c ON c.user_id = u.id
$qb->from('users', 'u')->leftJoin('u', 'comments', 'c', 'c.user_id = u.id');

rightJoin(string $fromAlias, string $join, string $alias, ?string $condition = null) — RIGHT JOIN that includes all rows from the right table even if no matches exist on the left.

// RIGHT JOIN profiles pr ON pr.user_id = u.id
$qb->from('users', 'u')->rightJoin('u', 'profiles', 'pr', 'pr.user_id = u.id');

3. WHERE/HAVING

where(string|CompositeExpression $predicate, ...$predicates) — Sets or completely replaces the entire WHERE clause with new conditions, discarding any previous filters.

// WHERE u.active = 1 AND u.role = 'admin'
$qb->select('u.*')
   ->from('users', 'u')
   ->where('u.active = :active AND u.role = :role')
   ->setParameter('active', 1)
   ->setParameter('role', 'admin');

andWhere(string|CompositeExpression $predicate, ...$predicates) — Appends AND conditions to the existing WHERE clause for additional filtering.

// WHERE u.active = 1 AND u.age > 18
$qb->where('u.active = :active')
   ->andWhere('u.age > :age')
   ->setParameter('active', 1)
   ->setParameter('age', 18);

orWhere(string|CompositeExpression $predicate, ...$predicates) — Adds OR conditions to the WHERE clause, creating alternative matching paths.

// WHERE u.active = 1 OR u.role = 'guest'
$qb->where('u.active = :active')
   ->orWhere('u.role = :role')
   ->setParameter('active', 1)
   ->setParameter('role', 'guest');

having(string|CompositeExpression $predicate, ...$predicates) — Sets or replaces the HAVING clause for filtering grouped results (used after GROUP BY).

// HAVING COUNT(p.id) > 5
$qb->select('u.id, COUNT(p.id) as post_count')
   ->from('users', 'u')
   ->leftJoin('u', 'posts', 'p', 'p.user_id = u.id')
   ->groupBy('u.id')
   ->having('COUNT(p.id) > :count')
   ->setParameter('count', 5);

andHaving(string|CompositeExpression $predicate, ...$predicates) — Appends AND conditions to existing HAVING clause for grouped data filtering.

// HAVING COUNT(p.id) > 5 AND AVG(p.rating) > 4.0
$qb->groupBy('u.id')
   ->having('COUNT(p.id) > :min_posts')
   ->andHaving('AVG(p.rating) > :min_rating')
   ->setParameter('min_posts', 5)
   ->setParameter('min_rating', 4.0);

orHaving(string|CompositeExpression $predicate, ...$predicates) — Adds OR conditions to HAVING clause for flexible grouped result filtering.

// HAVING COUNT(p.id) > 10 OR AVG(p.rating) > 4.5
$qb->groupBy('u.id')
   ->having('COUNT(p.id) > :high_count')
   ->orHaving('AVG(p.rating) > :high_rating')
   ->setParameter('high_count', 10)
   ->setParameter('high_rating', 4.5);

4. GROUP/ORDER

groupBy(string $expression, ...$expressions) — Groups rows that have matching values in specified columns, typically used with aggregate functions like COUNT, SUM, AVG.

// GROUP BY u.city - aggregates users by location
$qb->select('u.city, COUNT(u.id) as user_count')
   ->from('users', 'u')
   ->groupBy('u.city');

addGroupBy(string $expression, ...$expressions) — Appends additional grouping columns to existing GROUP BY clause for multi-level grouping.

// GROUP BY u.city, u.role
$qb->select('u.city, u.role, COUNT(*)')
   ->from('users', 'u')
   ->groupBy('u.city')
   ->addGroupBy('u.role');

orderBy(string $sort, ?string $order = null) — Sets primary sorting for query results (ASC/DESC or default ASC), replacing any existing ORDER BY.

// ORDER BY u.created_at DESC
$qb->select('*')
   ->from('users', 'u')
   ->orderBy('u.created_at', 'DESC');

addOrderBy(string $sort, ?string $order = null) — Adds secondary sorting rules to existing ORDER BY for multi-column sorting.

// ORDER BY u.created_at DESC, u.name ASC
$qb->select('*')
   ->from('users', 'u')
   ->orderBy('u.created_at', 'DESC')
   ->addOrderBy('u.name', 'ASC');

5. CRUD

insert(string $table) — Switches QueryBuilder to INSERT mode and sets the target table for new record creation.

// INSERT INTO users (name, email) VALUES ...
$qb->insert('users')
   ->setValue('name', ':name')
   ->setValue('email', ':email')
   ->setParameter('name', 'John')
   ->setParameter('email', '[email protected]');

setValue(string $column, string $value) — Sets individual column values for INSERT operations using placeholders for parameters.

// INSERT INTO users (name) VALUES ('Jane')
$qb->insert('users')
   ->setValue('name', '?')
   ->setParameter(0, 'Jane');

values(array $values) — Bulk sets multiple column values for INSERT in one call using associative array format.

// INSERT INTO users (name, email, active) VALUES ...
$qb->insert('users')
   ->values([
       'name' => ':name',
       'email' => ':email', 
       'active' => ':active'
   ])
   ->setParameters([
       'name' => 'Bob',
       'email' => '[email protected]',
       'active' => 1
   ]);

update(string $table) — Switches QueryBuilder to UPDATE mode and specifies the table to modify existing records.

// UPDATE users SET active = 0 WHERE id = 123
$qb->update('users', 'u')
   ->set('active', '?')
   ->where('u.id = ?')
   ->setParameter(0, 0)
   ->setParameter(1, 123);

set(string $key, string $value) — Defines SET clauses for UPDATE operations with column values and placeholders.

// UPDATE users SET name = 'Updated', active = 1 WHERE ...
$qb->update('users')
   ->set('name', ':name')
   ->set('active', ':active')
   ->where('id = :id')
   ->setParameters([
       'name' => 'Updated Name',
       'active' => 1,
       'id' => 456
   ]);

delete(string $table) — Switches QueryBuilder to DELETE mode and sets the table from which records will be removed.

// DELETE FROM users WHERE id = 789
$qb->delete('users', 'u')
   ->where('u.id = :id')
   ->setParameter('id', 789);

6. UNION (DBAL 4.x)

union(string|QueryBuilder $part) — Adds the first UNION block to combine results from multiple SELECT queries, removing duplicates by default.

// (SELECT u.id FROM users u) UNION (SELECT a.id FROM admins a)
$qb1 = $entityManager->createQueryBuilder()
    ->select('u.id')
    ->from('users', 'u');

$qb2 = $entityManager->createQueryBuilder()
    ->select('a.id')
    ->from('admins', 'a');

$qb1->union($qb2->getDQL());

addUnion(string|QueryBuilder $part, UnionType $type = UnionType::DISTINCT) — Appends additional UNION blocks to combine more query results (DISTINCT removes duplicates, ALL keeps them).

// (SELECT u.name FROM users) UNION (SELECT p.name FROM profiles) UNION ALL (SELECT g.name FROM guests)
$qb->select('u.name')
   ->from('users', 'u')
   ->union('(SELECT p.name FROM profiles p)')
   ->addUnion('(SELECT g.name FROM guests g)', UnionType::ALL);

7. CTE

with(string $name, string|QueryBuilder $part, ?array $columns = null) — Creates Common Table Expression (CTE) with named subquery that can be referenced multiple times in the main query.

// WITH active_users AS (SELECT * FROM users WHERE active = 1)
$qb->with('active_users', '(SELECT * FROM users u WHERE u.active = 1)')
   ->select('au.id, au.name, COUNT(p.id) as post_count')
   ->from('active_users', 'au')
   ->leftJoin('au', 'posts', 'p', 'p.user_id = au.id')
   ->groupBy('au.id, au.name');

Complex CTE example with column names:

// WITH user_stats(id, post_count) AS (...)
$qb->with('user_stats', '(SELECT u.id, COUNT(p.id) as post_count FROM users u LEFT JOIN posts p ON p.user_id = u.id GROUP BY u.id)', ['id', 'post_count'])
   ->select('stats.id, stats.post_count')
   ->from('user_stats', 'stats')
   ->having('stats.post_count > 10');

8. LIMIT/OFFSET

setFirstResult(int $firstResult) — Sets OFFSET to skip the first N rows, useful for pagination (combined with LIMIT).

// Skip first 20 users (page 3 with 20 per page)
$qb->select('u.id, u.name')
   ->from('users', 'u')
   ->setFirstResult(20)
   ->setMaxResults(20);
// OFFSET 20 LIMIT 20

setMaxResults(?int $maxResults) — Sets LIMIT to restrict the maximum number of returned rows, perfect for pagination and performance.

// Get only 10 most recent users
$qb->select('u.*')
   ->from('users', 'u')
   ->orderBy('u.created_at', 'DESC')
   ->setMaxResults(10);
// LIMIT 10

Pagination example:

// Page 2 (results 11-20)
$qb->select('*')->from('users', 'u')
   ->setFirstResult(10)  // Skip first 10
   ->setMaxResults(10);  // Take next 10

9. Parameters

setParameter(int|string $key, mixed $value, $type = ParameterType::STRING) — Binds a single named or positional parameter to prevent SQL injection and support all data types.

// Named parameter :userId
$qb->where('u.id = :userId')
   ->setParameter('userId', 123, ParameterType::INTEGER);
// WHERE u.id = ?

setParameters(array $params, array $types = []) — Binds multiple parameters at once using associative array for cleaner bulk parameter assignment.

$qb->where('u.id = :id AND u.role = :role AND u.active = :active')
   ->setParameters([
       'id' => 123,
       'role' => 'admin', 
       'active' => 1
   ], [
       'id' => ParameterType::INTEGER,
       'role' => ParameterType::STRING,
       'active' => ParameterType::BOOLEAN
   ]);

createNamedParameter(mixed $value, $type = ParameterType::STRING, ?string $placeHolder = null) — Creates auto-named parameter with :dcValueN format for dynamic conditions.

$param = $qb->createNamedParameter(42, ParameterType::INTEGER);
// Returns: :dcValue1
$qb->where('u.id > ' . $param);

createPositionalParameter(mixed $value, $type = ParameterType::STRING) — Creates positional parameter with ? placeholder for sequential binding.

$param = $qb->createPositionalParameter('admin', ParameterType::STRING);
// Returns: ?
$qb->where('u.role = ' . $param);

10. Execution

executeQuery(): Result — Executes SELECT queries and returns a Result object for iterating large result sets efficiently.

// Execute SELECT and get Result
$result = $qb->select('u.id, u.name')
   ->from('users', 'u')
   ->where('u.active = :active')
   ->setParameter('active', 1)
   ->getQuery()
   ->executeQuery();

foreach ($result as $row) {
    echo $row['id'] . ': ' . $row['name'];
}

executeStatement(): int|string — Executes DML operations (INSERT/UPDATE/DELETE) and returns the number of affected rows.

// UPDATE and return affected rows count
$affectedRows = $qb->update('users', 'u')
   ->set('active', '?')
   ->where('u.id IN (?)')
   ->setParameter(0, 0)
   ->setParameter(1, [123, 456])
   ->executeStatement();

echo "Updated $affectedRows rows";

fetchAssociative(): array|false — Fetches first row only as associative array, returns false if no results.

// Get single user by ID
$user = $qb->select('u.id, u.name')
   ->from('users', 'u')
   ->where('u.id = ?')
   ->setParameter(0, 123)
   ->getQuery()
   ->fetchAssociative();

if ($user) {
    print_r($user);
}

fetchAllAssociative(): array — Fetches all rows as array of associative arrays (convenience method).

// Get all active users as array
$users = $qb->select('u.id, u.name, u.email')
   ->from('users', 'u')
   ->where('u.active = ?')
   ->setParameter(0, 1)
   ->getQuery()
   ->fetchAllAssociative();

getSQL(): string — Generates complete SQL with bound parameters for debugging and logging.

// Debug: see exact SQL that will be executed
echo $qb->getSQL();
// SELECT u.id, u.name FROM users u WHERE u.active = ? -- :1: 1

11. Management

resetWhere() — Clears all WHERE conditions completely, removing any existing filters and starting with empty WHERE clause.

// Remove all WHERE conditions
$qb->where('u.active = 1')
   ->andWhere('u.role = "admin"')
   ->resetWhere();
// WHERE clause is now empty

resetGroupBy() — Removes all GROUP BY clauses, eliminating grouping and allowing aggregate-free queries again.

// Clear GROUP BY to run without grouping
$qb->groupBy('u.city')
   ->addGroupBy('u.role')
   ->resetGroupBy();
// GROUP BY is now empty

resetHaving() — Clears all HAVING conditions used for filtering grouped results.

// Remove HAVING filters
$qb->groupBy('u.id')
   ->having('COUNT(p.id) > 5')
   ->resetHaving();
// HAVING clause is now empty

resetOrderBy() — Removes all ORDER BY clauses, returning results in natural database order.

// Clear sorting
$qb->orderBy('u.created_at', 'DESC')
   ->addOrderBy('u.name')
   ->resetOrderBy();
// ORDER BY is now empty

12. Cache

enableResultCache(QueryCacheProfile $cacheProfile) — Enables result caching for SELECT queries using Doctrine cache provider, dramatically speeding up repeated identical queries.

use Doctrine\DBAL\Cache\QueryCacheProfile;

$cacheProfile = new QueryCacheProfile(3600, 'user_cache_key'); // 1 hour TTL

$qb->select('u.id, u.name')
   ->from('users', 'u')
   ->enableResultCache($cacheProfile);
// Results cached for 1 hour

disableResultCache() — Disables any active result caching, forcing fresh database execution on next query run.

$qb->select('u.*')
   ->from('users', 'u')
   ->enableResultCache($someCacheProfile)
   ->disableResultCache();
// Cache disabled - always fresh data

Done!

This cheat sheet packs all core Doctrine QueryBuilder methods into 13 practical sections - from basic SELECT/JOIN/WHERE to advanced CTE, UNION, caching, and ExpressionBuilder.

Everything you need for writing clean, efficient SQL queries without constant docs lookup. Compact, example-driven, ready-to-use.

Doctrine QueryBuilder Methods Cheat Sheet

1. SELECT

2. FROM & JOIN

3. WHERE/HAVING

4. GROUP/ORDER

5. CRUD

6. UNION (DBAL 4.x)

7. CTE

8. LIMIT/OFFSET

9. Parameters

10. Execution

11. Management

12. Cache

Done!

Comments (0)

United States

Related News

CBS News Shutters Radio Service After Nearly a Century

White House Unveils National AI Policy Framework To Limit State Power

Officer Leaks Location of French Aircraft Carrier With Strava Run

Microsoft Says It Is Fixing Windows 11

NASA's Hubble Unexpectedly Catches Comet Breaking Up