Doctrine2 Native Query ManyToMany

doctrine-orm, mysql, php, symfony

Solution

You just have to manually add the join table into the sql. Extending the example in chpt 14 of the Doctrine manual:

$rsm = new ResultSetMapping;
$rsm->addEntityResult('User', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'name', 'name');
$rsm->addJoinedEntityResult('Address' , 'a', 'u', 'address');
$rsm->addFieldResult('a', 'address_id', 'id');
$rsm->addFieldResult('a', 'street', 'street');
$rsm->addFieldResult('a', 'city', 'city');

$sql = 'SELECT u.id, u.name, a.id AS address_id, a.street, a.city FROM users u ' .
       'INNER JOIN address__user j ON u.id = j.user '.
       'INNER JOIN address a ON a.id = j.address '.
       'WHERE u.name = ?';
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(1, 'romanb');

$users = $query->getResult();

This example assumes your join table has fields address and user for its keys.

Problem

I've read the Doctrine2 documentation about native sql queries. It doesn't talk about entities joined by a ManyToMany relation. I didn't figure how to handle that, any idea or example.

Original source