Checking for duplicate keys with Doctrine 2

问题:

Is there an easy way to check for duplicate keys with Doctrine 2 before doing a flush?

回答1:

You can catch the UniqueConstraintViolationException as such:

use Doctrine\DBAL\Exception\UniqueConstraintViolationException;

// ...

try {
   // ...
   $em->flush();
}
catch (UniqueConstraintViolationException $e) {
    // ....
}

回答2:

I use this strategy to check for unique constraints after flush(), may not be what you want, but might help someone else.

---

When you call flush(), if a unique constrain fails, a PDOException is thrown with the code 23000.

try {
    // ...
    $em->flush();
}
catch( \PDOException $e )
{
    if( $e->getCode() === '23000' )
    {
        echo $e->getMessage();

        // Will output an SQLSTATE[23000] message, similar to:
        // Integrity constraint violation: 1062 Duplicate entry 'x'
        // ... for key 'UNIQ_BB4A8E30E7927C74'
    }

    else throw $e;
}

---

If you need to get the name of the failing column:

Create table indices with prefixed names, eg. 'unique_'

 * @Entity
 * @Table(name="table_name",
 *      uniqueConstraints={
 *          @UniqueConstraint(name="unique_name",columns={"name"}),
 *          @UniqueConstraint(name="unique_email",columns={"email"})
 *      })

---

DO NOT specify your columns as unique in the @Column definition

This seems to override the index name with a random one...

 **ie.** Do not have 'unique=true' in your @Column definition

---

After you regenerate your table (you may need to drop it & rebuild), you should be able to extract the column name from the exception message.

// ...
if( $e->getCode() === '23000' )
{
    if( \preg_match( "%key 'unique_(?P<key>.+)'%", $e->getMessage(), $match ) )
    {
        echo 'Unique constraint failed for key "' . $match[ 'key' ] . '"';
    }

    else throw $e;
}

else throw $e;

---

Not perfect, but it works...

回答3:

If running a SELECT query prior to the insert is not what you want, you can only run the flush() and catch the exception.

In Doctrine DBAL 2.3, you can safely understand an unique constraint error by looking at the PDO Exception error code (23000 for MySQL, 23050 for Postgres), that is wrapped by the Doctrine DBALException:

        try {
            $em->flush($user);
        } catch (\Doctrine\DBAL\DBALException $e) {
            if ($e->getPrevious() &&  0 === strpos($e->getPrevious()->getCode(), '23')) {
                throw new YourCustomException();
            }
        }

回答4:

I have come across this problem some time ago, too. The main problem is not database specific exceptions but the fact, when an PDOException is thrown the EntityManager is closed. That means you can not be sure what will happen with the data you wanted to flush. But probably it would be not saved in database because I think this is done within a transaction.

So when I was thinking about this problem I came up with this solution, but I did not have time to actually write it yet.

1. It could be done using event listeners, particularly the onFlush event. This event is invoked before the data is send to database (after the changesets are computed - so you already know which entities were changed).
2. In this event listener you would have to browse all the changed entities for their keys (for primary it would be looking in the class metadata for @Id).
3. Then you would have to use a find method with the criteria of your keys. If you would find a result, you have the chance to throw your own exception, which will not close the EntityManager and you are able to catch it in your model and make some corrections to the data before trying the flush again.

The problem with this solution would be that it could generate quite a lot of queries to the database, so it would require quite a lot of optimization. If you want to use such thing only in few places I recommend doing the check on the place where the duplicate might arise. So for example where you want to create an entity and save it:

$user = new User('login');
$presentUsers = $em->getRepository('MyProject\Domain\User')->findBy(array('login' => 'login'));
if (count($presentUsers)>0) {
    // this login is already taken (throw exception)
}

回答5:

If you're using Symfony2 you can use UniqueEntity(…) with form->isValid() to catch duplicates prior to flush().

I'm on the fence posting this answer here but it seems valuable since a lot of Doctrine user's will also be using Symfony2. To be clear: this uses Symfony's validations class that under the hood is using an entity repository to check (is configurable but defaults to findBy).

On your entity you can add the annotation:

use Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity;

/**
 * @UniqueEntity("email")
 */
class YourEntity {

Then in your controller, after handing the request to the form you can check your validations.

$form->handleRequest($request);

if ( ! $form->isValid())
{
    if ($email_errors = $form['email']->getErrors())
    {
        foreach($email_errors as $error) {
           // all validation errors related to email
        }
    }
…

I'd recommend combining this with Peter's answer, since your database schema should enforce uniqueness too:

/**
 * @UniqueEntity('email')
 * @Orm\Entity()
 * @Orm\Table(name="table_name",
 *      uniqueConstraints={
 *          @UniqueConstraint(name="unique_email",columns={"email"})
 * })
 */

回答6:

If you just want to catch duplicate errors. You should not just check the code number

$e->getCode() === '23000'

because this will catch other errors like field 'user' can not be empty. My solution is to check the error message, if it contains the Text 'Duplicate entry'

                try {
                    $em->flush();
                } catch (\Doctrine\DBAL\DBALException $e) {

                    if (is_int(strpos($e->getPrevious()->getMessage(), 'Duplicate entry'))) {
                        $error = 'The name of the site must be a unique name!';
                    } else {
                        //....
                    }
                }

回答7:

In Symfony 2, it actually throws an \Exception, not a \PDOException

try {
    // ...
    $em->flush();
}
catch( \Exception $e )
{
   echo $e->getMessage();
   echo  $e->getCode(); //shows '0'
   ### handle ###

}

$e->getMessage() echos something like following:

An exception occurred while executing 'INSERT INTO (...) VALUES (?, ?, ?, ?)' with params [...]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '...' for key 'PRIMARY'

回答8:

I would like to add to this specifically regarding PDOExceptions--

The 23000 error code is blanket code for a family of Integrity Constraint Violations that MySQL can return.

Therefore, handling the 23000 error code is not specific enough for some use cases.

For example you may want to react differently to a duplicate record violation than to a missing foreign key violation.

Here is an example of how to deal with this:

try {
     $pdo -> executeDoomedToFailQuery();
} catch(\PDOException $e) {
     // log the actual exception here
     $code = PDOCode::get($e);
     // Decide what to do next based on meaningful MySQL code
}

// ... The PDOCode::get function

public static function get(\PDOException $e) {
    $message = $e -> getMessage();
    $matches = array();
    $code = preg_match('/ (\d\d\d\d) / ', $message, $matches);
    return $code;
}

I realize that this is not as detailed as the question was asking but I find this is very useful in many cases and is not Doctrine2 specific.

回答9:

The easiest way should be this :

$product    = $entityManager->getRepository("\Api\Product\Entity\Product")->findBy(array('productName' => $data['product_name']));
if(!empty($product)){
 // duplicate
}

回答10:

I used this and it seems to work. It returns the specific MySQL error number - i.e. 1062 for a duplicate entry - ready for you to handle how you like.

try
{
    $em->flush();
}
catch(\PDOException $e)
{
    $code = $e->errorInfo[1];
    // Do stuff with error code
    echo $code;
}

I tested this with a few other scenarios and it will return other codes too like 1146 (Table doesn't exist) and 1054 (Unknown column).