mysql - Doctrine2 按多对多关系过滤结果

Question

我正在构建搜索。以下是用于此目的的方法:

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/>";
    }
}

用户实体与实体AdditionalServices 具有Many2Many 关系。我需要过滤掉用户，如果他们提供附加服务，例如宽度 id 26 AND 27

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

如果我们只需要一项服务，但这项服务没有产生预期的结果，那么它是有效的:

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

那么我该怎么做呢？如果能够仅提供带有附加服务 ID 的数组，并且 Doctrine 会解决所有其他问题，那就太好了。但我会感激任何可行的解决方案。

Answer 1

Doctrine DQL 提供了一个 MEMBER OF 规则，您应该将其用于此目的。所以代替:

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

这样写就足够了:

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

正如您所看到的，您甚至不必加入表，除非您确实还想获取附加服务。