Uploaded image for project: 'Doctrine 2 - ORM'
  1. Doctrine 2 - ORM
  2. DDC-1754

Allow use of Foregin Keys in DQL LIKE condition


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


      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.



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


            • Created: