CODEWITHSUNDEEP
phpsymfonydoctrine-orm
ikleiman
ikleiman

Reputation: 545

Doctrine 2 DQL - Select by exact ManyToMany array

I have ClassA with a ManyToMany relationship to ClassB, is there a way to Select ClassA entities by ClassB array? Currently I have:

$query = $em->createQuery("
    SELECT a FROM Bundle:ClassA a 
    JOIN a.classB b 
    WHERE b IN (:classBEntities)
    ")
    ->setParameter('classBEntities', $classBEntities);

Being $classBEntities an array of ClassB entities. The problem with that query is that if I'm looking the ClassA entity that have ClassB entities 1, 2 and 3 it returns any ClassA entity that have one of those 3 ClassB entity as well as the one that have the three of them, but I need to select just the ones that have all the entities that the array contains.

Upvotes: 2

Views: 1678

Answers (2)

ikleiman
ikleiman

Reputation: 545

After some trial and error using nifr answer I got the solution, here it is if someone ran into the same problem:

$queryBuilder = $em->getRepository('Bundle:ClassA')
                    ->createQueryBuilder('a')
                    ->join('a.classB', 'b');
                    ->groupBy('a.id')
                    ->having('COUNT(b) = :cB')
                    ->setParameter('cB', count($classBEntities));

           foreach ($classBEntities as $entity) 
           {
               $id = $entity->getId();
               $queryBuilder
                ->join('a.classB', 'b'.$id)
                ->andWhere('b'.$id.'.id IN (:b_entity'.$id.')')
                ->setParameter('b_entity'.$id, array($id));
           }

$result = $queryBuilder->getQuery()->getResult();

Upvotes: 1

Nicolai Fröhlich
Nicolai Fröhlich

Reputation: 52473

$queryBuilder = $em
   ->getRepository('Bundle:ClassA')
   ->createQueryBuilder('a')
   ->leftJoin('a.classB', 'b')
;

foreach ($classBentities as $entity) {
    $queryBuilder
        ->andWhere('b IN (:b_entity)')
        ->setParameter('b_entity', array($entity))
    ;
}

$queryBuilder
   ->add('where', $queryBuilder->expr()->count('b'), '=' , ':count_b')
   ->setParameter('count_b', count($classBentities))

$result = $queryBuilder->getQuery()->getResult();

You will need Doctrine >= 2.1 for this to work.

Upvotes: 3

Related Questions