⚠️ Important: Some PostgreSQL operators have multiple meanings depending on the data types involved. This library provides specific DQL function names to avoid conflicts:
Operator
Array/JSON Usage
Spatial Usage
Text/Pattern Usage
@>
CONTAINS (arrays contain elements)
Works automatically with geometry/geography
N/A
<@
IS_CONTAINED_BY (element in array)
Works automatically with geometry/geography
N/A
&&
OVERLAPS (arrays/ranges overlap)
Works automatically with geometry/geography
N/A
Usage Guidelines:
Arrays/JSON: Use CONTAINS, IS_CONTAINED_BY, OVERLAPS for array and JSON operations
Boolean operators: All operators return boolean values and should be used with = TRUE or = FALSE in DQL
ARRAY takes strings, fields, parameters and function calls; a bare number such as ARRAY(1, 2) does not parse. Quoted elements build a text[], which PostgreSQL will not compare with a non-text array column — there, pass a PostgreSQL array literal instead: CONTAINS(e.integerArray, '{1,2}') = TRUE.
FILTER wraps the aggregate in DQL
In SQL, FILTER (WHERE ...) follows the aggregate’s closing parenthesis. DQL cannot parse anything after a function’s closing parenthesis, so in DQL FILTER becomes a function around the aggregate, with the condition as its second argument:
SQL
DQL
COUNT(e.id) FILTER (WHERE e.status = 'active')
FILTER(COUNT(e.id), WHERE e.status = 'active')
SUM(e.amount) FILTER (WHERE e.refunded = false)
FILTER(SUM(e.amount), WHERE e.refunded = FALSE)
array_agg(e.id ORDER BY e.createdAt) FILTER (WHERE e.archivedAt IS NULL)
FILTER(ARRAY_AGG(e.id ORDER BY e.createdAt), WHERE e.archivedAt IS NULL)
percentile_cont(0.5) WITHIN GROUP (ORDER BY e.amount) FILTER (WHERE e.paid = true)
FILTER(PERCENTILE_CONT(0.5 WITHIN GROUP ORDER BY e.amount), WHERE e.paid = TRUE)
A comma separates the aggregate from WHERE, and the condition has no parentheses around it.
The first argument must be an aggregate: DQL’s own AVG, COUNT, MAX, MIN and SUM, or a function implementing MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AggregateFunction. Every aggregate in this library implements it; implement it on an aggregate of your own to wrap that one too. Anything else, including a nested FILTER, throws a ParserException, as PostgreSQL would reject it.
The condition takes anything a DQL WHERE does, including parameters.
Usage Examples
-- DQL array literal syntax: ARRAY('val1', 'val2') — not standard PHP array notationSELECTeFROMEntityeWHERECONTAINS(e.tags,ARRAY('important','urgent'))=TRUESELECTeFROMEntityeWHEREOVERLAPS(e.categories,ARRAY('admin','user'))=TRUE-- JSON_BUILD_OBJECT takes alternating key/value pairs; result is a JSON objectSELECTe.id,JSON_BUILD_OBJECT('name',e.name,'status',e.status,'score',e.score)asjson_dataFROMEntitye-- ARRAY_AGG with ORDER BY inside the aggregateSELECTe.category,ARRAY_AGG(e.idORDERBYe.createdAtDESC)asentity_idsFROMEntityeGROUPBYe.category-- FILTER wraps the aggregate in DQL (see above)SELECTe.category,FILTER(COUNT(e.id),WHEREe.status='active')asactive_countFROMEntityeGROUPBYe.categorySELECTe.category,FILTER(ARRAY_AGG(e.idORDERBYe.createdAt),WHEREe.archivedAtISNULL)aslive_idsFROMEntityeGROUPBYe.category
💡 Tips for Usage:
Boolean operators should be used with = TRUE or = FALSE in DQL
Array functions provide efficient PostgreSQL array operations
JSON functions support both JSON and JSONB data types
JSONB functions offer better performance for complex JSON operations
