Verifying duplicate keys with Doctrine 2

Question

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

Answer 1

You can catch a UniqueConstraintViolationException as such:

 use Doctrine\DBAL\Exception\UniqueConstraintViolationException; // ... try { // ... $em->flush(); } catch (UniqueConstraintViolationException $e) { // .... } 

Answer 2

I use this strategy to check for unique constraints after flush (), maybe not the way you want, but may help someone else.

---

When you call flush () if a unique constraint cannot fail, a PDOException is thrown with 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 column name with an error :

Create indexes on tables with prefix names, for example. 'Unique _'

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

---

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

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

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

---

After regenerating the table (you may have to drop it and rebuild it), you can 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 ...

Answer 3

If executing a SELECT query before inserting is not what you need, you can only run flush () and catch the exception.

In Doctrine DBAL 2.3, you can safely detect a unique constraint error by looking at the PDO exception error code (23000 for MySQL, 23050 for Postgres), which is enclosed in a Doctrine DBALException exception:

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

Answer 4

I ran into this problem a while ago. The main problem is not with exceptions for a particular database, but with the exception of a PDOException, the EntityManager closes. This means that you cannot be sure what will happen to the data you want to reset. But probably this will not be stored in the database, because I think this is done as part of the transaction.

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

• This can be done using event listeners , in particular the onFlush event. This event is fired before the data is sent to the database (after calculating the change sets, so that you already know which objects were changed).
• In this event listener, you will need to look at all the changed objects for your keys (for the initial search, he will look for class metadata for @Id).
• Then you have to use the find method with the criteria of your keys. If you find the result, you have a chance to throw your own exception that EntityManager will not close, and you can catch it in your model and make some corrections to the data before trying the flash again.

The problem with this solution would be that it could generate quite a lot of database queries, so it would require quite a bit of optimization. If you want to use such a thing in only a few places, I recommend that you check in a place where duplication may occur. For example, if you want to create an object 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) } 

Answer 5

If you use Symfony2, you can use UniqueEntity (...) with form->isValid() to catch duplicates before flush ().

I am on the fence by posting this answer here, but it seems valuable, as many Doctrine users will also use Symfony2. To be clear: this uses the Symfony validation class, which under the hood uses the entity repository for validation (configurable, but findBy by default).

In your organization, you can add annotation:

 use Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity; /** * @UniqueEntity("email") */ class YourEntity { 

Then, in your controller, after submitting the request to the form, you can check your checks.

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

I would recommend combining this with Peter's answer, since your database schema should also provide uniqueness:

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

Answer 6

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

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

because it will catch other errors, such as the user field cannot 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 { //.... } } 

Answer 7

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

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

$ e> getMessage () echos is something like the following:

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

SQLSTATE [23000]: Integrity constraint violation: 1062 Duplicate record "..." for key "PRIMARY"

Answer 8

I would like to add to this specifically about PDOExceptions -

Error code 23000 is a security code for the family of integrity constraints that MySQL can return.

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

For example, you may respond differently to duplicate write violation than to the absence of a 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 understand that this is not as detailed as the question asked, but I believe that it is very useful in many cases and is not specific to Doctrine2.

Answer 9

The easiest way is:

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

Answer 10

I used this and it seems to work. It returns a specific MySQL error number - i.e. 1062 for dubbing - ready for you as you like.

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

I checked this with several other scenarios and it will return other codes too, like 1146 (table does not exist) and 1054 (Unknown column).