Php – implementing “update if exists” in Doctrine ORM

doctrineormPHPsql

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?

Best Answer

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();

Related Solutions

The “N+1 selects problem” in ORM (Object-Relational Mapping)

Let's say you have a collection of Car objects (database rows), and each Car has a collection of Wheel objects (also rows). In other words, Car → Wheel is a 1-to-many relationship.

Now, let's say you need to iterate through all the cars, and for each one, print out a list of the wheels. The naive O/R implementation would do the following:

SELECT * FROM Cars;

And then for each Car:

SELECT * FROM Wheel WHERE CarId = ?

In other words, you have one select for the Cars, and then N additional selects, where N is the total number of cars.

Alternatively, one could get all wheels and perform the lookups in memory:

SELECT * FROM Wheel

This reduces the number of round-trips to the database from N+1 to 2. Most ORM tools give you several ways to prevent N+1 selects.

Reference: Java Persistence with Hibernate, chapter 13.

SQLite – UPSERT not INSERT or REPLACE

Assuming three columns in the table: ID, NAME, ROLE

BAD: This will insert or replace all columns with new values for ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

Best Answer

Related Solutions

The “N+1 selects problem” in ORM (Object-Relational Mapping)

SQLite – UPSERT *not* INSERT or REPLACE

Related Topic

SQLite – UPSERT not INSERT or REPLACE