DDC-3652: Problem with joins between entities without associations #4487

New Issue

admin · 2026-01-22T14:42:42+01:00

admin commented

2026-01-22 14:42:42 +01:00

Originally created by @doctrinebot on GitHub (Apr 2, 2015).

Originally assigned to: @beberlei on GitHub.

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

Originally created by @doctrinebot on GitHub (Apr 2, 2015). Originally assigned to: @beberlei on GitHub. 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

admin added the Bug label 2026-01-22 14:42:42 +01:00

admin commented

2026-01-22 14:42:43 +01:00

@doctrinebot commented on GitHub (Apr 2, 2015):

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

@doctrinebot commented on GitHub (Apr 2, 2015): - relates to [DDC-3410: Allow Query Builder to specify the joins of Join Inheritance entities](http://www.doctrine-project.org/jira/browse/DDC-3410)

admin commented

2026-01-22 14:42:43 +01:00

@doctrinebot commented on GitHub (Apr 6, 2015):

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

@doctrinebot commented on GitHub (Apr 6, 2015): 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

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#4487