Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1754

Allow use of Foregin Keys in DQL LIKE condition

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.2.1
    • Fix Version/s: None
    • Component/s: DQL
    • Labels:
      None
    • Environment:
      Php 5.3

      Description

      When using a LIKE condition in a WHERE clause, I would like to be able to use the foreign key of a single value association. For example:

      Imagine Product and Department are both entities. Department has the field Name with a unique index. Product is has a ManyToOne association called Department with the Department entity, referencing the field Name.

      I would like to write:
      SELECT FROM Product p WHERE p.Department LIKE '% Tools'

      However, at present I need to write:
      SELECT FROM Product p
      JOIN p.Department d
      WHERE d.name LIKE '% Tools'

      The issue is one of performance. On large record sets the first query runs several magnatudes more quickly than the second, particularly when four or five joins are involved.

      Looking at the DQL grammar from the online docs, the relveant lines are:

      LikeExpression ::= StringExpression ["NOT"] "LIKE" string ["ESCAPE" char]
      StringExpression ::= StringPrimary | "(" Subselect ")"
      StringPrimary ::= StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression

      The problem is the StateFieldPathExpression in StringPrimary. When used with a LikeExpression a SingleValuedAssociationField should be allowed also.

        Activity

        Tim Roediger created issue -
        Hide
        Benjamin Eberlei added a comment -

        This would only work if Department#Name is the primary key. Is it?

        DQL is not about optimized performance in very single edge case. We need to keep some parts open to keep the code simple. This is at best a feature request, not a bug.

        Show
        Benjamin Eberlei added a comment - This would only work if Department#Name is the primary key. Is it? DQL is not about optimized performance in very single edge case. We need to keep some parts open to keep the code simple. This is at best a feature request, not a bug.
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Issue Type Bug [ 1 ] Improvement [ 4 ]
        Hide
        Tim Roediger added a comment -

        Thanks for your reply Benjamin,

        I agree with your assesment that this is more accurately an improvement request than a bug. My appologies it was placed in the wrong category.

        No, Department#Name is not the primary key, but it does have a unique index.

        I've worked a little more to understand the DQL grammar. It appears that only one small, simple change is required:

        FROM:
        StringPrimary ::= StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression

        TO:
        StringPrimary ::= SingleValuedPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression

        This would improve consistancy with other aspects of DQL. For example, AggregateExpression COUNT, NullComparisonExpression , GroupByItem, and ArithmeticPrimary all allow a SingleValuedPathExpression rather than the more strict StateFieldPathExpression.

        Bascially my frustration is that as DQL currently stands, foregin keys which are already existant in a db table cannot be used in a LIKE expression without doing an unnessessary JOIN. Foreign keys can already be used in DQL for BETWEEN, IS NULL and comparison expressions, so why not LIKE expressions also? It appears the only thing holding this back is an unrequired restriction in the DQL grammar.

        Cheers, Tim

        Show
        Tim Roediger added a comment - Thanks for your reply Benjamin, I agree with your assesment that this is more accurately an improvement request than a bug. My appologies it was placed in the wrong category. No, Department#Name is not the primary key, but it does have a unique index. I've worked a little more to understand the DQL grammar. It appears that only one small, simple change is required: FROM: StringPrimary ::= StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression TO: StringPrimary ::= SingleValuedPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression This would improve consistancy with other aspects of DQL. For example, AggregateExpression COUNT, NullComparisonExpression , GroupByItem, and ArithmeticPrimary all allow a SingleValuedPathExpression rather than the more strict StateFieldPathExpression. Bascially my frustration is that as DQL currently stands, foregin keys which are already existant in a db table cannot be used in a LIKE expression without doing an unnessessary JOIN. Foreign keys can already be used in DQL for BETWEEN, IS NULL and comparison expressions, so why not LIKE expressions also? It appears the only thing holding this back is an unrequired restriction in the DQL grammar. Cheers, Tim
        Benjamin Eberlei made changes -
        Workflow jira [ 13593 ] jira-feedback [ 14043 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 14043 ] jira-feedback2 [ 15907 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 15907 ] jira-feedback3 [ 18162 ]

        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-1754, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Tim Roediger
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: