Cannot retrieve a user's categories using multiple joins in doctrine2 symfony2

Question

I have created a database and a bundle that manages users, guests, categories:

A user can have many guests
A guest can belong to many categories
Many guests (of a user) can belong to a category
A user can have many categories

I have set the relationships as below:

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

Guest entity

class Guest
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
    * @ORM\ManyToOne(targetEntity="Acme\SomethingBundle\Entity\User", inversedBy="guests")
    * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
    */
    protected $user;

    /**
    * @ORM\ManyToMany(targetEntity="Category", inversedBy="guests")
    * @ORM\JoinTable(name="guests_categories")
    */
    protected $categories;

User entity

class User implements UserInterface
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
    * @ORM\OneToMany(targetEntity="Acme\SomethingBundle\Entity\Guest", mappedBy="user")
    */
    protected $guests;

    /**
    * @ORM\OneToMany(targetEntity="Acme\SomethingBundle\Entity\Category", mappedBy="user")
    */
    protected $categories;

Category entity

class Category
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
    * @ORM\ManyToOne(targetEntity="Acme\SomethingBundle\Entity\User", inversedBy="categories")
    * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
    */
    protected $user;

    /**
    * @ORM\ManyToMany(targetEntity="Guest", mappedBy="categories")
    */
    protected $guests;

The user must be able to view ONLY his guests and categories - they are not shared or something. I have successfully retrieved a list of a user's guests and their categories with a DQL query.

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 Acme\SomethingBundle\Entity\Guest 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
        ));
    }

and a twig interface:

{% 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 %}

Where is the problem ? When I am trying to edit a guest entry (a form in twig), all the categories are listed in a multiple-select box, even those that haven't created by the current logged in user. I can't manage to get the user's specific categories :

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()        
            ));
    }

and a twig template:

{% 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 %}

Some more information: Symfony profiler shows many queries:

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: { }

Any help will be greatly appreciated.

Cyprian Cyprian · Accepted Answer · 2012-08-20T09:58:21

Probably you should define in more specific way which categories will be passed to your GuestType form.

You can do that by passing option 'query_builder' to your form's builder. Eg. in GuestType will be something like that:

$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(...);

Look here: http://symfony.com/doc/current/reference/forms/types/entity.html#query-builder

Cannot retrieve a user's categories using multiple joins in doctrine2 symfony2

1 Answers