(MySql)OnConflict for hasOne/hasMany assotiations - specify all (non keys) fields updates for all tables

[dkul108]

Your Question

let's suppose the user table has addresses discounts, whatever - any kinds of hasOne and hasMany relationships for a MySQL
so how to update all fields from the forementioned associations

Upsert / On Conflict how to update ALL columns including all associations (hasOne etc) on conflict?

Db.Clauses(clause.OnConflict
{
Tables:[]clause.Table{"Addresses", "Discounts"}
Columns: []clause.Column{{Name: "some_foreign key_to_root_id"}},
DoUpdates: clause.AssignmentColumns([]string{ALL}),
}).Create(&users)

currently, NOTHING is updated at all

Actually it is required to be in some general Config and not in each Create/Upsert specified

[dkul108]

anyway, the default behavior or some option should do
INSERT...VALUES...ON
DUPLICATE KEY
UPDATE
"ALL FIELDS FROM ASSOCIATION SPECIFIED"

let me know if the patch from my side is needed. thanks, Dima

[jinzhu]

Yes, it would be helpful, do you have any idea for how to change it?

[dkul108]

From the theory point - actually it is a just a bug.
-Would be great to have it in Config - defaults on clause.OnConflict per association.
-Natural way according what you ve created is to adopt your fields permissions
-you ve deviated from Hibernate Java ORM 20 years strong user experience that associations updated should be updated with root table out of box + it uses pure ANSI SQL only (disregrading they've started from Oracle dialect at Hibernate 2.0)
-Expected workaround is probably some additional flag. But we have intersection of both gorm and MySql driver of yours

Waiting for your reply with your understanding and vision and then - lets go to the source code of associations.go lines 160-175 - why we just specify foreign keys for update in associations (the rest of the code may be affected is just the way to make things more obious). But first we need to understand what we would break and how is possible that nobody has ccomplained that assotiations in gorm2 with mysql where never updated (only object replacement is possible)

[jinzhu]

If your associations's data is not complete, which is common case when you are getting your data from API call, your association's data will be corrupted when saving your data, GORM's default behavior should never do that to users.

[dkul108]

ok but how we've lost this normal feature by migrating from GORM1 to 2 ( due to the lack of joins)? Basically we had a working source code in production. Just to update an entity with associations updated

[dkul108]

btw workaround is to delete all foreign keys and IDs but we have our own delete orphans implementation (also miissed in GORM) and gorm is recreates new records and we delete the old ones. It is totally wrong - but we can't wait anymore.

will try to return to you with a patch soon in order just to add all fields by some flag

[jinzhu]

https://gorm.io/docs/delete.html#delete_with_select

[jinzhu]

https://gorm.io/docs/belongs_to.html#FOREIGN-KEY-Constraints

[dkul108]

https://gorm.io/docs/delete.html#delete_with_select
yes we ve made delete orphans operation fro GORM1 with BeforeUpdate/AfterUpdate and Delete associations not used
https://gorm.io/docs/belongs_to.html#FOREIGN-KEY-Constraints
yes was happy to see this before but it is not working - so we are suing self-made delete orphans untill that cascades will start to work
but delete orphans is a DELETE ON UPDATE operation that is different a bit

anyway this workaround is ugly and it reduces performance - an actual fix required

[jinzhu]

Following code will save full associations

DB.Session(&gorm.Session{FullSaveAssociations: true}).Save(&user)

[zsw-test]

thanks