Skip to content

Question: UPDATE with dynamic() #112

@CJCombrink

Description

@CJCombrink

Migrating from sqlpp11 we have a few places where we update a table given some optional updates that must be applied to the table.
Now trying to migrate to sqlpp23 I have a question regarding the notation and behavior of update and dynamic

Our sqlpp11 code looks as follow (more or less):

struct update_info
{
    boost::uuid uuid;
    std::optional<std::string> name        = std::nullopt;
    std::optional<std::string> description = std::nullopt;
};

// Do the update 
bool do_update(const update_info& update)
{
    constexpr gen::PublicDeploymentInfo t{};
    auto query = sqlpp::dynamic_update(connection, t)
                     .dynamic_set()
                     .where(t.uuid == to_string(update.uuid));

    if (update.name.has_value())
        query.assignments.add(t.name = *update.name);
    if (update.description.has_value())
        query.assignments.add(t.description = *update.description);

    // This is bad probably :P
    if (query.assignments._data._dynamic_assignments.empty()) 
        return false;
    ....
}

Now with sqlpp23 I came up with the following that works as expected:

auto query = sqlpp::update(t_info)
                     .set(dynamic(update.name.has_value(), t_info.name = update.name.value_or({})),
                          dynamic(update.description.has_value(), t_info.description = update.description.value_or({})))
                     .where(t_info.uuid == info.uuid);

Is that the indented or optimal way to handle this case?

Initially I have tried the following but it throws std::bad_optional_access since I can't call value() on the optional if it does not have a value

dynamic(update.name.has_value(), t_info.name = update.name.value())),

And the following clears my fields when they are not set (as expected) and the field does have a NULL option (which is awesome)

t_info.name = update.name

And less import: Is there a way to better handle the case where no fields are set except to check manually before building the query (which is fine, just asking)

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions