CaseExpression as first parameter of LikeExpression not working as intended #6656

New Issue

admin · 2026-01-22T15:36:32+01:00

admin commented

2026-01-22 15:36:32 +01:00

Originally created by @MassimoDeFacciZucchettiSWGiuridico on GitHub (Mar 22, 2021).

Bug report

Short Explanation:
Inside a WhereClause, having a LikeExpression which the first term of is a CaseExpression invokes the error "Expected =, <, <=, <>, >, >=, !=, got 'LIKE'"

Long Explanation
I want to start using doctrine in a big program I'm working on, as it would make development time faster. To test it I tried converting a complex query I'm using to its doctrine orm implementation. This query is build based on a lot of parameters, but in this specific part it invokes an error.
What I need to do is, when some conditions are met, do a series of leftjoins and then apply a where clause, which like compares different parameters based on a value. To do that in the original sql query I used a WHERE clause, inside of which I used an IF condition, that would form the first parameter of the like comparison, like this simplified example:

WHERE (IF user.type = 'type_name',
  CONCAT_WS(' ', user.surname, user.name),
  CONCAT_WS(' ', user.identifier, user.other_data)
) LIKE %value_to_compare%

I converted that sql code into this doctrine one:

$query_builder
  ->andWhere(
    $query_builder->expr()->like(
      "(CASE
        WHEN user.type = 'type_name'
        THEN CONCAT(user.surname, ' ', user.name)
        ELSE CONCAT(user.identifier, ' ', user.other_data)
      END)",
      ":second_term"
    )
  )
  ->setParameter('second_term', '%' . $value_to_compare . '%')

When I do that tho, I get the error "Expected =, <, <=, <>, >, >=, !=, got 'LIKE'", which disappears if instead of using a like comparison I just use an equal operator. This doesnt make sense if we follow what's written in this page: here
in fact, following that same page definition, we know that there is a WhereClause, which only contains a ConditionalExpression. The ConditionalExpression its just a LikeExpression, the second term of which is just a string, while the first one is a StringExpression, a StringPrimary which is a CaseExpression.
I tried modifying the form of the code in various ways, like having the CaseExpression hold the LikeExpression inside of it's results, but to no success.

Summary
Let a WhereClause have a LikeExpression in which the first term is defined by a CaseExpression, as intended by documentation in this page: here

How to reproduce
this is a simplified version of the incriminated code

$query_builder
  ->andWhere(
    $query_builder->expr()->like(
      "(CASE
        WHEN user.type = 'type_name'
        THEN CONCAT(user.surname, ' ', user.name)
        ELSE CONCAT(user.identifier, ' ', user.other_data)
      END)",
      ":second_term"
    )
  )
  ->setParameter('second_term', '%' . $value_to_compare . '%')

Originally created by @MassimoDeFacciZucchettiSWGiuridico on GitHub (Mar 22, 2021). ### **Bug report** **Short Explanation:** Inside a WhereClause, having a LikeExpression which the first term of is a CaseExpression invokes the error "Expected =, <, <=, <>, >, >=, !=, got 'LIKE'" **Long Explanation** I want to start using doctrine in a big program I'm working on, as it would make development time faster. To test it I tried converting a complex query I'm using to its doctrine orm implementation. This query is build based on a lot of parameters, but in this specific part it invokes an error. What I need to do is, when some conditions are met, do a series of leftjoins and then apply a where clause, which like compares different parameters based on a value. To do that in the original sql query I used a WHERE clause, inside of which I used an IF condition, that would form the first parameter of the like comparison, like this simplified example: ```sql WHERE (IF user.type = 'type_name', CONCAT_WS(' ', user.surname, user.name), CONCAT_WS(' ', user.identifier, user.other_data) ) LIKE %value_to_compare% ``` I converted that sql code into this doctrine one: ```php $query_builder ->andWhere( $query_builder->expr()->like( "(CASE WHEN user.type = 'type_name' THEN CONCAT(user.surname, ' ', user.name) ELSE CONCAT(user.identifier, ' ', user.other_data) END)", ":second_term" ) ) ->setParameter('second_term', '%' . $value_to_compare . '%') ``` When I do that tho, I get the error "Expected =, <, <=, <>, >, >=, !=, got 'LIKE'", which disappears if instead of using a like comparison I just use an equal operator. This doesnt make sense if we follow what's written in this page: [here](https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#ebnf) in fact, following that same page definition, we know that there is a WhereClause, which only contains a ConditionalExpression. The ConditionalExpression its just a LikeExpression, the second term of which is just a string, while the first one is a StringExpression, a StringPrimary which is a CaseExpression. I tried modifying the form of the code in various ways, like having the CaseExpression hold the LikeExpression inside of it's results, but to no success. **Summary** Let a WhereClause have a LikeExpression in which the first term is defined by a CaseExpression, as intended by documentation in this page: [here](https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#ebnf) **How to reproduce** this is a simplified version of the incriminated code ```php $query_builder ->andWhere( $query_builder->expr()->like( "(CASE WHEN user.type = 'type_name' THEN CONCAT(user.surname, ' ', user.name) ELSE CONCAT(user.identifier, ' ', user.other_data) END)", ":second_term" ) ) ->setParameter('second_term', '%' . $value_to_compare . '%') ```

admin commented

2026-01-22 15:36:33 +01:00

@MassimoDeFacciZucchettiSWGiuridico commented on GitHub (Mar 23, 2021):

Small update:
I was able to avoid this problem by writing a query that gives the same results, but is less optimized. I still think this bug should be fixed, since it goes against the expected behaviour defined in the reference page.
If anyone is curious about the query I used, this is a simplified version of it:

$query_builder
  ->andWhere(
    $query_builder->expr()->orX(
      $query_builder->expr()->andX(
        $query_builder->expr()->eq('user.type', ':type_name_param'),
        $query_builder->expr()->like(
          $query_builder->expr()->concat(
            "COALESCE(user.surname, '')",
            $query_builder->expr()->literal(' '),
            "COALESCE(user.name, '')"
          ),
          ':second_term'
        )
      ),
      $query_builder->expr()->andX(
        $query_builder->expr()->neq('user.type', ':type_name_param'),
          $query_builder->expr()->like(
            $query_builder->expr()->concat(
              "COALESCE(user.identifier, '')",
              $query_builder->expr()->literal(' '),
              "COALESCE(user.other_data, '')"
            ),
            ':second_term'
        )
      )
    )
  )
  ->setParameter('type_name_param', 'type_name')
  ->setParameter('second_term', '%' . $value_to_compare . '%')

@MassimoDeFacciZucchettiSWGiuridico commented on GitHub (Mar 23, 2021): **Small update:** I was able to avoid this problem by writing a query that gives the same results, but is less optimized. I still think this bug should be fixed, since it goes against the expected behaviour defined in the reference page. If anyone is curious about the query I used, this is a simplified version of it: ```php $query_builder ->andWhere( $query_builder->expr()->orX( $query_builder->expr()->andX( $query_builder->expr()->eq('user.type', ':type_name_param'), $query_builder->expr()->like( $query_builder->expr()->concat( "COALESCE(user.surname, '')", $query_builder->expr()->literal(' '), "COALESCE(user.name, '')" ), ':second_term' ) ), $query_builder->expr()->andX( $query_builder->expr()->neq('user.type', ':type_name_param'), $query_builder->expr()->like( $query_builder->expr()->concat( "COALESCE(user.identifier, '')", $query_builder->expr()->literal(' '), "COALESCE(user.other_data, '')" ), ':second_term' ) ) ) ) ->setParameter('type_name_param', 'type_name') ->setParameter('second_term', '%' . $value_to_compare . '%') ```

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#6656