Combination of ManyToMany relation and LEFT JOIN "WITH" produces extra rows #5420

New Issue

admin · 2026-01-22T15:07:16+01:00

admin commented

2026-01-22 15:07:16 +01:00

Originally created by @Erikvv on GitHub (Feb 17, 2017).

$qb->innerJoin('person.activities', 'activity', Join::WITH, 'activity.id = 3');

produces the following SQL:

LEFT JOIN person_activity o8_ ON o1_.id = o8_.person_id 
LEFT JOIN activity a0_ ON a0_.id = o8_.activity_id AND (a0_.id = 3)

The first left join leads to extra rows due to activities which do not match the WITH clause.

Note that it works correctly when using INNER JOIN or a OneToMany relationship.

Maybe this can be solved by moving the condition from the second to the first LEFT JOIN like so:

LEFT JOIN person_activity o8_ ON o1_.id = o8_.person_id 
    AND (o8_.activity_id IS NULL OR o8_.activity_id IN (
        SELECT id FROM activity WHERE activity.id = 3)
    )
LEFT JOIN activity a0_ ON a0_.id = o8_.activity_id

Originally created by @Erikvv on GitHub (Feb 17, 2017). $qb->innerJoin('person.activities', 'activity', Join::WITH, 'activity.id = 3'); produces the following SQL: LEFT JOIN person_activity o8_ ON o1_.id = o8_.person_id LEFT JOIN activity a0_ ON a0_.id = o8_.activity_id AND (a0_.id = 3) The first left join leads to extra rows due to activities which do not match the WITH clause. Note that it works correctly when using INNER JOIN or a OneToMany relationship. Maybe this can be solved by moving the condition from the second to the first LEFT JOIN like so: LEFT JOIN person_activity o8_ ON o1_.id = o8_.person_id AND (o8_.activity_id IS NULL OR o8_.activity_id IN ( SELECT id FROM activity WHERE activity.id = 3) ) LEFT JOIN activity a0_ ON a0_.id = o8_.activity_id

admin commented

2026-01-22 15:07:18 +01:00

@lcobucci commented on GitHub (Feb 20, 2017):

@Erikvv I'm not sure but I think you can use a MEMBER OF instead of a join to achieve what you want and the ORM will generate the proper query (e.g.: $qb->andWhere(':activity MEMBER OF person. activities')->setParameter('activity', 3);).

@lcobucci commented on GitHub (Feb 20, 2017): @Erikvv I'm not sure but I think you can use a `MEMBER OF` instead of a join to achieve what you want and the ORM will generate the proper query (e.g.: `$qb->andWhere(':activity MEMBER OF person. activities')->setParameter('activity', 3);`).

admin commented

2026-01-22 15:07:18 +01:00

@Erikvv commented on GitHub (Feb 21, 2017):

It would not be the same behavior. If you left join to one activity with id 2 and then filter only id 3 you lose the person record entirely.

(it's not that I can't think of a solution it's just that the current behavior is unintuitive)

@Erikvv commented on GitHub (Feb 21, 2017): It would not be the same behavior. If you left join to one activity with id 2 and then filter only id 3 you lose the person record entirely. (it's not that I can't think of a solution it's just that the current behavior is unintuitive)

admin commented

2026-01-22 15:07:18 +01:00

@lcobucci commented on GitHub (Feb 21, 2017):

@Erikvv sure, that should behave the same way as the inner join but without requiring the join.

We do need a testing case though, could you please create something to reproduce that unexpected behaviour (something like e4704beaf9/tests/Doctrine/Tests/ORM/Functional/Ticket/GH5562Test.php)?

@lcobucci commented on GitHub (Feb 21, 2017): @Erikvv sure, that should behave the same way as the `inner join` but without requiring the join. We do need a testing case though, could you please create something to reproduce that unexpected behaviour (something like https://github.com/doctrine/doctrine2/blob/e4704beaf9fad5a521fe7592dec382ae209b3cc1/tests/Doctrine/Tests/ORM/Functional/Ticket/GH5562Test.php)?

admin commented

2026-01-22 15:07:19 +01:00

@Erikvv commented on GitHub (Feb 22, 2017):

I'll write a test case, but I'm not sure I can make a patch, I am not familiar with parsers.

@Erikvv commented on GitHub (Feb 22, 2017): I'll write a test case, but I'm not sure I can make a patch, I am not familiar with parsers.

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#5420