CODEWITHSUNDEEP
mysqlsymfonyormdoctrine-orm
Lukas Klizas
Lukas Klizas

Reputation: 175

Doctrine2 filter results by many to many relationship

I'm building search. Here is method for this purpose:

public function execute()
{
    $this->handleSearchQuery();

    $startDate = new \DateTime($this->date . ' ' . $this->time);

    $timeRequiredInterval = new \DateInterval("PT" . $this->hoursRequired * 60 . "M");

    $endDate = $startDate->add($timeRequiredInterval);

    $date = $startDate->format('Y-m-d');
    $startTime = $startDate->format('H:i');
    $endTime = $endDate->format('H:i');

    $repo = $this->em->getRepository('AppBundle:User');
    $qb = $repo->createQueryBuilder('c');

    $qb->select('c')
        ->innerJoin('c.workingTime', 'wt', Join::WITH, $qb->expr()->andX(
            $qb->expr()->eq('wt.day', ':day'),
            $qb->expr()->lte('wt.workTimeStarts', ':timeStart'),
            $qb->expr()->gte('wt.workTimeEnds', ':timeEnd')
        ))
        ->innerJoin('c.cityDistricts', 'cd', Join::WITH, 'cd.id = :district')
        ->setParameter('timeStart', $startTime)
        ->setParameter('timeEnd', $endTime)
        ->setParameter('day', $date)
        ->setParameter('district', $this->district);

    if ($this->additionalServices) {
        $qb->innerJoin('c.additionalServices', 'se');
        $qb->where('se.id = 26');
    }

    $query = $qb->getQuery();
    $result = $query->getResult();

    foreach ($result as $item) {
        echo $item->getFirstName() . "<br/>";
    }
}

User entity has Many2Many relationship with Entity AdditionalServices. I need to filter out users, if they provide additional services, for example, width id 26 AND 27

if ($this->additionalServices) {
    $qb->innerJoin('c.additionalServices', 'se');
    $qb->where('se.id = 26');
}

It works, if we have only one service required, but this one does not produce expected results:

if ($this->additionalServices) {
    $qb->innerJoin('c.additionalServices', 'se');
    $qb->where('se.id = 26');
    $qb->andWhere('se.id = 27');
}

So how can I do it? It would be nice, if there's possibility to provide only array with AdditionalService ID's, and Doctrine would sort everything else out. But I will appreciate any working solution.

Upvotes: 0

Views: 54

Answers (1)

Kuba Birecki
Kuba Birecki

Reputation: 3016

Doctrine DQL provides a MEMBER OF rule, which you should use for this purpose. So instead of:

$qb->innerJoin('c.additionalServices', 'se');
$qb->where('se.id = 26');
$qb->andWhere('se.id = 27');

It's enough to write:

$qb->where('26 MEMBER OF c.additionalServices');
$qb->andWhere('27 MEMBER OF c.additionalServices');

As you can see, you don't even have to join the tables, unless you actually want to fetch the additional services as well.

Upvotes: 1

Related Questions