Doctrine2 complex multiple many-to-many search query

Question

I am creating an application that has an Entity that has multiple many-to-many relationships with other objects. Let me layout the Entities:

• Entry = the main object
• Region = has many-to-many with Entry via "_entry_region" reference table
• Type = has many-to-many with Entry via "_entry_type" reference table
• Tag = has many-to-many with Entry via "_entry_tag" reference table

Now in the frontend the user can setup some filters for the entries that need to be loaded. In human language the query needs to be like this.

Get the entries WHERE  region is (1 or 2 or 3) AND type is ( 3 or 4 or 5 ) AND tag is ( 4 OR 6 or 1)

i currently strugle with this piece of code:

$query = $this->em->createQuery('SELECT m.id FROM Entity\Entry e WHERE :region_id MEMBER OF e.regions);
$query->setParameter('region_id',  1);      
$ids = $query->getResult();

This gives me the ID's of the entry's of the corresponding region. but it is not possible to add an array of the region id's in the setParameter(). I also can't find in the docs how to do this on multiple related entities like my human based query:

Get the entries WHERE  region is (1 or 2 or 3) AND type is ( 3 or 4 or 5 ) AND tag is ( 

Answer 1

You might also consider taking a look at the D2 query builder. Bit more verbose but it's much easier to build more complicated queries once you get the syntax down.

Something like:

    $qb = $this->em->createQueryBuilder();

    $qb->addSelect('entry');
    $qb->addSelect('region');
    $qb->addSelect('type');
    $qb->addSelect('tag');

    $qb->from('MyBundle:Entry',   'entry');
    $qb->leftJoin('entry.regions','region');
    $qb->leftJoin('entry.types',  'type');
    $qb->leftJoin('entry.tags',   'tag');


    $qb->andWhere($qb->expr()->in('region.id',$regions));
    $qb->andWhere($qb->expr()->in('type.id',  $types));
    $qb->andWhere($qb->expr()->in('tag.id',   $tags));