Partial queries with optional<T>

Clearer interfaces with optional<T> showed what optional<T> was, and how to make interfaces clearer by using it as a return type. If you’re not familiar with optional, I would recommend you to read it first.

Here I present a example of using optional as an argument, to leverage on its “not set” semantic. The need we’ll work on here is to build several variations of an SQL query, with different parameters in the ‘where’ clause. optional makes for a clear and precise interface for doing this. This example has been adapted from real production code.

Let’s take the example of a database table referencing persons, containing amongst others their gender, nationality and eye color. We would like to be able to build various SQL queries, on various criteria:

• gender and eye color,
• gender and nationality,
• nationality only,
• all 3 at the same time,
• and so on.

As a first attempt we could write a function like the following:

std::string buildQuery(const std::string& gender,
                       const std::string& nationality,
                       const std::string& eyeColor)
{
    std::ostringstream oss;
    oss << "SELECT * FROM persons ";
    oss << "WHERE gender = '" << *gender << "' ";
    oss << "AND nationality = '" << *nationality << "' ";
    oss << "AND eyeColor = '" << *eyeColor << "'";

    return oss.str();
}

and pass the empty string “” for the criteria we want to ignore. For instance to query all blue-eyed males regardless of their nationality, we would call the function this way:

buildQuery("male", "", "blue");

But this has a bug: the generated SQL request will query only entries that have a nationality equal to “”, and miss all blue-eyed males that have some nationality set in the database.

So, you might think, the fix is easy: the function buildQuery can just ignore fields for which the value passed is the empty string “”:

std::string buildQuery(const std::string& gender,
                       const std::string& nationality,
                       const std::string& eyeColor)
{
    std::ostringstream oss;
    oss << "SELECT * FROM persons ";

    if (gender != "")
        oss << "WHERE gender = '" << *gender << "' ";
    if (nationality != "")
        oss << "AND nationality = '" << *nationality << "' ";
    if (eyeColor != "")
        oss << "AND eyeColor = '" << *eyeColor << "'";

    return oss.str();
}

But there is still a bug, subtler than the previous one: maybe the intention of the caller was to query the entries that really have an empty nationality ! With this interface, there is no way for the caller to express its intention precisely.

This is where optional comes into play.

optional has one special value, ‘none’, that means that no value is set. All other values, including “”, are considered to be meaningful and will be requested by the query.

Here is the implementation using optional:

 

std::string buildQuery(const boost::optional<std::string>& gender,
                       const boost::optional<std::string>& nationality,
                       const boost::optional<std::string>& eyeColor)
{
    std::ostringstream oss;
    oss << "SELECT * FROM persons ";

    if (gender)
        oss << "WHERE gender = '" << *gender << "' ";
    if (nationality)
        oss << "AND nationality = '" << *nationality << "' ";
    if (eyeColor)
        oss << "AND eyeColor = '" << *eyeColor << "'";

    return oss.str();
}

And at call site:

std::string gender = "male";
std::string eyeColor = "blue";
buildQuery(gender, boost::none, eyeColor);

 

The above call constructs the following SQL query:

SELECT * FROM persons WHERE gender = 'male' AND eyeColor = 'blue'

 

Here the call to buildQuery conveniently queries all persons with male gender and blue eyes, regardless of their nationality. Do you see how this is different from asking for a nationality equal to “”, like in the following ?

std::string gender = "male";
std::string nationality = ""
std::string eyeColor = "blue";
buildQuery(gender, nationality, eyeColor);

 

Actually there is one last thing to fix in the above implementation of buildQuery: if you don’t specify the first parameter (gender) the word “where” will not be in the query. The code has to be slightly modified to work in all cases.

One simple solution that has been proposed to me by Nemanja Boric is to add a WHERE 1=1 at the beginning of the query in all cases:

std::string buildQuery(const boost::optional<std::string>& gender,
                       const boost::optional<std::string>& nationality,
                       const boost::optional<std::string>& eyeColor)
{
    std::ostringstream oss;
    oss << "SELECT * FROM persons WHERE 1=1 ";

    if (gender)
        oss << "AND gender = '" << *gender << "' ";
    if (nationality)
        oss << "AND nationality = '" << *nationality << "' ";
    if (eyeColor)
        oss << "AND eyeColor = '" << *eyeColor << "'";

    return oss.str();
}

Or you can still keep the original request but the implementation gets a little more complicated:

void addFieldToQuery(std::ostringstream& oss, const std::string& field,
const boost::optional<std::string>& value, bool& first)
{
    if (value)
    {
        oss << (first ? "where " : "and ") << field << " = '" << *value << "' ";
        first = false;
    }
}

std::string buildQuery(const mbl::optional<string>& gender,
                       const mbl::optional<string>& nationality,
                       const mbl::optional<string>& eyeColor)
{
    std::ostringstream oss;
    oss << "SELECT * FROM persons ";

    bool first = true;
    addFieldToQuery(oss, "gender", gender, first);
    addFieldToQuery(oss, "natonality", nationality, first);
    addFieldToQuery(oss, "eyeColor", eyeColor, first);

    return oss.str();
}

 

 

Related articles:

• Clearer interfaces with optional<T>

Don't want to miss out ? Follow:   
Share this post!