using on duplicate key in Explorer

mes

I tried to find a solution to use a ‘ON DUPLICATE KEY UPDATE’.
Since I think the usage of the Explorer is more safe, how can i realize such query?

$this->database->table('table')
->insert([
'mail' => $mail,
'status' => 1
])
->(ON DUPLICATE KEY UPDATE)([
'mail' => $mail,
'status' => 2
]);

Is there a chance for implementation within the Explorer?

Thank you in advance?

dsar

Adding a MERGE/UPSERT statement to Database Explorer wouldn't be easy, because every RDBS has its own custom syntax (with some degree of flexibility, more than that of MySQL).

But not even with that syntax :-) with fluent syntax I would use ->insert(…)->orUpdate() or something similar. However PostgreSQL and FirebirdSQL allow to specify which keys to check, I don't know how it could be implemented in Explorer with a nice syntax.

In my opinion you should hide that kind of syntax with a Facade or Repository class for your queries where you put these operations, for example ->firstOrCreate(), ->createOrUpdate(), etc…

Explorer is nice, but there are always cases where you need to use just plain SQL

Last edited by dsar (2022-01-29 11:15)

materix

You could do something like this:

try {
 $this->database->table('table')
->insert([
'mail' => $mail,
'status' => 1
])
} catch (Nette\Database\UniqueConstraintViolationException $e) {
 $this->database->table('table')
->where([...primary_key...])
->update([
'mail' => $mail,
'status' => 2
]);