CODEWITHSUNDEEP
symfonydoctrine-orm
Aymeric
Aymeric

Reputation: 397

Doctrine Many-to-Many find strictly by its relation

I have a classic ManyToMany on my entity. I would like to find Session by its disciplines.

class Session

/**
 * @ORM\ManyToMany(targetEntity=Discipline::class)
 */
private $disciplines;

Example of my database:

Session  Discipline 
51            1
51            2

52            2
52            3
52            4

53            1

If I send $disciplines = [1,2]; I would like the Session 51

If I send $disciplines = [1]; I would like the Session 53

If I send $disciplines = [2,3,4]; I would like the Session 52

I have this query

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

But the request its not strict. How can I improve this?

Upvotes: 2

Views: 204

Answers (2)

Aymeric
Aymeric

Reputation: 397

I found the solution. The only annoying thing is that makes one join per element of the array.

$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);

Upvotes: 1

nikoshr
nikoshr

Reputation: 33334

A solution would be to build a SQL query returning the results you want and then use a Doctrine native SQL query to map the results to your Session objects

First, the raw SQL query, it could look like this

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
)

It probably can be improved, but that does the trick.

Now, we can use this query to build our request

// 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 \Doctrine\ORM\Query\ResultSetMappingBuilder($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();

Upvotes: 0

Related Questions