Core: How to reproduce the algorithm ON DUPLICATE KEY UPDATE?

[cawa-93]

I have a table with users options:

|    id     |    option_id     |    option_value     |
------------------------------------------------------

I want to update a row in a table with one command or create a new one if it does not already exist.

INSERT INTO
  options
SET
  option_id = 'enabled',
  value = 1
ON DUPLICATE KEY UPDATE
  value = 1

How to achieve this result using Lucid or Query Builder?

[thetutlage]

You can run a raw query.

Database.schema.raw('Your sql query')

[cawa-93]

@thetutlage, There is no way to call a method? For example

Database.table('options').insertOrUpdate({option_id: 'enabled', ...})

[RomainLanz]

Hey @cawa-93!

There's the save() method.

• Lucid save()

[cawa-93]

For this, as I understood, you must first find the object. If it does not exist, create. Then save. This requires a few more requests and manipulations, rather than a simple insertOrUpdate (In perspective)

[RomainLanz]

May I ask what's your usecase?

[cawa-93]

@RomainLanz, Excuse me, but I did not understand what you mean

[betaWeb]

I think that having a insertOrUpdate method could be a good idea, and would simplify certain usecases.

[cawa-93]

Example upsert in knex https://github.com/tgriesser/knex/issues/54#issuecomment-294271255

[cawa-93]

For those who are also interested in this issue, I propose a temporary solution (for mysql):

const pagesData = {
  id: 1,
  name: 'Some page',
  picture: 'https://...',
}
const insert = Database.table('pages').insert(pagesData).toString()
yield Database.schema.raw(insert + ' ON DUPLICATE KEY UPDATE name=VALUES(name), picture=VALUES(picture), update_at=CURRENT_TIMESTAMP')

[thetutlage]

Closing since not a bug, but can be discussed in forum.https://forum.adonisjs.com/

[lock[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.