无法使用 doctrine2 symfony2 中的多个联接检索用户的类别

Question

我创建了一个数据库和一个捆绑包，用于管理用户、访客和类别：

• 一个用户可以有很多客人
• 客人可以分为许多类别
• （用户的）许多访客可以属于一个类别
• 一个用户可以有许多类别

我将关系设置如下：

User -> OneToMany -> Guests  
Guests <-> ManyToMany <-> Categories  
User -> OneToMany -> Categories  

客户实体

class Guest
{
    /**
     * @ORMId
     * @ORMColumn(type="integer")
     * @ORMGeneratedValue(strategy="AUTO")
     */
    protected $id;
    /**
    * @ORMManyToOne(targetEntity="AcmeSomethingBundleEntityUser", inversedBy="guests")
    * @ORMJoinColumn(name="user_id", referencedColumnName="id")
    */
    protected $user;
    /**
    * @ORMManyToMany(targetEntity="Category", inversedBy="guests")
    * @ORMJoinTable(name="guests_categories")
    */
    protected $categories;

用户实体

class User implements UserInterface
{
    /**
     * @ORMColumn(type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;
    /**
    * @ORMOneToMany(targetEntity="AcmeSomethingBundleEntityGuest", mappedBy="user")
    */
    protected $guests;
    /**
    * @ORMOneToMany(targetEntity="AcmeSomethingBundleEntityCategory", mappedBy="user")
    */
    protected $categories;

类别实体

class Category
{
    /**
     * @ORMId
     * @ORMColumn(type="integer")
     * @ORMGeneratedValue(strategy="AUTO")
     */
    protected $id;
    /**
    * @ORMManyToOne(targetEntity="AcmeSomethingBundleEntityUser", inversedBy="categories")
    * @ORMJoinColumn(name="user_id", referencedColumnName="id")
    */
    protected $user;
    /**
    * @ORMManyToMany(targetEntity="Guest", mappedBy="categories")
    */
    protected $guests;

用户必须能够只查看他的客人和类别-他们不是共享的或其他什么。我已经使用DQL查询成功地检索到了用户的访客及其类别的列表。

public function indexAction()
    {
        $user = $this->get('security.context')->getToken()->getUser();
        $userId = $user->getId();
        $em = $this->getDoctrine()->getEntityManager();
        $query = $em->createQuery( "SELECT g, u, c
                                    FROM AcmeSomethingBundleEntityGuest g
                                    JOIN g.user u
                                    LEFT JOIN g.categories c
                                    WHERE u.id = :userId
                                    ORDER BY g.surname ASC");
        $query->setParameter('userId', $userId); 
        $entities = $query->getResult();
        return $this->render('AcmeSomethingBundle:Guest:index.html.twig', array(
            'entities' => $entities
        ));
    }

和一个分支接口：

{% block body %}
    {% for entity in entities %}
    <div class="guestBox">
            <div class="guestName" >{{ entity.surname }} {{ entity.name }}</div>
            {% for category in entity.categories %}
            {{ category.name }}
            {% endfor %}
        </div>
    </div>
    {% endfor %}
{% endblock %}

问题出在哪里？当我试图编辑访客条目（树枝中的表单）时，所有类别都会列在多选框中，即使是当前登录用户尚未创建的类别。我无法获取用户的特定类别：

public function editAction($id)
    {
        $user = $this->get('security.context')->getToken()->getUser();
        $userId = $user->getId();
        $em = $this->getDoctrine()->getEntityManager();
        $query = $em->createQuery( "SELECT g, c, u
                                    FROM AcmeSomethingBundle:Guest g
                                    JOIN g.user u
                                    LEFT JOIN g.categories c 
                                    WHERE u.id = :userId
                                    AND g.id = :guestId");
        $query->setParameter('guestId', $id)
              ->setParameter('userId', $userId);
        $entity = $query->getSingleResult();
        if (!$entity) {
            throw $this->createNotFoundException('Wrong guest id');
        }
        $editForm = $this->createForm(new GuestType(), $entity);
        $deleteForm = $this->createDeleteForm($id);
        return $this->render('AcmeSomethingBundle:Guest:edit.html.twig', array(
            'entity'     => $entity,
            'editForm'   => $editForm->createView(),
            'deleteForm' => $deleteForm->createView()        
            ));
    }

和一个小树枝模板：

{% block body %}
<form action="{{ path('guest_update', { 'id': entity.id }) }}" method="post" {{ form_enctype(editForm) }}>
        {{ 'guest.form.categories'|trans }}
        {{ form_errors(editForm.categories) }}
        {{ form_widget(editForm.categories) }}
        <button type="submit" class="btn guestSave">{{ 'guest.form.save'|trans }}</button>
</form>
{% endblock %}

更多信息：Symfony探查器显示了许多查询：

SELECT t0.id AS id1, t0.username AS username2, t0.salt AS salt3, t0.password AS password4, t0.email AS email5, t0.is_active AS is_active6 FROM users t0 WHERE t0.id = ?
Parameters: ['2']
SELECT g0_.id AS id0, g0_.name AS name1, g0_.surname AS surname2, g0_.email AS email3, g0_.address AS address4, g0_.phone AS phone5, g0_.mobile AS mobile6, g0_.description AS description7, g0_.created_at AS created_at8, g0_.updated_at AS updated_at9, g0_.is_activated AS is_activated10, u1_.id AS id11, u1_.username AS username12, u1_.salt AS salt13, u1_.password AS password14, u1_.email AS email15, u1_.is_active AS is_active16, c2_.id AS id17, c2_.name AS name18, c2_.description AS description19, g0_.user_id AS user_id20, c2_.user_id AS user_id21 FROM guest g0_ INNER JOIN users u1_ ON g0_.user_id = u1_.id LEFT JOIN guests_categories g3_ ON g0_.id = g3_.guest_id LEFT JOIN category c2_ ON c2_.id = g3_.category_id WHERE u1_.id = ? AND g0_.id = ?
Parameters: ['2', '33']
SELECT t0.id AS id1, t0.name AS name2, t0.description AS description3, t0.user_id AS user_id4 FROM category t0
Parameters: { }

任何帮助都将不胜感激。

Answer 1

也许您应该以更具体的方式定义哪些类别将传递给GuestType表单。

您可以通过将选项"query_builder"传递给表单的生成器来实现这一点。例如，在GuestType中将是这样的：

$builder->add(...)
        ->add('category', 'entity', array (
            ...
            'label' => 'Some label',
            'required' => false,
            'query_builder' => function(CategoryRepository $repository) use($user) {
                 // should return doctrine's QueryBuilder object to create query which returns categories for specified $user                      
            }))
        ->add(...);

看这里：http://symfony.com/doc/current/reference/forms/types/entity.html#query-生成器