Doctrine 2 leftJoin, with, manytomany relationship, wrong number of rows returned

Question

I have a problem with Doctrine 2 and a left join on a maytomany relationship I'm trying to get working. It's probably my Query builder, but I can't seem to figure it out.

I have a join table like so:

----------------------------------
| user_id    |   attraction_id   |
----------------------------------
| 4              1               |
| 4              2               |
| 4              3               |
----------------------------------

For two models, User and Attraction, where User is the owner of the relationship.

When I perform this query:

$attractions = $CI->em->createQueryBuilder()
        ->select('a', 'u')
        ->from('\ListLovers\Model\Attraction', 'a')
        ->leftJoin('a.users', 'u', \Doctrine\ORM\Query\Expr\Join::WITH, 'u.id = 4')
->getQuery()->getResult();

My users count for the attraction with id of 1 is 1. Great!

Now if I add another row to the join table like so:

----------------------------------
| user_id    |   attraction_id   |
----------------------------------
| 1              1               |
| 4              1               |
| 4              2               |
| 4              3               |
----------------------------------

...and perform the same query, my users count for the attraction with id of 1 is ...ZERO. What the?

Am I missing something?

Thanks, Mark.

Answer 1

The best way for Many-to-Many is MEMBER OF or NOT MEMBER OF.

$attractions = $CI->em->createQueryBuilder()
        ->select('a', 'u')
        ->from('\ListLovers\Model\Attraction', 'a')
        ->where(':uid MEMBER OF a.users')
        ->setParameter('uid', 4)
        ->getQuery()->getResult();