DDC-3652: Problem with joins between entities without associations

[doctrinebot]

Jira issue originally created by user evaldez:

I needed to write a DQL query that selects an entity that is not specified in the FROM part. For that I am using joins between entities without associations. But I found a problem. Let me explain it with the following example:

Here the query I wrote

            SELECT l
            FROM Label l
            JOIN l.product_labels pl
            JOIN pl.product p
            JOIN creams.c WITH c.product = p
            WHERE c.type = 'general_cleaners'
            AND l.name = "Eye cleaners"

This is translated to SQL as:

SELECT 
  l0_.id AS id0, 
  l0_.name AS name1, 
FROM 
  label l0_ 
  INNER JOIN product*label p1_ ON l0_.id = p1_.label*id 
  INNER JOIN product p2* ON p1_.product_id = p2*.id 
  INNER JOIN creams c3_ 
  AND (c3*.product = p2*.id)  <---- this should be in the WHERE part!
WHERE 
  c3*.type = 'general*cleaners'
 AND l0_.name "Eye cleaners"

What happens is that the query gets all the products with the label "Eye cleaners" and the creams having that belong to those products. But it never filters out the products that have no cream association.

To go around the problem I added the condition

AND c.product = p.id

in the WHERE clause in the DQL query

After searching for the issue I found something related but no the same. So i decided to put my findings under your consideration.

Thanks a lot

[doctrinebot]

• relates to DDC-3410: Allow Query Builder to specify the joins of Join Inheritance entities

[doctrinebot]

Comment created by @Ocramius:

AND (c3*.product = p2*.id) <---- this should be in the WHERE part!

Why should it go in the WHERE clause? You specified it on a join WITH condition, which in ORM terms translates to an additional ON clause