Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1721

LIKE clausule should accept functions on the pattern

    Details

    • Type: Improvement Improvement
    • Status: Awaiting Feedback
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.1.6
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All

      Description

      Example:
      SELECT .... WHERE upper(n.title) LIKE upper(:filter)

      should be a valid SQL, now is rejected because the walker only accept a variable or an string expression.

      I'm adding a patch to address this.

      1. Parser.patch
        0.8 kB
        Ignacio Larranaga
      2. SqlWalker.patch
        0.9 kB
        Ignacio Larranaga

        Activity

        Ignacio Larranaga created issue -
        Hide
        Ignacio Larranaga added a comment -

        Sorry the Parser has to be modified also to allow expressions to be recognized, I'm attaching the necessary patch.

        Show
        Ignacio Larranaga added a comment - Sorry the Parser has to be modified also to allow expressions to be recognized, I'm attaching the necessary patch.
        Ignacio Larranaga made changes -
        Field Original Value New Value
        Attachment Parser.patch [ 11172 ]
        Hide
        Benjamin Eberlei added a comment -

        I am sure there is a reason why the walker doesn't accept this such as not all supported vendors allowing functions in right hand side LIKE expressions, but i am not sure about this.

        Show
        Benjamin Eberlei added a comment - I am sure there is a reason why the walker doesn't accept this such as not all supported vendors allowing functions in right hand side LIKE expressions, but i am not sure about this.
        Benjamin Eberlei made changes -
        Workflow jira [ 13548 ] jira-feedback [ 14034 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 14034 ] jira-feedback2 [ 15898 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 15898 ] jira-feedback3 [ 18153 ]
        Hide
        Glen Ainscow added a comment -

        This is not possible either:

        WHERE CASE WHEN p.name IS NULL THEN u.username ELSE p.name END LIKE :name

        Show
        Glen Ainscow added a comment - This is not possible either: WHERE CASE WHEN p.name IS NULL THEN u.username ELSE p.name END LIKE :name
        Hide
        Thomas Mayer added a comment -

        In my case it worked when using "=" instead of "LIKE".

        //works:
        (CASE WHEN (Book.id = BookFrom.id) THEN BookTo.displayName ELSE BookFrom.displayName END) = :name

        //[Syntax Error] line 0, col 1217: Error: Expected =, <, <=, <>, >, >=, !=, got 'LIKE'
        (CASE WHEN (Book.id = BookFrom.id) THEN BookTo.displayName ELSE BookFrom.displayName END) LIKE :name

        So the LIKE operator only needs to be allowed here.

        I'm wondering which vendor should not be able to handle that:
        The CASE WHEN ... THEN ... END is documented in DQL, and allowed.
        LIKE itself is allowed.
        If an RDBMs cannot use CASE WHEN and LIKE in combination, this would be a strange limitation.

        Show
        Thomas Mayer added a comment - In my case it worked when using "=" instead of "LIKE". //works: (CASE WHEN (Book.id = BookFrom.id) THEN BookTo.displayName ELSE BookFrom.displayName END) = :name // [Syntax Error] line 0, col 1217: Error: Expected =, <, <=, <>, >, >=, !=, got 'LIKE' (CASE WHEN (Book.id = BookFrom.id) THEN BookTo.displayName ELSE BookFrom.displayName END) LIKE :name So the LIKE operator only needs to be allowed here. I'm wondering which vendor should not be able to handle that: The CASE WHEN ... THEN ... END is documented in DQL, and allowed. LIKE itself is allowed. If an RDBMs cannot use CASE WHEN and LIKE in combination, this would be a strange limitation.
        Hide
        Martin Keckeis added a comment -

        Having the same problem here.

        LIKE + CASE is often used in my application at the WHERE part.
        (e.g. data filtering of a datagrid column)

        Show
        Martin Keckeis added a comment - Having the same problem here. LIKE + CASE is often used in my application at the WHERE part. (e.g. data filtering of a datagrid column)
        Martin Keckeis made changes -
        Status Open [ 1 ] Awaiting Feedback [ 10000 ]
        Martin Keckeis made changes -
        Labels querybuilder, sql-walker

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-1721, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Ignacio Larranaga
          • Votes:
            2 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated: