Symfony createquery 与联合 [英] Symfony createquery with union

问题描述

我需要用 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 与联合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！