Symfony Createquery With Union

I need to execute the next query with symfony : $qb = $this->_em; $query = $qb->createQuery( 'SELECT f1.friend1 FROM AppBundle:Friend f1 WHERE f1.friend2 = ?1

Solution 1:

UNION is not supported in doctrine instead you can use Doctrine\ORM\Query\ResultSetMapping; this will map your resultset to the entity which you defined to use by the native query like $rsm->addEntityResult('Namespace\yourBundle\Entity\Friend', 'f');

$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addEntityResult('Namespace\yourBundle\Entity\Friend', 'f');
$rsm->addFieldResult('f', 'friend', 'friend1');    
$sql = "SELECT f1.friend1 AS friend
    FROM friend_table f1
    WHERE f1.friend2 = ?
    UNION
    SELECT f2.friend2  AS friend
    FROM friend_table f2
    WHERE f2.friend1 = ?";
$result = $DM->createNativeQuery($sql, $rsm)
->setParameter(1, $user_id)
->setParameter(2,$user_id)
->getResult();

Edit from comments

---

Not advisable solution due to lack of information why using union but the answer for question in comments below you can do so to map fields,but remember this will give you the duplicates

$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addEntityResult('Project\MyBundle\Entity\Friend', 'f');
$rsm->addFieldResult('f', 'friend1', 'friend1');
$rsm->addFieldResult('f', 'friend2', 'friend2');
$rsm->addFieldResult('f', 'id', 'id');
$rsm->addFieldResult('f', 'state', 'state');
$sql = "SELECT f1.id,f1.friend1 AS friend,f1.friend1,f1.friend2 ,f1.state
    FROM friend f1
    WHERE f1.friend2 = ?
    UNION
    SELECT f2.id,f2.friend2  AS friend,f2.friend1,f2.friend2 ,f2.state
    FROM friend f2
    WHERE f2.friend1 = ?";
$result = $this->_em->createNativeQuery($sql, $rsm)
->setParameter(1, $user_id)
->setParameter(2, $user_id)
->getResult();

Edit 2i just want an array with one field "friend", with friend1 in one case, and friend2 in another

---

For the above you asked you can run two queries using your entity

$DM = $this->getDoctrine()->getEntityManager();
$result1=$DM->getRepository('Namespace\yourBundle\Entity\Friend')
->findBy(array('friend2'=>$user_id));
$result2=$DM->getRepository('Namespace\yourBundle\Entity\Friend')
->findBy(array('friend1'=>$user_id));