IN clause equivalent for array property

Question

Actually I have two entities A an B, mapped by a Many-To-Many relationship. Doctrine represents this relation with both A.bElements and B.aElements attributes which are accessible with DQL, so I would like to use these attributes in my query, because this query includes other where clauses, and doing all the job in the same query looks fine.

I have a subquery which returns some B elements, and I would like to check if there is at least one of these elements present in A.bElements. The problem is the IN clause works only for one value, not an array.

Here is an example of my query (this is for a search engine) :

$qb->select('l')
   ->from('AppBundle:Livre', 'l')
   ->where("l.ISBN LIKE :motcle")
   ->orWhere("l.cote LIKE :motcle")
   ->orWhere($qb->expr()->in('l.dessinateurs',
       $em->createQueryBuilder()
          ->select('d.id')
          ->from('AppBundle:Artiste', 'd')
          ->where('d.dessinateur=1')
          ->andWhere('d.nom LIKE :motcle OR d.prenom LIKE :motcle')
          ->setParameter('motcle', '%'.$motcle.'%')
          ->getDql()
   ))
   ->setParameter('motcle', '%'.$motcle.'%');

Here I want all the 'Livre' entities which have at least one of the subquery result in their l.dessinateurs property.

I know it's possible to realize this work by retriving all the B and A elements separately, and then use php to check if there are common elements. It's also possible to use the join table, but I guess Doctrine aims to avoid the use of a such table directly.

Maybe someone could help me ? Maybe the problem is not clear enough, I could try to reformulate it if necessary. Thanks in advance.

EDIT:

The following query works fine :

$qb->select('l')
    ->from('AppBundle:Livre', 'l')
    ->join('l.serie', 's')
    ->join('l.editeur', 'e')
    ->join('l.dessinateurs', 'd', 'WITH', 
        'd.nom LIKE :motcle 
        OR d.prenom LIKE :motcle 
        OR l.ISBN LIKE :motcle 
        OR l.cote LIKE :motcle 
        OR l.etat LIKE :motcle 
        OR l.edition LIKE :motcle
        OR s.libelle LIKE :motcle 
        OR e.nom LIKE :motcle')
    ->join('l.auteurs', 'a', 'WITH', 
        'a.nom LIKE :motcle 
        OR a.prenom LIKE :motcle 
        OR l.ISBN LIKE :motcle 
        OR l.cote LIKE :motcle 
        OR l.etat LIKE :motcle 
        OR l.edition LIKE :motcle
        OR s.libelle LIKE :motcle 
        OR e.nom LIKE :motcle')
    ->setParameter('motcle', '%'.$motcle.'%');

Unfortunately I have to repeat the others LIKE clause in both join, but this is another problem. The ccKep's answer below is perfect.

Answer 1

You could give this a shot:

$qb->select('l')
   ->from('AppBundle:Livre', 'l')
   ->join('l.dessinateurs', 'd', 'WITH', 'd.dessinateur=1 AND (d.nom LIKE :motcle OR d.prenom LIKE :motcle)')
   ->where("l.ISBN LIKE :motcle")
   ->orWhere("l.cote LIKE :motcle")
   ->setParameter('motcle', '%'.$motcle.'%');