学说多对多根据其关系严格查找

Question

我的实体上有一个经典的manymany。我想通过它的学科找到Session。

class Session
/**
* @ORMManyToMany(targetEntity=Discipline::class)
*/
private $disciplines;

数据库示例:

Session  Discipline 
51            1
51            2
52            2
52            3
52            4
53            1

如果我发送$disciplines = [1,2];我想要会话51

如果我发送$disciplines = [1];我想要会话53

如果我发送$disciplines = [2,3,4];我想要会话52

我有这个查询

$queryBuilder
->innerJoin('s.disciplines', 'dis')
->andWhere($queryBuilder->expr()->in('dis.id', ':disciplines'))
->setParameter(':disciplines', $disciplines, DoctrineDBALConnection::PARAM_INT_ARRAY)
;

但是这个要求并不严格。我该如何改进呢?

Answer 1

我找到了解决方案。唯一令人讨厌的是，数组的每个元素都有一个连接。

$values = [2,3,4];
$andX = $queryBuilder->expr()->andX();
foreach ($values as $k => $value) {
$andX->add($queryBuilder->expr()->eq("d$k.id", ':param' . $k));
$queryBuilder->setParameter(':param' . $k, $value);
$queryBuilder->innerJoin($alias . '.disciplines', "d$k");
}
$queryBuilder->andWhere($andX);

Answer 2

一个解决方案是建立一个SQL查询返回你想要的结果，然后使用Doctrine本地SQL查询将结果映射到你的会话对象

首先，原始SQL查询，它看起来像这样

SELECT s.*
FROM session s
WHERE id IN (
-- must match all required disciplines
SELECT session_id FROM session_discipline
WHERE discipline_id IN (:disciplines)
GROUP BY session_id
HAVING COUNT(discipline_id)=:nb
)
AND id IN (
-- must have the required number of rows
SELECT session_id FROM session_discipline
GROUP BY session_id
HAVING COUNT(discipline_id)=:nb
)

它可能还可以改进，但这就够了。

现在，我们可以使用这个查询来构建我们的请求

// assuming this runs in a repository. Change at will to get your entity manager
$em = $this->getEntityManager(); 
$sql = "-- the raw SQL above --"; // don't forget to set the correct query
// 
$rsm = new DoctrineORMQueryResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata(Session::class, 's');
$query = $em->createNativeQuery($sql, $rsm);
// $disciplines is an array of ids as shown in the question
$query
->setParameter('disciplines', $disciplines)
->setParameter('nb', count($disciplines))
;
$sessions = $query->getResult();