How get Doctrine sortable "Duplicate entry error"

Question

I have a problem with doctrine sortable extension.

first of all, i have a House entity with 1:n Releation to HouseImage entity (setup to save the image postion) and a HouseImage entity 1:1 releation to File entity.

class House
{
    /**
     * @var HouseImage[]|Collection
     *
     * @ORM\OneToMany(
     *     targetEntity="HouseImage",
     *     mappedBy="houses",
     *     cascade={"persist", "remove"},
     *     orphanRemoval=true
     * )
     * @ORM\OrderBy({"position" = "ASC"})
     */
    protected $images;

}


/**
 * HouseImage
 *
 * @ORM\Table(name=house_image)
 * @ORM\Entity(repositoryClass="Gedmo\Sortable\Entity\Repository\SortableRepository")
 */
class HouseImage {


    /**
     * @var House
     *
     * @ORM\ManyToOne(
     *     targetEntity="House",
     *     inversedBy="images",
     * )
     * @ORM\JoinColumn(
     *     name="house_id",
     *     referencedColumnName="id",
     *     onDelete="SET NULL"
     * )
     * @Gedmo\SortableGroup
     */
    protected $houses;

    /**
     * @var File
     *
     * @ORM\OneToOne(
     *     targetEntity="File",
     * )
     *
     * @ORM\JoinColumn(
     *     name="image_id",
     *     referencedColumnName="id",
     *     nullable=false,
     * )
     */
    protected $image;

    /**
     * @var integer
     *
     * @Gedmo\SortablePosition
     * @ORM\Column(name="position", type="integer")
     */
    protected $position;
    
    
    ....
}

//so i create some HouseImage-Objects

$HouseImage = new HouseImage();
$HouseImage->setImage($myFile);

$HouseImage2 = new HouseImage();
$HouseImage2->setImage($myFile2);

$HouseImage3 = new HouseImage();
$HouseImage3->setImage($myFile3);

//add first image-relation to house
$house->setImages([$HouseImage]);
$em->persist($house);
$em->flush();

//add second image-relation, should be inserted at first position 
$house->setImages([$HouseImage2, $HouseImage]);

$em->persist($house);
$em->flush();

//add new list of image-relation 
$house->setImages([$HouseImage2, $HouseImage3]);

$em->persist($house);
$em->flush();

///after flush the entitymanager this error occurred

An exception occurred while executing 'INSERT INTO house_image (position, house_id, image_id) VALUES (?, ?, ?)' with params [0, 123, 999]:\n
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '999' for key 'UNIQ_E0C3790C3DA5256D

the some error occurred with form factory

$data['images'] = [

    [
        'house' => 181,
        'image' => 123
    ],
    [
        'house' => 181,
        'image' => 1234
    ],
    [
        'house' => 181,
        'image' => 12345
    ],
];

$form   = $formFactory->create(HouseType::class, $houseObject);#
$form->submit($data, false);

Question: How can i update the HouseImage-postion in the list by add a list of HouseImage or How can i cleanup all entites befor insert a complete new list of HouseImage-Releations

Edit: I have fixed my issue by removing the addImage() method an implement this setImage() Method:

public function setImages(Collection $images): void
{
    $col = new ArrayCollection();
    $i = 0;
    /* @var $image HouseImage */
    foreach ($images as $image) {
        $image->setHouse($this);
        $image->setPosition($i++);

        $col->add($image);
    }
    $this->images = $col;
}

TZiebura TZiebura · Accepted Answer · 2020-07-10T22:18:38

Your way to create the house image entities is a little bit wrong, you can define that House entity should cascade persist your HouseImage collection. You should add this in your House entity:

class House
{
  function __construct()
  {
    $this->images = new ArrayCollection();
  }

  public function addHouseImage(HouseImage $houseImage)
  {
     $this->images->add($houseImage);
     $houseImage->setHouse($this);
  }
}

Also rename $houses to $house in your HouseImage. You are linking HouseImage to House(not many houses). Also don't forget to link HouseImage with File entity. If you want to set the position after the last image on a new image you do that in addHouseImage function or anywhere else in your application. With the above code you code could look like this:

$image1 = ...;
$image2 = ...;
$image3 = ...;

$house->addHouseImage($image1);
add2.
add2.

// This will persist all house images along with house.
$em->persist($house);
$em->flush();

If I didn't cover something related to your question pleas let me know
Also the exception

'INSERT INTO house_image (position, house_id, image_id) VALUES (?, ?, ?)' with params [0, 123, 999]:\n SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '999' for key 'UNIQ_E0C3790C3DA5256D

tells you that there already is a File with id 999 in relation and you are trying to set 2 HouseImage entities to the same File entity. I can't spot it in your code but I'm almost certain that it happens somewhere in your code.

How get Doctrine sortable "Duplicate entry error"

1 Answers