Symfony-在Doctrine ORM中使用外部联接 [英] Symfony - Using Outer Joins with Doctrine ORM

问题描述

我有三个实体:User，Answer和Question.

在User和Answer之间存在OneToMany关系，在Answer和Question之间存在ManyToOne关系.基本上，特定用户可以选择给出一堆问题的答案.

There is a OneToMany relationship between User and Answer and a ManyToOne relationship between Answer and Question. Basically, a particular user optionally gives answers to a bunch of questions.

在ORM领域中，我想要完成的工作是为特定用户检索所有问题及其相关的答案.关键部分是用户可能尚未回答特定问题，但我仍然想回答该问题(答案为空).

What I'm trying to accomplish in the world of ORM is retrieving ALL questions and their associated answers for a particular user. The key part is that a user may not have answered a particular question but I still want to get that question (with a null answer).

我的Answer实体具有一个用户"字段，该字段映射到User实体，该实体由User实体中的答案"字段反转.如果我在User实体中使用此答案"字段，则只会得到用户已实际回答的问题/答案对.我没有收到用户未回答 的问题.

My Answer entity has a 'user' field which maps to the User entity which is inverted by an 'answers' field within the User entity. If I use this 'answers' field within the User entity, I only get the question/answer pairs for which the user has actually answered. I do not get questions for which the user has not answered.

通常，使用原始SQL，这将在我的问题和答案表之间涉及一个简单的左外部联接"，但是我希望使用Doctrine的ORM来完成.有什么建议?我对ORM领域还很陌生.

Typically, using raw SQL, this would involve a simple "left outer join" between my question and answer table, but I want this to be done using Doctrine's ORM. Any suggestions? I'm pretty new to the world of ORM.

推荐答案

我做到了！方法如下:

我在Question实体中创建了一个字段，其中包含所有用户针对该特定问题的所有答案；其通过OneToMany关系映射到Answer实体.然后确保将列表限制为特定用户的答案，我为我的Question实体创建了一个自定义存储库并创建了以下功能:

I created a field in my Question entity that contains all answers, from all users, for that particular question; its mapped with a OneToMany relationship to the Answer entity. And then to make sure we restrict that list to the answers for a particular user I created a custom repository for my Question entity and created the following function:

public function findAllJoinedToAnswer($user)
{
    $query = $this->getEntityManager()
        ->createQuery('
            SELECT q, a
            FROM Bundle:Question q
            LEFT JOIN q.answers a
            WITH a.user = :user'
         )->setParameter('user', $user);

    try{
        return $query->getResult();
    }catch (\Doctrine\ORM\NoResultException $e) {
        return null;
    }
}

只需传入User实体的实例，瞧！

Just pass in an instance of the User entity, and voila!

这篇关于Symfony-在Doctrine ORM中使用外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！