Symfony 3 - how to prevent sql table from duplicate rows

Question

I'm creating API in Symfony3 and I have a situation where the user can add games to his stack. So I have the table which connects user id's and game id's, but when I'm adding rows to DB with that there can be a situation where there can be duplicates, like:

I want to avoid that situation, but how to do it? Are some symfony3-like ways to prevent that kind of situation? Or should I add some if-else statements in the endpoint and return some JSON with success: false? Anyway, how can I do it? I'm looking for the easiest or the most efficient way.

The code of the entity looks like:

<?php
namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;
use Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity;

/**
 * @ORM\Entity
 * @ORM\Table(name="games_on_stacks")
 */
class GamesOnStacks
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     * @ORM\Column(type="integer")
     */
    private $id;
    /**
     * @ORM\ManyToOne(targetEntity="User")
     *
     */
    private $user;

    /**
     * @return mixed
     */
    public function getUser()
    {
        return $this->user;
    }

    /**
     * @param mixed $user
     */
    public function setUser(User $user)
    {
        $this->user = $user;
    }

    /**
     * @ORM\ManyToOne(targetEntity="Game")
     */
    private $game;

    /**
     * @return mixed
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @return mixed
     */
    public function getGame()
    {
        return $this->game;
    }

    /**
     * @param mixed $game
     */
    public function setGame($game)
    {
        $this->game = $game;
    }

}

and the REST endpoint:

<?php


namespace AppBundle\Controller;

use AppBundle\Entity\Game;
use AppBundle\Entity\GamesOnStacks;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;

class GameController extends Controller
{
    (...)

    /**
     * @Route("/game/{gameId}/add", name="game_add_to_stack")
     */
    public function addToStack($gameId)
    {
        $gameOnStack = new GamesOnStacks(); 
        // db apply
        $em = $this->getDoctrine()->getManager();
        $game = $em->getRepository('AppBundle:Game')
            ->findOneBy(['id' => $gameId]);
        $gameOnStack->setGame($game);
        $user = $em->getRepository('AppBundle:User')
            ->findOneBy(['id' => 1]);
        $gameOnStack->setUser($user);
        $em->persist($gameOnStack);
        $em->flush();
        $arr = array('success' => true);

        return new JsonResponse(json_encode((array)$arr));

    }
}

You can look for existing `GamesOnStacks` and save only when there is none. Additionally, you can set a [constraint on database level](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/annotations-reference.html#annref-uniqueconstraint). — Mateusz Sip, May 28 '17 at 17:49
@MateuszSip OK, but how to do it? I tried: `@UniqueEntity( * fields={"user", "game"}, * errorPath="game", * message="This game is already added on this user stack." * )` to add under the `@ORM\Table(name="games_on_stacks")` line, but it didn't worked. — hm hm, May 28 '17 at 19:20
`@UniqueEntity` is a different thing, related to [validator component](https://symfony.com/doc/current/validation.html). Constraint i mentioned needs schema update/migration (and it's worth doing). You can use both (It's good to introduce that kind of validation both on application and database level) or replace `@UniqueEntity` with a manual check using repository method (this is more explicit, but would work the same if implemented correctly). Everything is documented well in symfony docs, so spend some time on reading and you should get through your problem without a hassle. — Mateusz Sip, May 28 '17 at 19:32

score 2 · Accepted Answer · answered May 28 '17 at 19:40

add the UniqueEntity constraint to your entity and validate the object using the validator service in your controller:

AppBundle/Entity/GamesOnStacks.php

<?php
namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;
use Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity;

/**
 * @ORM\Entity
 * @ORM\Table(name="games_on_stacks")
 *
 * @UniqueEntity( fields={"user", "game"}, errorPath="game", message="This game is already added on this user stack.")
 */
class GamesOnStacks
{
...
}

AppBundle/Controller/GameController

/**
 * @Route("/game/{gameId}/add", name="game_add_to_stack")
 */
public function addToStack($gameId)
{
    $gameOnStack = new GamesOnStacks(); 
    // db apply
    $em = $this->getDoctrine()->getManager();
    $game = $em->getRepository('AppBundle:Game')
        ->findOneBy(['id' => $gameId]);
    $gameOnStack->setGame($game);
    $user = $em->getRepository('AppBundle:User')
        ->findOneBy(['id' => 1]);
    $gameOnStack->setUser($user);

    // validate using the validator service
    $validator = $this->get('validator');
    $errors = $validator->validate($gameOnStack);

    if (count($errors) > 0) {

        // custom error handling, e.g. returning failure jsonreponse, etc.
        $errorsString = (string) $errors;           

    } else {

        $em->persist($gameOnStack);
        $em->flush();
        $arr = array('success' => true);


        return new JsonResponse(json_encode((array)$arr));
    }

}

Thanks! That was exactly what I was looking for.;) I created error handling in that way: `if (count($errors) > 0) { $errorsString = ''; foreach ($errors as $error) { $errorsString .= $error->getMessage(); } $errArr = array( 'success' => false, 'error' => $errorsString ); return new JsonResponse(json_encode((array)$errArr)); }` — hm hm, May 28 '17 at 20:04

Symfony 3 - how to prevent sql table from duplicate rows

1 Answers1