Gorm How to change clauses of a given statement (UPDATE's ["UPDATE", "SET", "WHERE"] to ["UPDATE", "SET", "FROM", "WHERE"])

Your Question

val := "temp"
db.Model(&property.Property{}).Clauses(clause.From{Tables: []clause.Table{{Name: serviceTableName}, {Name: hostTableName}, {Name: teamTableName}}}).Where(fmt.Sprintf("%s.id = %s.host_id AND %s.id = %s.service_id AND %s.key = ? AND %s.service_id = ? AND %s.id = ?",hostTableName,serviceTableName,serviceTableName,propertyTableName,propertyTableName,propertyTableName,teamTableName),
            "Username", uuid.FromStringOrNil("ff783e2b-ddd2-4d24-82ee-53f58c1be716"),uuid.FromStringOrNil("03302d0b-c28d-444a-9b9b-0440c3d711e3")).
        Updates(property.Property{Value: &val,
    })

The above should produce:

UPDATE Properties SET value = 'temp'    FROM Teams, Hosts, Services WHERE Hosts.ID = Services.host_id AND Services.ID = Properties.service_id  AND Properties.key = 'Username' AND Properties.service_id = 'ff783e2b-ddd2-4d 24-82ee-53f58c1be716' AND Teams.id = '03302d0b-c28d-444a-9b9b-0440c3d711e3';

However, because "FROM" is not a default clause in "UPDATE" statement, ends up being omitted, and I instead get:

[4.407ms] [rows:0] UPDATE "properties" SET "value"='Kek' WHERE hosts.id = services.host_id AND services.id = properties.service_id AND properties.key = 'Username' AND properties.service_id = 'ff783e2b-ddd2-4d24-82ee-53f58c1be716' AND teams.id = '03302d0b-c28d-444a-9b9b-0440c3d711e3'
panic: ERROR: no data source matches prefix: hosts in this context (SQLSTATE 42P01

How could I update the clauses from ["UPDATE", "SET", "WHERE"] to ["UPDATE", "SET", "FROM", "WHERE"] I don't want the above to be a global setting because some of the updates that run under the same driver don't need "FROM" , instead could this be set as part of the .Clause() call?

The document you expected this should be explained

https://gorm.io/docs/sql_builder.html#Clause-Builder

Expected answer

Something along the lines of:

db.Model(...).Clause(clause.ClauseBuilder([]string{"UPDATE", "SET", "FROM", "WHERE")).Clause(clause.From(...))...

Comment From: L1ghtman2k

The following did the trick:

tx := db.Debug().Model(&property.Property{}).Clauses(clause.From{Tables: []clause.Table{{Name: serviceTableName}, {Name: hostTableName}, {Name: teamTableName}}}).
        Where(fmt.Sprintf("%s.id = %s.host_id AND %s.id = %s.service_id AND %s.key = ? AND %s.service_id = ? AND %s.id = ?",hostTableName,serviceTableName,serviceTableName,propertyTableName,propertyTableName,propertyTableName,teamTableName),
            "Username", uuid.FromStringOrNil("ff783e2b-ddd2-4d24-82ee-53f58c1be716"),uuid.FromStringOrNil("03302d0b-c28d-444a-9b9b-0440c3d711e3"))
//THIS PART IS IMPORTANT:
tx.Statement.BuildClauses = []string{clause.Update{}.Name(), clause.Set{}.Name(), clause.From{}.Name(), clause.Where{}.Name()}
tx = tx.Updates(property.Property{Value: &val})     

NOTICE: I did not call. Updates right away, instead I set Statement.BuildClauses to the tables that I need to pull The above statement resulted in the following query:

UPDATE "properties" SET "value"='val' FROM "services","hosts","teams" WHERE hosts.id = services.host_id AND services.id = properties.service_id AND properties.key = 'Username' AND properties.service_id = 'ff783e2b-ddd2-4d24-82ee-53f58c1be716' AND teams.id = '03302d0b-c28d-444a-9b9b-0440c3d711e3'`

Now, I am not sure if this is the best way to go about this, so if anybody knows a more elegant way, please comment!

Comment From: jinzhu

You can write a clause that implements the ModifyStatement method, and use it to change the BuildClauses

Comment From: dhalturin

https://github.com/go-gorm/gorm/issues/4248#issuecomment-1986139639