将2个查询合并成1个Doctrine DQL查询

Question

我正在使用Symfony 2.8和Doctrine 2.4构建一个交友系统。

我有一个MateRelationship实体：

class MateRelationship
{
    /**
     * @var integer
     *
     * @ORM\Column(type="integer", name="id")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="Acme\UserBundle\Entity\User")
     * @ORM\JoinColumn(name="sender", referencedColumnName="id", nullable=false)
     *
     */
    private $sender;

    /**
     * @ORM\ManyToOne(targetEntity="Acme\UserBundle\Entity\User")
     * @ORM\JoinColumn(name="receiver", referencedColumnName="id", nullable=false)
     *
     */
    private $receiver;

    /**
     * @var \DateTime
     *
     * @ORM\Column(type="datetime", nullable=false)
     */
    private $date;

    /**
     * @var boolean
     *
     * @ORM\Column(type="boolean", nullable=false)
     */
    private $invitationAccepted;

我的目标是编写一个DQL查询来获取给定用户的所有被接受的好友关系（无论该用户是发送方还是接收方）。

我已经用两个查询并合并结果的方式实现了这一目标，但这种方法并不优化且难以实现$limit限制。

public function getMates($user, $limit = 0){

    $query1 = $this->_em->createQuery('SELECT m, u.nickname, u.username FROM AcmeUserBundle:MateRelationship m JOIN m.receiver u WHERE m.invitationAccepted = 1 AND m.sender = :user')
            ->setParameter('user', $user);

    try{
        $result1 = $query1->getResult();
    }
    catch(\Doctrine\ORM\NoResultException $e){
        $result1 = [];
    }

    $query2 = $this->_em->createQuery('SELECT m, u.nickname, u.username FROM AcmeUserBundle:MateRelationship m JOIN m.sender u WHERE m.invitationAccepted = 1 AND m.receiver = :user')
            ->setParameter('user', $user);

    try{
        $result2 = $query2->getResult();
    }
    catch(\Doctrine\ORM\NoResultException $e){
        $result2 = [];
    }

    return array_merge($result1, $result2);
}

我希望能使用一条查询语句，并且可以使用 ->setMaxResults($limit)，但我不知道如何将这两个查询合并成一个。 谢谢你的帮助 :)

- Kaz

Answer 1

这里有一个使用Doctrine查询构建器的提案。

public function getMates($user, $limit = 0)
{
    // Create query builder
    $queryBuilder = $this->_em->getRepository("AcmeUserBundle:MateRelationship")->createQueryBuilder('m');

    // Create 'or' expression
    $or = $queryBuilder->expr()->orX();
    $or
        ->add('m.sender = :user')
        ->add('m.receiver = :user');

    // Create the query
    $queryBuilder
        ->andWhere('m.invitationAccepted = 1')
        ->andWhere($or)
        ->setMaxResults($limit)
        ->setParameter('user', $user);

    // Return the result
    return $queryBuilder->getQuery()->getResult();
}

- lolmx

Answer 2

您可以使用LEFT JOIN和CASE来获取有效的用户信息：

    public function getMates($user, $limit = 0)
    {
        $dql = <<<DQL
            SELECT 
               m, 
               CASE usen.nickname IS NULL WHEN TRUE THEN urec.nickname ELSE usen.nickname END AS nickname, 
               CASE usen.username IS NULL WHEN TRUE THEN urec.username ELSE usen.username END AS username, 
               CASE usen.nickname IS NULL WHEN TRUE THEN 'sender' ELSE 'receiver' END AS relationship
            FROM AcmeUserBundle:MateRelationship m 
            LEFT JOIN m.sender usen 
            LEFT JOIN m.receiver urec 
            WHERE m.invitationAccepted = 1 AND (m.receiver = :user OR m.sender= :user)
DQL;

        return $this->_em->createQuery($dql)
            ->setParameter('user', $user)
            ->setMaxResults($limit)
            ->getResult();
    }

如果$result为空，则它已经是一个空数组[]。

注意：只有在调用getOneOrNullResult、getSingleResult或getSingleScalarResult方法时才会抛出NoResultException异常。

- yceruto

---

我已经更新了答案，使用CASE语句的别名，提取数据会更容易一些。 - yceruto

看起来很不错，但是Doctrine抛出了一个异常"Doctrine\ORM\Query\QueryException" "[Syntax Error] line 0, col 32: Error: Unexpected 'NULL'"。这可能与此问题有关吗？ - Kaz

Answer 3

当你匹配:user时，可以通过使用OR来简单实现。这可以是receiver或sender。

SELECT m, usen.nickname, usen.username, urec.nickname, urec.username
FROM AcmeUserBundle:MateRelationship m 
JOIN m.sender usen 
JOIN m.receiver urec 
WHERE m.invitationAccepted = 1 AND 
(m.receiver = :user OR m.sender= :user)

- Alok Patel

---

你需要将 u. 改为 usen. 或 urec 别名。 - yceruto

然而这并不是那么简单，我认为。当使用 usen.nickname 和 m.sender <> :user 时会发生什么？ - yceruto

在这种情况下，您需要使用LEFT JOIN而不是JOIN，否则sender和receiver都将等于:user才能被选中。 - yceruto

但是你怎么知道哪一个是朋友，哪一个是请求的用户呢？ - Kaz

你可以添加另一个 CASE 来确定选择了哪个用户关系，请参见我上面的更新。 - yceruto

它应该可行，你的动机是通过任何方式找到用户的朋友。因此，这个查询应该可行。用户是你要比较的对象 :user - Alok Patel