InsertGraph does not work with onConflict

[from-nibly]

Apologies for the quality of the previous issue.

Which objection version are you using?

At the moment the latest non 3.0.0 version: 2.2.18 Upgrading to 3.0.0 did not seem to affect this issue but I could be mistaken.

What are you doing? I am trying to run an insertGraph query as an upsert using onConflict with postgres.

What code are you running?

My queries look like this.

Contact.query().insertGraph({
  ...contactProperties,
  integrations: [
    {
       ...integrationProperties
    }
  ]
}).onConflict().ignore();

Contact.query().insertGraph({
  ...contactProperties,
  user: {
    ...userProperties
  }
}).onConflict().ignore();

What is happening?

on conflict doesn't appear in the built insert query for the Contact table.

If I use insert() instead of insertGraph() the on conflict does appear. but that means I have to run separate queries. (which I know it really is doing on the backend I just would like to be able to describe it as a graph insert).

What are you expecting to happen instead?

I would expect on conflict to appear at the end of both the insert queries for Contact and the insert queries for Integrations.

Questions:

• Is this a design limitation?
• Is this expected?
  • If so is there a way to make this more apparent rather than just ignoring the onConflict() calls on the query?
• Is this what you expect from an issue? Let me know if there is something I can improve.

[kibertoad]

if onConflict is coming from knex, then you'd need Objection 3.0.0 and knex 0.95. but if it works with insert, then that probably isn't the reason

[from-nibly]

Let me try again with objection.js 3.0.0 I do currently have knex 0.95.

[from-nibly]

OK just tried it with 3.0.0 and it's definitely still not adding the on conflcit into the query. It just ends with returning id.

This is the query that prints out when I add .debug()

insert into "contacts" ("avatar", "cause_group_id", "created_at", "email", "first_name", "id", "is_active", "last_name", "org_id", "updated_at") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) returning "id"

[koskimas]

Thank you for rewriting this issue!

It might be that objection doesn't support empty onConflict call. Or possibly doesn't support onConflict at all with insertGraph at the moment. I need to check this.

A reason why it's not implemented might be that it's not clear what onConflict should do with insertGraph. Some people might expect it to only add on conflict for the root query, in this case insert into "contacts". Some people might expect on conflict to be added to all insert queries (like you if I understood correctly?).

In the latter case, what should happen if you call onConflict('email') and not all tables in the graph have the column email? Objection can't know which tables do, so it would either add the on conflict "email" to all queries or none of them.

How would you like this to work considering all the possible ways to call onConflict?

[from-nibly]

What if onConflict took a relation object type thing? the key would be the relations included in the query. and the value would be ignore or an object with either a $modify builder or a $merge which would take a PartialModelObject ?

And if you pass it nothing it just assumes that you want to only specify onConflict for the main table in the query?

Another way would be to just assume it always only applies to the main table. and then allow you to add $modify to the sub table object? allowing you to get a builder instance and just add onConflict via the builder?

Does that make sense?

maybe some rough code examples?

Only allow onConflict for main table

Contact.query().insertGraph({
  ...contactProperties,
  integrations: [
    {
       ...integrationProperties
    }
  ]
}).onConflict().ignore();
// only Contact insert query would have onConflict

add object as a possible param for onConflict. key is table name, value is either a boolean for ignore, or an object/array for merge.

Contact.query().insertGraph({
  ...contactProperties,
  integrations: [
    {
       ...integrationProperties
    }
  ]
}).onConflict({
  integrations: true | {
    first_name: 'so and so'
  } | [ { first_name: 'so and so' }]
}).ignore();

allow $modify in insertGraph to allow for adding onConflict

Contact.query().insertGraph({
  ...contactProperties,
  integrations: [
    {
       ...integrationProperties
       $modify: builder => builder.onConflict().ignore()
    }
  ]
}).onConflict(['email']).ignore();

This would be repetitive with arrays as well as awkward.

Maybe a special key for array values?

Contact.query().insertGraph({
  ...contactProperties,
  integrations: {
    $items: [{
       ...integrationProperties
      
    }]
    $modify: builder => builder.onConflict().ignore()
  }
}).onConflict(['email']).ignore();

I feel like there is something in there? Maybe between a combination of some of them.

I think I prefer cases where the options can be as close to the data being inserted as possible. Lining up arrays in two different places feels like a code smell. the merge option is probably the trickiest to deal with.

[joneldiablo]

HI!!!! just have a doubt with somthing similar,

MyModel.query().insertGraph([.....]);

I'm trying to insert an array of objects but if someone has a conflict error, there is not any insertions, could I do:

MyModel.query().insertGraph([.....]).onConflict().ignore();

to continue without filter my array or check if somthing already exist?