How can I handle dynamic query when I'm updating and some fields are not required?-CodePudding

Here's an example of what I mean:

func (s *Store) UpdateOrganization(entity *Organization) (*Organization, error) {
    org := &Organization{}
    stmt := `
        UPDATE public.organizations
        SET name = $1,
            website = $2,
            image_url = $3
        WHERE id = $4
        RETURNING *;`

    rows, _ := s.db.Query(
        context.Background(),
        stmt,
        entity.Name,
        entity.Website,
        entity.ImageURL,
        entity.ID,
    )

    if err := pgxscan.ScanOne(org, rows); err != nil {
        return nil, newStoreError(err)
    }

    return org, nil
}

None of these fields (name, website, image_url) are required (PATCH update), so the entity generated from UpdateOrganizationDTO might have only name = "george jungle" with the other fields (website, image_url) as nil.

How can I handle such a case? Otherwise, I will overrwite existing fields in the DB with null values.

One thing I've been trying (WIP, and not sure yet if this is a good approach esp when I have way more complicated querys) is creating a map map[string]string that will map non nil values to their column names.

I tried using conditionals as mentioned in comment below, now I'm wondering how do I pass the values to Query()?

setClauses := []string{}


if entity.Name != nil {
    setClauses = append(setClauses, "name = $2")
}

if entity.Website != nil {
    setClauses = append(setClauses, "website = $3")
}

if entity.ImageURL != nil {
    setClauses = append(setClauses, "image_url = $4")
}

setClause := ""

stmt := fmt.Sprintf(`
    UPDATE public.organizations
    SET %s
    WHERE id = $1
    RETURNING *;`, setClause)

rows, _ := s.db.Query(
    context.Background(),
    stmt,
    entity.ID,
////// how to handle these VVVVV
    entity.Name,
    entity.Website,
    entity.ImageURL,
)

CodePudding user response：

I think I made it work.......

Please someone tell me if there's a better way

func (s *Store) UpdateOrganization(entity *Organization) (*Organization, error) {
    org := &Organization{}

    setClauses := []string{}
    var queryArgs []interface{}

    queryArgs = append(queryArgs, entity.ID)

    if entity.Name != nil {
        setClauses = append(setClauses, "name = $2")
        queryArgs = append(queryArgs, entity.Name)
    }

    if entity.Website != nil {
        setClauses = append(setClauses, "website = $3")
        queryArgs = append(queryArgs, entity.Website)
    }

    if entity.ImageURL != nil {
        setClauses = append(setClauses, "image_url = $4")
        queryArgs = append(queryArgs, entity.ImageURL)
    }

    setClause := strings.Join(setClauses, ",")

    stmt := fmt.Sprintf(`
    UPDATE public.organizations
    SET %s
    WHERE id = $1
    RETURNING *;`, setClause)

    rows, _ := s.db.Query(
        context.Background(),
        stmt,
        queryArgs...,
    )

    if err := pgxscan.ScanOne(org, rows); err != nil {
        return nil, newStoreError(err)
    }

    return org, nil
}

CodePudding user response：

You can do the following:

func (s *Store) UpdateOrganization(entity *Organization) (*Organization, error) {
    org := &Organization{}
    stmt := `
        UPDATE public.organizations
        SET name = COALESCE($1, name),
            website = COALESCE($2, website),
            image_url = COALESCE($3, image_url)
        WHERE id = $4
        RETURNING *;`

    rows, _ := s.db.Query(
        context.Background(),
        stmt,
        entity.Name,
        entity.Website,
        entity.ImageURL,
        entity.ID,
    )

    if err := pgxscan.ScanOne(org, rows); err != nil {
        return nil, newStoreError(err)
    }

    return org, nil
}

However, personally I think the correct default approach (exceptions aside), would be to retrieve the complete object from the db; then in Go modify those fields that the user provided; and then store the object as is.