Symfony createquery 与联合

本文介绍了Symfony createquery 与联合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要用 symfony 执行下一个查询:

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 UNION SELECT f2.friend2 FROM AppBundle:Friend f2 WHERE f2.friend1 = ?2 ' )->setParameters(array(1 => $user_id, 2 => $user_id));

当我执行这个 $query 时，我有这个错误:预期的字符串结尾，得到 'UNION'.

When i execute this $query, i have this error : Expected end of string, got 'UNION'.

我该怎么办?

编辑我的新代码:

$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.friend1 AS friend FROM friend f1 WHERE f1.friend2 = ? UNION SELECT f2.friend2 AS friend FROM friend f2 WHERE f2.friend1 = ?"; $result = $this->_em->createNativeQuery($sql, $rsm) ->setParameter(1, $user_id) ->setParameter(2, $user_id) ->getResult(); return $result;

这是我的新查询.我直接在 phpmyadmin 上测试了这个查询，我得到了回报.但是使用此代码，我在 $result 中有空

This is my new query. I have test this query directly on phpmyadmin, and i have a return. But with this code, i have empty in $result

推荐答案

UNION 在学说中不受支持，您可以使用 Doctrine\ORM\Query\ResultSetMapping; 这会将您的结果集映射到您定义的实体由本机查询使用，如 $rsm->addEntityResult('Namespace\yourBundle\Entity\Friend', 'f');

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

编辑 2 我只想要一个包含一个字段朋友"的数组，一个是朋友1，另一个是朋友2

对于上述问题，您可以使用实体运行两个查询

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