How to 'upsert' (update or insert): For Mysql, Postgresql and SQLite (MERGE SQL)

$model->save($upsertArray);

See: https://docs.phalcon.io/en/latest/reference/models.html#creating-updating-records

what SQL generate? @nsossonko

I say something like:

 MERGE INTO table_name_1 USING table_name_2 ON (condition)
   WHEN MATCHED THEN
   UPDATE SET column1 = value1 [, column2 = value2 ...]
   WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...

Is this some PHP framework support even?

Model::save is already checking itself if it's new instance like(new Model()) or already found model using ::findFirst() And it will generate update or insert query, but without using merge. Not sure why exactly you need it ? Can you write some example for what exactly you need it ?

info: [https://en.wikipedia.org/wiki/Merge_(SQL)]

• For Mysql: https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
• For Postgresql: https://wiki.postgresql.org/wiki/UPSERT
• For Sqlite: https://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace

CREATE TABLE public.config
(
  collection character varying(255) NOT NULL DEFAULT ''::character varying, -- Primary Key: Config object collection.
  name character varying(255) NOT NULL DEFAULT ''::character varying, -- Primary Key: Config object name.
  data bytea, -- A serialized configuration object data.
  CONSTRAINT config____pkey PRIMARY KEY (collection, name)
)

I wish to insert or update the fields that were updated, also if you can not replace everything, only they changed.

Then use dynamic update - it will update only fields which were updated in model.

Not with model, one query.

I created a function:

    /**
     * Helper method so we can re-try a write.
     *
     * @param string $name
     *   The config name.
     * @param string $data
     *   The config data, already dumped to a string.
     * @return bool
     * @throws \Exception
     */
    protected function doWrite($name, $data)
    {
        $type = $this->connection->getType();
        switch ($type) {
            case 'mysql':
                $sql = 'INSERT INTO config ( collection, name, data ) '
                    . ' VALUES (:collection, :name, :data) '
                    . ' ON DUPLICATE KEY UPDATE '
                    . ' data = VALUES(data);';
                break;
            case 'pgsql':
                $sql = 'INSERT INTO config (collection, name, data) '
                    . 'VALUES (:collection, :name, :data) '
                    . 'ON CONFLICT (collection, name) '
                    . 'DO UPDATE SET data = EXCLUDED.data;';
                break;
            case 'sqlite':
                $sql = 'INSERT OR REPLACE INTO config (collection, name, data) '
                    . 'VALUES (:collection, :name, :data);';
                break;
            default:
                throw new \Exception('Only support Mysql, Postgres and SQLite');
                break;
        }

        return (bool)$this->connection->execute(
            $sql,
            [
                'collection' => $this->collection,
                'name' => $name,
                'data' => $this->encode($data)
            ],
            [
                Db\Column::BIND_PARAM_STR,
                Db\Column::BIND_PARAM_STR,
                Db\Column::BIND_PARAM_BLOB
            ]
        );
    }

Oh okay. I would just use Model because it has already dynamic update built-in.

Could you give me an example with the model, please.

I want to know the inner workings.

Oh okay. I would just use Model because it has already dynamic update built-in.

is antipattern???

// anti pattern to upsert
$fbUser = \App\Models\Profile::findFirst($fbId);

if (empty($fbUser)) {
    $fbUser = new \App\Models\Profile();
    $fbUser->id = $fbId;
}
// ~ anti pattern to upsert

$fbUser->status = 2;

See: https://github.com/phalcon/cphalcon/issues/11101

You can't set primary keys in phalcon. Dynamic update is for updating rows - for executing query update only with changed fields, not all. Just remove setting id. For me it's not antipattern.

If exists:

$connection->query();
$connection->insert();
$connection->delete();
$connection->update();

There should be:

$connection->merge();

Phalcon need to implement:

$connection->merge();
$connection->upsert();

NRF

Why ? Can you show me other frameworks implementing this ?

• Drupal 8
  • merge
  • upsert
• Doctrine 2
  • upsert (in query builder ->upsert(true))

I am researching. to give examples of code.

Example drupal 8 merge query:

https://www.drupal8.ovh/en/tutoriels/46/database-merge-insert-or-update-if-exist

If you really wnat this then the best would be just implement it to phalcon, if you don't know zephir then you can just do it for incubator in plain php https://github.com/phalcon/incubator/tree/master/Library/Phalcon and someone will port it to phalcon. I was never using merge/upsert. Don't know how they work exactly and how it should work in active record pattern.