Update only changed/dirty columns

[gadelkareem]

Is there a way to add several partial update queries in one go. For ex:

Db.Model(t).Column("title","name").Update()
Db.Model(t).Column("col1","col2").Update()

[vmihailenco]

The answer is Column("title","name","col1","col2"), but I guess that is not what you are looking for.

[gadelkareem]

I was looking for something like this:

UPDATE books SET 
title =  CASE  id 
    WHEN 1 THEN 'new title' ELSE title END,
name = CASE  id 
    WHEN 2 THEN 'new name' ELSE name END
    WHERE id IN(1,2);

This way we do not have to send all the struct fields if we do not need. That also can be achieved with hashing old fields and comparing them to the new ones ex: https://gist.github.com/gadelkareem/6e42bbb547dc7afd3c119a612fa5a1c5

[vmihailenco]

No, this is not supported. Proposed solution in https://gist.github.com/gadelkareem/6e42bbb547dc7afd3c119a612fa5a1c5 looks fine and can be used like this:

state := NewModelState(book)
book.Title = "new title"
err := db.Model(book).Columns(state.DirtyColumns()).Update()

There is no real need in adding new code in go-pg (but we may if someone is ready to contibute something).

[gadelkareem]

I will definitely give it a shot if I got the time. Please feel free to close this..

[vmihailenco]

Let's keep this open - it is interesting feature.

[jh125486]

OK, maybe someone can help me out here because I am at my wit's ends :)

I have a pg model:

type ModelA struct {
	tableName struct{} `pg:"model_a,discard_unknown_columns"`

	ID                int64       `pg:"id,pk"`
	Attr1 int `pg:"attr_1"`
        Stuff *string `pg:"stuff"`

	dirtyHash
}

var _ orm.AfterSelectHook = (*ModelA )(nil)

func (a*ModelA ) AfterSelect(ctx context.Context) error {
	a.dirtyHash = initDirtyHash(a)
	return nil
}

type dirtyHash struct {
        in interface{}
	hashes map[string]uint64
}

func initDirtyHash(in interface{}) dirtyHash {
	dh := dirtyHash{
                in: in,
		hashes: make(map[string]uint64),
	}
	v := reflect.ValueOf(in).Elem()
	dh.hashes = make(map[string]uint64)
	var (
		hash uint64
		skip bool
		name string
	)
	for i := 0; i < v.NumField(); i++ {
		hash, skip = dh.hash(v.Field(i))
		if skip {
			continue
		}
		if name = dh.name(v, i); name != "" {
			dh.hashes[name] = hash
		}
	}
	return dh
}

The issue is later when I call modelA.Dirty(), it only sees "dirty" cols if they are not pointers, i.e. Attr1 will be dirty, but Stuff will still show to be a null field in dirtyhash.in.

In the mean time I am just passing back into the Dirty func the struct pointer, and everything works fine (the null column is now a pointer address that the hash function can hash).

I cannot duplicate this in Go playground, and this is such a niche problem I can't find anyone else online having this issue.

Any advice would be greatly appreciated :)

[vmihailenco]

I can't help with dirtyHash but if your frontend only sends dirty/changed columns you can de-serialize them into map[string]interface and use that map for inserts/updates:

var m map[string]interface{}
json.Unmarshal(b, &m)

db.Model(&m).TableExpr("table_name").Insert()