Details
-
Type:
Improvement
-
Status:
Open
-
Priority:
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.
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.