implementing "update if exists" in Doctrine ORM

Question

I am trying to INSERT OR UPDATE IF EXISTS in one transaction.

in mysql, I would generally use DUPLICATE KEY ("UPDATE ON DUPLICATE KEY".) I'm aware of many solutions to this problem using various SQL variants and sub-queries, but I'm trying to implement this in Doctrine (PHP ORM). It seems there would be Doctrine methods for doing this since it's so feature packed, but I'm not finding anything. Is this sort of thing a problem using PHP ORM packages for some reason? Or do any Doctrine experts know how to achieve this through hacks or any means?

I've started work on a plugin to implement this functionality. It's still in the early stages but tested and working for my use-case. It's available at: https://github.com/m14t/m14tDoctrineRecordPlugin Test cases, bug reports and pull requests welcome. — m14t, Apr 19 '12 at 07:39
For anyone that finds this, merge() is deprecated and will be removed. https://github.com/doctrine/orm/pull/1577 — cjsfj, Feb 25 '21 at 17:38

score 7 · Answer 1 · answered Oct 11 '17 at 13:34

7

According to https://www.vivait.co.uk/labs/updating-entities-when-an-insert-has-a-duplicate-key-in-doctrine this can be achieved with $entityManager->merge().

$entity = new Table();
$entity->setId(1);
$entity->setValue('TEST');

$entityManager->merge($entity);
$entityManager->flush();

answered Oct 11 '17 at 13:34

Björn Tantau

1,564
14
13

strange, but don't worked for me. i'm used https://stackoverflow.com/a/24384768/5809937 in symfony 3 – Vladimir Ch Aug 07 '18 at 21:55

score 6 · Answer 2 · answered Oct 20 '09 at 08:45

6

The only thing I can think of is to query first for the entity if it exists otherwise create new entity.

if(!$entity = Doctrine::getTable('Foo')->find(/*[insert id]*/))
{
   $entity = new Foo();
}
/*do logic here*/
$entity->save();

answered Oct 20 '09 at 08:45

ken

4,886
4
30
26

21

There's nothing that ensures that the row isn't created between the query and the save. – Alex J Dec 30 '09 at 03:08
7

Isn't that what transactions are for? – chiborg Jan 07 '11 at 12:51
1

Very dangerous, like @IliaJerebtsov said, a row can be created between the persist and the flush. In this case you should consider to use pure SQL ;-( – Thomas Decaux Jun 28 '12 at 06:33

score 3 · Answer 3 · edited Aug 20 '12 at 06:23

3

Doctrine supports REPLACE INTO using the replace() method. This should work exactly like the ON DUPLICATE KEY UPDATE you were looking for.

Docs: Replacing Records

edited Aug 20 '12 at 06:23

j0k

22,600
28
79
90

answered Jul 15 '09 at 17:22

pix0r

31,139
18
86
102

9

the only problem with REPLACE seems to be that it drops and then creates a new row (rather than performing an actual UPDATE), thus dropping the auto increment ids (in this case, my primary id). Am I missing something here? eg - my auto increment id is 9, but the count is as 3000. When I perform REPLACE INTO for row 9, the new row id is 3001. – seans Jul 15 '09 at 17:51
Its not a solution, the replace seems make a delete/insert and change autonumeric values (ex id) – Exos Nov 27 '11 at 15:36
5

`REPLACE INTO` is not equivalent to `INSERT ... ON DUPLICATE KEY UPDATE ...`. The 1st will delete the row and insert a new one. If there are delete triggers or foreign keys (even more with `CASCADE`), `REPLACE` is not a viable option. – ypercubeᵀᴹ Oct 30 '15 at 08:12
1

This doesn't seem to be an option anymore. – Matthieu Napoli Aug 14 '19 at 19:11

score 0 · Answer 4 · answered Nov 18 '18 at 14:37

0

I think best way is to call entityManager->merge($entity); Because it's the closest thing to update if exist operation as promised in documentation: https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/working-with-objects.html

answered Nov 18 '18 at 14:37

Javlonbek

281
2
7

implementing "update if exists" in Doctrine ORM

4 Answers4

Linked

Related