I have a User entity that has a collection of List Items associated with it.
Each List Item entity also references another entity, Topic. I have setup a UNIQUE constraint on the List Item table that will only allow unique combinations of the User and Topic foreign keys. No List Items with a duplicate reference to the Topic entity are allowed for each user. I am also ordering the results by "completion_week".
There are times when I will be attempting to persist a form collection and it will fail with an integrity constraint violation. For some reason Symfony seems to think updates are being made to the form and is incorrectly attempting to update collection items - but is switching the a foreign key on some of the updated entities seemingly randomly - which is causing the error because of the above mentioned constraints.
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '6-1' for key 'list_item_user_topic'
The User Entity:
<?php
/**
* @ORM\Entity(repositoryClass="App\MyBundle\Repository\UserRepository")
* @ORM\Table(name="users")
* @Gedmo\SoftDeleteable(fieldName="deleted_at", timeAware=true)
*/
class User implements UserInterface, EquatableInterface
{
/**
* @ORM\Id
* @ORM\Column(type="integer")
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @ORM\Column(type="string", length=30, nullable=false)
*/
private $first_name;
/**
* @ORM\Column(type="string", length=30, nullable=false)
*/
private $last_name;
/**
* @ORM\Column(type="string", unique=true, length=100, nullable=true)
*/
private $email;
/**
* @ORM\OneToMany(
* targetEntity="App\MyBundle\Entity\ListItem",
* mappedBy="user",
* orphanRemoval=true,
* fetch="EAGER",
* cascade={"all"}
* )
* @ORM\OrderBy({"completion_week"="ASC"})
*
*/
private $listItems;
...
The List Item Entity:
<?php
/**
* @ORM\Entity(repositoryClass="App\MyBundle\Repository\ListItemRepository")
* @ORM\Table(
* name="list_items",
* uniqueConstraints={@ORM\UniqueConstraint(name="list_item_user_topic", columns={"user_id","topic_id"})}
* )
*
* @Gedmo\SoftDeleteable(fieldName="deleted_at", timeAware=true)
*/
class ListItem
{
/**
* @ORM\Id
* @ORM\Column(type="integer")
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @ORM\ManyToOne(targetEntity="App\MyBundle\Entity\User", inversedBy="listItems", fetch="EAGER")
* @ORM\JoinColumn(name="user_id", referencedColumnName="id")
*/
private $user;
/**
* @ORM\Column(type="integer", length=11, nullable=true)
*/
private $completion_week;
/**
* @ORM\ManyToOne(targetEntity="App\MyBundle\Entity\Topic", inversedBy="listItems", fetch="EAGER")
* @ORM\JoinColumn(name="topic_id", referencedColumnName="id")
*/
private $topic;
...
I am using Symfony2 form builder to build the form. This is working great. I have added javascript for add/remove buttons on the front end. In general - I am able to save and persist the form collection without any problems.
User Form Type:
<?php
/**
* Class UserType
* @package App\MyBundle\Form\Type
*/
class UserType extends AbstractType
{
/**
* @param OptionsResolverInterface $resolver]
*/
public function setDefaultOptions(OptionsResolverInterface $resolver)
{
$resolver->setDefaults(array(
'data_class' => 'App\MyBundle\Entity\User',
'method' => 'POST',
'cascade_validation' => true
));
}
/**
* @param FormBuilderInterface $builder
* @param array $options
*/
public function buildForm(FormBuilderInterface $builder, array $options)
{
// Set User data
$user = $builder->getData();
// Generate form
$builder
->add('listItems', 'collection', array(
'options' => array(
'required' => false,
'attr' => array('class'=>'col-sm-12')
),
'type' => new ListItemType(),
'label' => false,
'allow_add' => true,
'allow_delete' => true,
'delete_empty' => true,
'prototype' => true,
'by_reference' => false
))
->add('first_name')
->add('last_name')
->add('email');
}
/**
* @return string
*/
public function getName()
{
return 'user';
}
}
List Item Form Type:
<?php
/**
* Class ListItemType
* @package App\MyBundle\Form\Type
*/
class ListItemType extends AbstractType
{
/**
* @param OptionsResolverInterface $resolver]
*/
public function setDefaultOptions(OptionsResolverInterface $resolver)
{
$resolver->setDefaults(array(
'data_class' => 'App\MyBundle\Entity\ListItem',
'method' => 'POST',
));
}
/**
* @param FormBuilderInterface $builder
* @param array $options
*/
public function buildForm(FormBuilderInterface $builder, array $options)
{
// Generate form
$builder
->add('topic', 'entity', array(
'attr' => array('class' => 'form-control chosen-select-10'),
'class' => 'AppMyBundle:Topic',
'empty_value' => 'Choose a Topic',
'label' => false,
'property' => 'name',
'expanded' => false,
'multiple' => false
))
->add('completion_week', 'integer', array(
'attr' => array('class' => 'form-control'),
'label' => false,
));
}
/**
* @return string
*/
public function getName()
{
return 'list_item';
}
}
What I discovered is that when the form is being processed - something is happening within the handleRequest() method that is swapping out foreign key references on different list items in the collection. In some cases - without making any changes to the form collection on the front end. Like so:
Original collection of List Items for a User:
User's List Item Collection after handleRequest():
This then causes the integrity constraint violation when Doctrine attempts to write the first record because it is violating the unique constraint on the List Items table. What I do not understand is how/why the handleRequest() method would be swapping foreign keys on update.
Also - in many cases - the form will persist fine for a user. I hate to use the word "random" here but I have not been able to identify a way to duplicate the issue other than just working with the entity for a while and performing CRUD operations on it. Many times the form persists fine - other times the foreign key references get swapped and I am unable to submit the form to update the entity because of the UNIQUE constraint.
Has anyone experienced similar issues or have some insight on why this might be occurring? Is this a bug in the handleRequest() method? This will occur even if I have not made any changes to the List Item collection. As in - if I edit a user and simply submit the form without making any changes - this behavior will still occur.
Is there a better way to do this?
