Doctrine2 Native query ManyToMany

Question

I read the Doctrine2 documentation about native SQL queries. It does not talk about objects connected by the ManyToMany relation. I did not understand how to handle this, any idea or example.

Answer 1

You just need to manually add the join table in sql. Extension of the example in chapter 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 that your connection table has a field address and a user for its keys.