We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

On duplicate key update

Hello!

I've been looking through the documentation (and Google) for how to set on duplicate key update in the models. All I've seen is basically writing custom queries to do this, but I feel that there must be some behavior that can be set in the model initialization or similar. In my table, I have a unique index (col1, col2), and basically, it only contains two values. If it does not exist, create it. If it exists, update col2.

Can someone point me in the right direction?

Thanks.

// dimhoLt



22.6k

Bump.



2.7k
Accepted
answer

on duplicate key update is not a sql standard. It's pretty much specific to MySQL. If you want your scripts to actually execute the mysql query with on duplicate key update, with Phalcon, you will have no choice but to have custom queries. If you just want the logic of the "on duplicate key update" (i doubt it), you can use beforeValidationOnCreate, check with select if the primary and/or unique key exist and if found, run update and fail validation so the insert wont be done. If you are running slave/master mysql servers, make sure to check if the keys exist using the master connection, not on the slave connection.



22.6k

Thank you for your answer. I'm not sure, however, why there would be any doubt that I would want this logic. Is there perhaps some best practices you're referring to that I may have missed?



2.7k

i said "i doubt it" just because from your question i was 99% sure that you want to run a single mysql query INSERT ... ON DUPLICATE KEY instead of 2 queries.It was not because of some "best practices" thing :) From my unpopular point of view, MySql's LIMIT,and ON DUPLICATE KEY should become sql standard.



22.6k

I see. Thank you for your extended explanation =) Yes, I was looking to do that. However, in this case, I'm going to solve this particular issue in a Service using this particular model instead to retain a consistent model behaviour / interface.

I agree, those things are wonderful and should exist everywhere. Recently switched to MariaDB which I so far like very well (MySQL fork ofc).

Thank you again!



621

Anything new here?