Being Doctrine’s query language, DQL syntax is different from the PostgreSQL’s one you already know. PostgreSQL’s syntax often does not parse as written in DQL. This page covers rules and patterns that the library follows:
what each PostgreSQL operator and function is called in DQL;
why boolean functions need = TRUE;
how clauses written after a closing parenthesis move inside it;
what each argument accepts; and
what to do when DQL cannot express a query at all.
Every function is registered under the DQL name the setup guides use, as in Doctrine setup.
Function and operator names
DQL has a fixed set of operators (=, <>, !=, <, <=, >, >=, LIKE, IN, BETWEEN, IS NULL, MEMBER OF, INSTANCE OF, EXISTS) and no way to add another. Every PostgreSQL operator the library supports is therefore a function, which the library renders back into the operator:
SELECT p.name FROM App\Entity\Product p WHERE CONTAINS(p.tags, ARRAY('sport')) = TRUE
The naming rule
You choose the name when you call addCustomStringFunction(). The setup guides, the examples and the issue tracker all use the same rule, so keep to it unless you have a reason not to:
A PostgreSQL function usually keeps its name in DQL but upper-cased: jsonb_path_exists is JSONB_PATH_EXISTS, PostGIS’s ST_DWithin is ST_DWITHIN, pg_trgm’s similarity is SIMILARITY.
An operator or an SQL construct gets a name that says what it does: @> is CONTAINS, ->> is JSON_GET_FIELD_AS_TEXT, @@ is TSMATCH, AT TIME ZONE is AT_TIME_ZONE, (a, b) OVERLAPS (c, d) is DATE_OVERLAPS, value = ANY(array) is IN_ARRAY, and ARRAY[...] is ARRAY(...).
A few functions are renamed, so that the name says what it works on or does not collide with DQL:
Three DQL names are also Doctrine built-ins: DATE_ADD, BIT_AND and BIT_OR. Doctrine looks up registered functions first, so once you register the library’s versions, they replace Doctrine’s everywhere in the application: DATE_ADD(s.placedAt, 1, 'day') stops parsing (got '1'), and BIT_AND becomes the aggregate rather than Doctrine’s two-argument bitwise AND. Register them only if you want PostgreSQL’s meaning.
Except for the ltree casts, names that share an operator render the same SQL, so it is still the operand types that decide what runs: SPATIAL_CONTAINS(p.name, 'x') runs a regular expression. Two consequences follow:
@> and <@ have no geometry variant; for spatial containment use SPATIAL_CONTAINS (bounding boxes) or ST_CONTAINS (exact shapes).
When both operands are bare string literals, PostgreSQL resolves them as text. 'POLYGON((0 0,2 0,2 2,0 2,0 0))' ~ 'POINT(1 1)' is a regular-expression match and returns false, while the same call with the polygon cast to geometry returns true. Keep a column on one side, or give one literal a type with CAST(... AS GEOMETRY).
pgvector’s distance operators are reached through the functions L2_DISTANCE, COSINE_DISTANCE and INNER_PRODUCT instead. In the SQL Doctrine logs, the ? operators appear doubled, as ??: PDO would read a single ? as a placeholder, and turns ?? back into ? before PostgreSQL sees it.
Coming from MySQL or DoctrineExtensions
Some habits carry over with a different argument order or spelling:
-- parses, PostgreSQL rejects it: syntax error at or near "."
SELECT DATE_EXTRACT(s.placedAt, 'month') FROM App\Entity\Sale s
Boolean functions need a comparison
A WHERE or HAVING clause in DQL is built from conditions: comparisons, LIKE, IN, IS NULL, EXISTS and the like. A function call on its own is a value, not a condition, so the parser stops after it and asks for a comparison operator. Compare a boolean function with TRUE:
SELECTs.*FROMsalesWHEREs.referenceILIKE'A-%'
SELECT s FROM App\Entity\Sale s WHERE ILIKE(s.reference, :q) = TRUE
Without the comparison, and with the SQL operator, the query does not parse:
-- does not parse: Error: Expected =, <, <=, <>, >, >=, !=, got 'ORDER'
SELECT s FROM App\Entity\Sale s WHERE ILIKE(s.reference, :q) ORDER BY s.id
-- does not parse: Error: Expected =, <, <=, <>, >, >=, !=, got 'ILIKE'
SELECT s FROM App\Entity\Sale s WHERE s.reference ILIKE :q
When nothing follows the function, the message ends in a got end of string error instead.
The comparison costs nothing. PostgreSQL removes = true before planning, so an index on the operator is used as if you had written the bare operator:
The distance operators and the JSON accessors return values, not booleans; compare them like any other value, as in GEOMETRY_DISTANCE(...) < 1000.
After the closing parenthesis
PostgreSQL attaches some clauses to a call after its closing parenthesis: FILTER (WHERE ...), OVER (...) and WITHIN GROUP (ORDER BY ...). DQL reads a function call up to its closing parenthesis and then expects the next part of the query, so none of them can follow a call. The library moves each one inside a call instead:
SQL
DQL
count(s.id) FILTER (WHERE s.status = 'paid')
FILTER(COUNT(s.id), WHERE s.status = 'paid')
sum(s.amount) OVER (PARTITION BY s.customer_id ORDER BY s.placed_at)
OVER(SUM(s.amount), PARTITION BY s.customer ORDER BY s.placedAt)
sum(s.amount) FILTER (WHERE s.status = 'paid') OVER (PARTITION BY s.customer_id)
OVER(FILTER(SUM(s.amount), WHERE s.status = 'paid'), PARTITION BY s.customer)
percentile_cont(0.5) WITHIN GROUP (ORDER BY s.amount)
PERCENTILE_CONT(0.5 WITHIN GROUP ORDER BY s.amount)
mode() WITHIN GROUP (ORDER BY s.status)
MODE(WITHIN GROUP ORDER BY s.status)
Written the SQL way, DQL takes the clause keyword for a column alias and fails on the parenthesis after it:
-- does not parse: Error: Expected Doctrine\ORM\Query\TokenType::T_FROM, got '('
SELECT c.name, COUNT(s.id) FILTER (WHERE s.status = 'paid') FROM App\Entity\Sale s JOIN s.customer c GROUP BY c.name
FILTER wraps the aggregate
FILTER takes the aggregate, a comma, WHERE and the condition. The condition takes anything a DQL WHERE does, parameters included, and needs no parentheses. It works in SELECT and in HAVING, and counts several subsets in one pass over the rows:
SELECT c.name, COUNT(s.id) AS allSales, FILTER(COUNT(s.id), WHERE s.status = 'paid') AS paidSales, FILTER(SUM(s.amount), WHERE s.placedAt >= :startOfYear) AS paidThisYear
FROM App\Entity\Sale s JOIN s.customer c
GROUP BY c.name
OVER wraps the call
OVER takes the call, a comma and the window specification: PARTITION BY, ORDER BY and a frame, in that order, each optional, with no parentheses around them. Without a specification the window is the whole result. FILTER goes inside OVER, as it comes before OVER in SQL.
SELECT s.reference, OVER(SUM(s.amount), PARTITION BY s.customer ORDER BY s.placedAt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningTotal
FROM App\Entity\Sale s
The window specification, the frame clauses and the ranking and value functions are covered in Window functions.
WITHIN GROUP moves inside the call
The fraction, then WITHIN GROUP ORDER BY and the sort expression, with no comma before WITHIN and no parentheses around ORDER BY. MODE takes no fraction, so its call starts with WITHIN GROUP. The fraction is a literal, a parameter, or an expression over grouped columns.
SELECT c.name, PERCENTILE_CONT(0.5 WITHIN GROUP ORDER BY s.amount) AS medianAmount, MODE(WITHIN GROUP ORDER BY s.status) AS usualStatus
FROM App\Entity\Sale s JOIN s.customer c
GROUP BY c.name
-- does not parse: Error: Expected a single ORDER BY item, got ')'
SELECT PERCENTILE_CONT(0.5 WITHIN GROUP ORDER BY s.amount, s.id) FROM App\Entity\Sale s
The sort items are DQL ORDER BY items, which have no NULLS FIRST or NULLS LAST. The aggregated value is a field, a string, a parameter or a function call; arithmetic stops the parser at the operator with an error. To overcome this, wrap it in a function that takes arithmetic, such as CAST:
SELECT ARRAY_AGG(CAST(s.amount * 2 AS NUMERIC)) AS doubled FROM App\Entity\Sale s
What FILTER and OVER accept
FILTER takes an aggregate: DQL’s own AVG, COUNT, MAX, MIN and SUM, DISTINCT included, or a function implementing MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AggregateFunction, which every aggregate of this library does. OVER takes the same, a FILTER(...), or a function implementing MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\WindowFunction, which the ranking and value functions do. Implement the interface on a function of your own to wrap that one too.
Anything else, including a nested FILTER or OVER, throws a ParserException while the query is parsed, since PostgreSQL would reject it anyway:
-- does not parse: FILTER() requires an aggregate as its first argument, upper() given
SELECT FILTER(UPPER(s.status), WHERE s.status = 'paid') FROM App\Entity\Sale s
The PostGIS aggregates, ST_COVERAGEUNION and the one-argument ST_MAKELINE, do not implement AggregateFunction, so neither can be wrapped.
Literals and parameters
Each argument of a library function is parsed as one kind of DQL expression, fixed by the function’s class. The kind decides what you can write in that position:
The element of ARRAY_APPEND, ARRAY_PREPEND, ARRAY_REMOVE, ARRAY_REPLACE, ARRAY_POSITION and ARRAY_POSITIONS; the fill value of ARRAY_FILL; TO_JSON, TO_JSONB; the new value of JSONB_SET_LAX; the default of LAG and LEAD
Strings and numbers
A DQL string is single-quoted. A double-quoted word is not a string:
-- does not parse: Error: Expected StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression, got '"'
SELECT DATE_EXTRACT("month", s.placedAt) FROM App\Entity\Sale s
SELECT NUMRANGE('20', '50') AS priceBand FROM App\Entity\Product p
ARRAY('php', 'postgres') renders ARRAY['php', 'postgres'], which PostgreSQL types as text[]. A text[] cannot be compared with an integer[] column (operator does not exist: integer[] && text[]); there, pass a PostgreSQL array literal as a string instead, such as '{1,2}'.
NULL
NULL is accepted only in the new-value positions listed above. Anywhere else it fails with got 'NULL'; bind a parameter set to null instead:
SELECT TSTZRANGE(s.placedAt, :null) AS fromThenOn FROM App\Entity\Sale s
Boolean and time-zone arguments
Some functions take an optional boolean or time-zone argument as their last one. Both positions take a string: DQL’s TRUE and FALSE literals are not accepted there, and DQL has no time-zone literal. So both are written as string literals and checked while the query is parsed:
Booleans are 'true' or 'false' and they are supported in ARRAY_TO_JSON, ROW_TO_JSON, JSON_STRIP_NULLS, JSONB_STRIP_NULLS, JSONB_SET, JSONB_INSERT, JSONB_PATH_EXISTS, JSONB_PATH_MATCH, JSONB_PATH_QUERY, JSONB_PATH_QUERY_ARRAY, JSONB_PATH_QUERY_FIRST, ST_AREA, ST_LENGTH, ST_DISTANCE, ST_CLOSESTPOINT, ST_LINELOCATEPOINT, ST_LINEINTERPOLATEPOINT, ST_CONCAVEHULL, ST_SIMPLIFYPOLYGONHULL, ST_REMOVEIRRELEVANTPOINTSFORVIEW. A bare TRUE, a parameter or any other string is rejected.
Time zones are checked with PHP’s DateTimeZone. A spelling that only PostgreSQL knows, such as the POSIX 'UTC+2', is rejected in DATE_TRUNC, DATE_ADD, DATE_SUBTRACT, MAKE_TIMESTAMPTZ.
SELECT JSONB_SET(p.attributes, '{color}', '"blue"', 'false') AS recoloured FROM App\Entity\Product p
-- does not parse: Invalid timezone "UTC+3" provided for date_trunc. Must be a valid PHP timezone identifier.
SELECT DATE_TRUNC('day', s.placedAt, 'UTC+3') FROM App\Entity\Sale s
A PHP array is different: Doctrine expands an untyped array parameter into a list, as it does for IN (:ids), and the query fails with operator does not exist: record = boolean. Pass the DBAL type name as the third argument, and the type converts the array into one PostgreSQL literal. Do the same for a value object, so its DBAL type writes it:
SELECT p.name FROM App\Entity\Product p WHERE OVERLAPS(p.tags, :tags) = TRUE
A list for IN is the opposite case, and takes DBAL’s array parameter type: setParameter('ids', [1, 3], ArrayParameterType::INTEGER).
CONTAINS on a jsonb column needs a JSON document on the right. Bind json_encode(['ROLE_ADMIN']), not 'ROLE_ADMIN', which PostgreSQL rejects with invalid input syntax for type json.
A jsonpath filter contains ?, which is also PDO’s placeholder character. Binding the whole path as a parameter keeps it out of the SQL text:
SELECT p.name FROM App\Entity\Product p WHERE JSONB_PATH_EXISTS(p.attributes, :path) = TRUE
CAST takes the target type as an unquoted, one-word identifier, with up to two integer parameters and an optional []: CAST(s.amount AS INTEGER), CAST(s.amount AS NUMERIC(12, 4)), CAST('{1,2}' AS INTEGER[]). For two-word types use the one-word alias PostgreSQL provides, such as FLOAT8 for double precision or TIMESTAMPTZ for timestamp with time zone:
-- does not parse: Error: Expected Doctrine\ORM\Query\TokenType::T_CLOSE_PARENTHESIS, got 'PRECISION'
SELECT CAST(s.amount AS DOUBLE PRECISION) FROM App\Entity\Sale s
What DQL cannot do
DQL maps queries onto entities, so some SQL has no DQL form at all. For those, drop to a native query, which still hydrates entities through a ResultSetMappingBuilder, or to DBAL:
$rows=$entityManager->createQuery('SELECT s, OVER(ROW_NUMBER(), PARTITION BY s.customer ORDER BY s.placedAt DESC) AS recency FROM App\Entity\Sale s')->getResult();$latestSales=array_column(array_filter($rows,fn(array$row):bool=>$row['recency']===1),0);
The same with DISTINCT ON in a native query, which leaves the other rows in the database:
$rsm=newResultSetMappingBuilder($entityManager);$rsm->addRootEntityFromClassMetadata(Sale::class,'s');$sql='SELECT DISTINCT ON (s.customer_id) '.$rsm->generateSelectClause().' FROM sale s ORDER BY s.customer_id, s.placed_at DESC';$latestSales=$entityManager->createNativeQuery($sql,$rsm)->getResult();
An upsert through DBAL:
$entityManager->getConnection()->executeStatement('INSERT INTO product_view (product_id, views) VALUES (:id, 1)
ON CONFLICT (product_id) DO UPDATE SET views = product_view.views + 1',['id'=>$product->getId()],);
Gotchas
Expected =, <, <=, <>, >, >=, !=, got 'ORDER' (or got end of string.): a boolean function stands alone in WHERE or HAVING. Add = TRUE, see Boolean functions need a comparison.
Expected =, <, <=, <>, >, >=, !=, got 'ILIKE', or got '@': PostgreSQL operator syntax in DQL. Use the function, such as ILIKE(s.reference, :q) = TRUE.
Expected Doctrine\ORM\Query\TokenType::T_SELECT, got 'p' (ORM 2: Doctrine\ORM\Query\Lexer::T_SELECT) for :tag = ANY(p.tags): ANY is DQL’s subquery keyword. Use :tag = ANY_OF(p.tags) or IN_ARRAY(:tag, p.tags) = TRUE.
Expected Doctrine\ORM\Query\TokenType::T_FROM, got '(' after COUNT(s.id) FILTER or SUM(s.amount) OVER: an SQL clause after the closing parenthesis. Wrap the call, see After the closing parenthesis.
Expected StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression, got '"', got '20' or got 'NULL': a double-quoted string, a number or NULL where a string primary goes. Single-quote strings and numbers; bind null as a parameter.
operator does not exist: geometry @> geometry: CONTAINS on geometries. Use SPATIAL_CONTAINS or ST_CONTAINS.
operator does not exist: record = boolean: a PHP array bound without a type expanded into a list. Pass the type name, setParameter('tags', $tags, 'text[]').
syntax error at or near "." from EXTRACT(s0_.placed_at FROM 'month'): DATE_EXTRACT with its arguments swapped. The field comes first: DATE_EXTRACT('month', s.placedAt).
Invalid boolean value "yes" provided for ST_Distance. Must be "true" or "false". or The boolean parameter for ST_Distance must be a string literal, got Doctrine\ORM\Query\AST\InputParameter: write the boolean as the literal 'true' or 'false'.
A catch (QueryException $e) misses date_trunc() requires at least 2 arguments: argument-count errors throw InvalidArgumentForVariadicFunctionException, an \InvalidArgumentException, and wrapper errors throw ParserException, a \RuntimeException. Catch those too.
