问题描述
我需要用 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));
这篇关于Symfony createquery 与联合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!