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

doctrine, doctrine-orm, many-to-many, php

Solution

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();

Problem

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.

Original source