Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
MassimoDeFacciZucchettiSWGiuridico opened this issue Mar 22, 2021 · 1 comment

Comments

@MassimoDeFacciZucchettiSWGiuridico
Copy link

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 . '%')
@MassimoDeFacciZucchettiSWGiuridico
Copy link
Author

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 . '%')

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant