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 withAND/ORlogic;
GROUP/ORDER: Grouping, sorting with reset controls;
CRUD:INSERT,UPDATE,DELETEoperations;
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.
SOCIAL SHARE CARD GENERATOR