User Defined Function and Comparison Operator #5410

New Issue

admin · 2026-01-22T15:07:02+01:00

admin commented

2026-01-22 15:07:02 +01:00

Originally created by @dautushenka on GitHub (Feb 6, 2017).

I work with PostgreSQL. I implemented custom function for working with array type
DQL: CONTAINS(field, '{1,2}')
which should converted into SQL: field @> '{1,2}'
When I was trying to apply with function I got error:

[Syntax Error] line 0, col -1: Error: Expected =, <, <=, <>, >, >=, !=, got end of string.

After digging into the code I found out that parser requires comparison operator also.
I use following workaround:
CONTAINS(field,?1) = TRUE

I think we need to implement ability to create custom operators.

Originally created by @dautushenka on GitHub (Feb 6, 2017). I work with PostgreSQL. I implemented custom function for working with array type **DQL**: `CONTAINS(field, '{1,2}')` which should converted into SQL: `field @> '{1,2}'` When I was trying to apply with function I got error: > [Syntax Error] line 0, col -1: Error: Expected =, <, <=, <>, >, >=, !=, got end of string. After digging into the code I found out that parser requires comparison operator also. I use following workaround: `CONTAINS(field,?1) = TRUE` I think we need to implement ability to create custom operators.

admin added the Improvement DQL labels 2026-01-22 15:07:02 +01:00

admin commented

2026-01-22 15:07:04 +01:00

@Ocramius commented on GitHub (Feb 6, 2017):

What's missing is probably a boolean function - we support string/date/numeric atm, but not boolean.

@Ocramius commented on GitHub (Feb 6, 2017): What's missing is probably a boolean function - we support string/date/numeric atm, but not boolean.

admin commented

2026-01-22 15:07:04 +01:00

@dautushenka commented on GitHub (Feb 6, 2017):

@Ocramius It also will fix the issue.

@dautushenka commented on GitHub (Feb 6, 2017): @Ocramius It also will fix the issue.

admin commented

2026-01-22 15:07:04 +01:00

@zspine commented on GitHub (Jul 7, 2017):

Seems like most of the PostgreSQL functions requires it. I had the same issue with jsonb functions.

$qb = $this->createQueryBuilder('u');
$qb->andWhere('JSONB_EXISTS(u.roles, :role) = TRUE');
$qb->setParameter('role', User::ROLE_OWNER);
return $qb->getQuery()->getResult();

@dautushenka Thanks a lot for sharing the workaround!!! I have been trying to debug this for more than 4 hours....

@zspine commented on GitHub (Jul 7, 2017): Seems like most of the PostgreSQL functions requires it. I had the same issue with jsonb functions. ``` $qb = $this->createQueryBuilder('u'); $qb->andWhere('JSONB_EXISTS(u.roles, :role) = TRUE'); $qb->setParameter('role', User::ROLE_OWNER); return $qb->getQuery()->getResult(); ``` @dautushenka Thanks a lot for sharing the workaround!!! I have been trying to debug this for more than 4 hours....

admin commented

2026-01-22 15:07:04 +01:00

@esbobkov commented on GitHub (Nov 3, 2017):

I have problem with Comparison Operator. I want to use POSIX Regular Expressions in Postgres. Operator is ~
But in not supported

@esbobkov commented on GitHub (Nov 3, 2017): I have problem with Comparison Operator. I want to use [POSIX Regular Expressions](https://www.postgresql.org/docs/current/static/functions-matching.html) in Postgres. Operator is `~` But in not supported

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5410