Doctrine QueryBuilder Methods Cheat Sheet

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', '[e-mail]');

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' => '[e-mail]',
       '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.