Upsert (append ... or replace)

Upsert is ObjectQuel's insert-or-update statement — append ... or replace ... combines append and replace into a single statement, run through the same EntityManager::executeQuery() entry point as retrieve. It bypasses the identity map and change tracking entirely, the same as a standalone append, replace, or delete.

explanation

Upsert: Insert-or-Update in One Statement

append ... or replace ... appends this row, or replaces the row(s) where matches instead. When where's columns exactly match a declared unique or primary-key constraint, this compiles to a single dialect-native atomic statement — the database itself resolves the conflict. Any other predicate on any field still works, just without that database-level atomicity guarantee — see "Matching on a Non-Unique Field" below. 

$entityManager->executeQuery('
    range of u is App\Entities\UserEntity
    append to u (email = :e, name = :n) or replace (name = :n) where u.email = :e
', ['e' => 'alice@example.com', 'n' => 'Alice']);

The where names the conflict target explicitly. or replace's assignment list is itself optional — when omitted, a conflict overwrites every appended column, except the target's primary key, with the row that would have been inserted, which is the common case and the whole reason repeating the values would otherwise be needed. The primary key is always left out of this default overwrite, even when one was supplied on the insert side, so a conflict can never clobber an existing row's real identity with a freshly generated or supplied one: 

$entityManager->executeQuery('
    range of u is App\Entities\UserEntity
    append to u (email = :e, name = :n) or replace where u.email = :e
', ['e' => 'alice@example.com', 'n' => 'Alice']);
If the primary key is the only appended column besides the conflict target itself, there's nothing left to update once it's excluded — that's rejected at compile time rather than silently compiling to a no-op update. Write an explicit or replace (...) list for that case.

Write an explicit list only when the conflict-time update must differ from the insert — for example, incrementing a counter instead of overwriting it:

$entityManager->executeQuery('
    range of u is App\Entities\UserEntity
    append to u (email = :e, views = 1) or replace (views = u.views + 1) where u.email = :e
', ['e' => 'alice@example.com']);

A multi-row append ... or replace upserts each row independently, using that row's own values on conflict — this requires where to be backed by a real unique/primary-key constraint (the atomic path): a non-unique-backed where has no per-row conflict target for the database to resolve independently, so a multi-row append using one is rejected at compile time — see "Matching on a Non-Unique Field" below. 

$entityManager->executeQuery('
    range of u is App\Entities\UserEntity
    append to u
        (email = :e1, name = :n1),
        (email = :e2, name = :n2)
    or replace where u.email = :e1
', ['e1' => 'alice@example.com', 'n1' => 'Alice V2', 'e2' => 'carol@example.com', 'n2' => 'Carol']);

The Atomic Path: a Real Unique Constraint

When where's columns exactly match a declared unique or primary-key constraint, the statement compiles to a single dialect-native atomic statement — the database itself enforces the uniqueness and resolves the conflict in one round trip: 

/**
 * @Orm\Table(name="users")
 * @Orm\UniqueIndex(name="idx_unique_email", columns={"email"})
 */
class UserEntity { ... }

Matching on a Non-Unique Field

where isn't restricted to a unique or primary-key column — any predicate a standalone replace supports works here too, on any field. There's no dialect-native atomic form to compile to in this case (the database has no constraint of its own to resolve the conflict against), so ObjectQuel runs the equivalent of a plain replace ... where ... first; only when that matches no rows does it fall back to inserting the new one: 

$entityManager->executeQuery('
    range of u is App\Entities\UserEntity
    append to u (email = :e, name = :n) or replace (name = :n) where u.status = :s
', ['e' => 'alice@example.com', 'n' => 'Alice', 's' => 'pending']);

This is bulk and set-based, same as a standalone replace: if where matches several rows, all of them are updated — that's intentional, not an error, and nothing is inserted whenever at least one row matched.

A multi-row append (...), (...) or replace where <cond> is rejected at compile time when <cond> isn't backed by a declared unique/primary-key constraint — a single shared where can't identify which literal row it's matching against per row the way a real constraint lets the database resolve independently. Write single-row statements instead, or back the conflict target with a real constraint for a multi-row atomic upsert.
Whether where takes the atomic path is checked purely against the entity's declared metadata — @Orm\UniqueIndex/primary-key annotations — never the live database schema or the actual data. A column that happens to hold unique values without a matching annotation still takes the fallback path above; conversely, an annotation that no longer matches the real table (say, the index was dropped outside ObjectQuel) still takes the atomic path, and can fail or misbehave at runtime, since the constraint's physical existence is never verified.

Dialect Notes

These apply to the atomic (constraint-backed) path only — the fallback above compiles to the same UPDATE/INSERT pair on every dialect, with no per-dialect branching at all:

Dialect Compiles to
pgsql / sqlite INSERT ... ON CONFLICT (cols) DO UPDATE SET ... — scoped to exactly the named columns.
mysql / mariadb INSERT ... ON DUPLICATE KEY UPDATE .... MySQL's form has no column-scoping: this fires on any unique-key collision on the table, not only the columns named in the QUEL where. A real, documented dialect gap, not something ObjectQuel can compile around.
sqlsrv MERGE ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...
MySQL's ON DUPLICATE KEY UPDATE reports 2 affected rows (not 1) when a row was updated via the duplicate-key path — a well-known, documented MySQL quirk in the driver's row count, not a bug in the generated SQL.

Working with the Result

Upsert returns a QuelResult with no fetchable rows — getAffectedRows() and getGeneratedId() are the accessors that matter here, the same as for a standalone append: 

$result = $entityManager->executeQuery($query, $parameters);

$affected = $result->getAffectedRows();
$newId    = $result->getGeneratedId(); // null unless a row was actually inserted

For upserts on the atomic (constraint-backed) path, getGeneratedId() is populated only on mysql/mariadb when the row was actually inserted; Postgres, SQLite, and SQL Server cannot distinguish inserts from conflict updates, so it remains null. On the fallback path, ObjectQuel knows whether the insert ran, so getGeneratedId() is populated whenever it did, on every dialect.

Scope & Limitations

  • A multi-row append ... or replace requires where to be backed by a real unique/primary-key constraint — see "Matching on a Non-Unique Field" above.
  • No RETURNING/output-clause support — the generated primary key comes back via getGeneratedId(), not a SQL-level RETURNING/OUTPUT clause. MySQL has no native equivalent to RETURNING at all.