I have created a database and a bundle that manages users, guests, 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.
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