UPDATE dynamic query.

[darylhjd]

I have the following query that I am planning to port to jet, but I am not sure how to go about it.

UPDATE users
SET name       = COALESCE($2, name),
    email      = COALESCE($3, email),
    role       = COALESCE($4, role),
    updated_at =
        CASE
            WHEN (COALESCE($2, name) <> name OR
                  COALESCE($3, email) <> email OR
                  COALESCE($4, role) <> role)
                THEN NOW()
            ELSE updated_at
            END
WHERE id = $1
RETURNING id, name, email, role, updated_at;

The arguments in this case can be NULL. The affect is that a new value is set only if the provided argument is NOT NULL, else the old value is used. The field itself cannot be NULL. The updated_at field is only updated when there is a change in the field value.

I am not too sure how this can be done in jet. For example, how do we get existing values within a single query and how can we use nullable values (e.g. does a postgres.NullString exist?)

[houten11]

One way to do it would be to translate your query as is to jet query:

func StringOrNull(s *StringOrNull) StringExpression {
    if s == nil {
        return StringExp(NULL)
    }

    return StringOrNull(*s)
}

.........

var name *StringOrNull ...
var email *StringOrNull ...
var role *StringOrNull ...

stmt := Users.UPDATE().
    SET(
        Users.Name.SET(StringExp(COALESCE(Users.Name, StringOrNull(name)))),
        Users.Email.SET(StringExp(COALESCE(Users.Email, StringOrNull(email)))),
        Users.Role.SET(StringExp(COALESCE(Users.Role, StringOrNull(role)))),
        Users.LastUpdate.SET(TimestampExp(
            CASE().WHEN(
                OR(
                    StringExp(COALESCE(Users.Name, StringOrNull(name))).NOT_EQ(Users.Name),
                    StringExp(COALESCE(Users.Email, StringOrNull(email))).NOT_EQ(Users.Email),
                    StringExp(COALESCE(Users.Role, StringOrNull(role))).NOT_EQ(Users.Role),
                ),
            ).THEN(NOW()).
                ELSE(Users.LastUpdate),
        )),
).WHERE(
    Users.UsersID.EQ(Int(1)),
).RETURNING(
    Users.AllColumns,
)

But, this IMO wouldn't be a recommended approach. Query is complex, you are constantly overriding unchanged data, and if there are no data to update you are still going to ping a database. The recommended approach would be to move procedural code out of SQL:

var columnsToUpdate ColumnList
var modelToUpdate model.Users

if name != nil {
    columnsToUpdate = append(columnsToUpdate, Users.Name)
    modelToUpdate.Name = *name
}
if email != nil {
    columnsToUpdate = append(columnsToUpdate, Users.Email)
    modelToUpdate.Email = *email
}
.....

if len(columnsToUpdate) == 0 {
    return 
}

stmt2 := Users.UPDATE(columnsToUpdate).
    MODEL(modelToUpdate).
    WHERE(Users.ID.EQ(Int(1))).
    RETURNING(columnsToUpdate) // or Users.AllColumns()

[darylhjd]

thanks for the example! the recommended approach was exactly what i was after. looks like theres a bunch of stuff i have still yet to find out from jet like ColumnList and StringExp... will be some time before i get hang of this. thanks!

for the update_at field, i decided to use a trigger instead.