Doctrine 2 - Outer join query

Question

In the context of the SonataAdminBundle / SonataUserBundle, I'm using the query builder to add static filters to the "list" query :

With this query, I get only users in the group "Juge", the query works well :

$query
  ->leftJoin( $query->getRootAlias().'.groups', 'g')
  ->andWhere( 'g.name = :group_name' )
  ->setParameter('group_name', 'Juge');

In an other Admin class, i want to do the oposite of this query : get the users who ARE NOT in the "Juge" group. How can I perform this? There is not outerJoin function in doctrine 2 right?

score 2 · Answer 1 · edited Oct 26 '16 at 11:06

2

I think you want to do

$query
  ->leftJoin( $query->getRootAlias().'.groups', 'g',
    Expr\Join::WITH, 'g.name = :group_name')
  ->where('g.name IS NULL')
  ->setParameter('group_name', 'Juge');

where Expr is Doctrine\ORM\Query\Expr.

edited Oct 26 '16 at 11:06

IluTov

6,807
6
41
103

answered Aug 03 '13 at 13:57

dMaggot

81
1
6

This doesnt work for users who are in the Juge group and at least another group. Still looking for the correct answer. – viarnes Mar 10 '15 at 05:21

score 1 · Answer 2 · answered Feb 17 '14 at 01:12

I used the following code and it works for me, its a kind of Outer Join.

$qb = $this->getEntityManager()->createQueryBuilder()
     ->select('u')
     ->from('UserBundle:User', 'u')
     ->leftJoin('u.group g WITH g.id = :groupId', false)
     ->where('g IS NULL')
     ->groupBy('u.id')
     ->setParameter('groupId', 12) 
return $qb->getQuery()->getResult();

Doctrine 2 - Outer join query

2 Answers2

Linked