Is it possible to use a compound key (two or more keys) as a predicate for updateOrCreateMany?

[vtduong]

I would like to update many rows based on a compound key or where two or more conditions are met, something like:

Address.updateOrCreateMany(['city', 'country'], addresses)

Is it possible in Adonisjs v5 and v6?

In Adonisjs v5, the predicate parameter of the method accepts a list of keys but only the first key is used in the WHERE clause of the executed statement.

I appreciate any help!

[thetutlage]

Can you please enable query debugging and share the SQL queries executed by the updateOrCreateMany method?

[vtduong]

I apologize for not including the full logs as it's quite lengthy (most queries are for setting up test data) and I thought it wasn't relevant to the issue. Please see the attached file:
test-log.log

I really appreciate your time and support!

[thetutlage]

Well, all I want is to isolate this issue and share its log. Either a log file of everything and neither the incomplete logs. Or you can create a new repo, reproduce the issue and share that with me

[vtduong]

Thank you for your guidance! here's the relevant log resulted from executing the below code

Opportunity.updateOrCreateMany(['id', 'version'], opps)

From the screenshot above, the SELECT FOR UPDATE statement does include both id and version as part of the WHERE clause but the UPDATE statement includes only the id which updates all rows with that id regardless of version. Is this the expected behavior of updateOrCreateMany?

[thetutlage]

I think, the update clause uses the primaryKey from the model, which in this case is the id column. So, if the primarKey alone is not unique, then you might run into other issues as well beyond updateOrCreateMany method.

This is because, Lucid does not support compound primary keys. Again, updateOrCreateMany can pass multiple keys for the SELECT query, but update will always be done using the primaryKey.

[vtduong]

I see, thank you for your detailed explanation! I found a discussion about this topic but it dated 2019 so I wonder if Lucid supports primary composite key in Adonis v6?