Skip to content

operators.md

Latest commit

 

History

History
224 lines (169 loc) · 3.53 KB

operators.md

File metadata and controls

224 lines (169 loc) · 3.53 KB

< Differences

Before and after: Dynamic queries

CASE

case_when now supports multiple when/then pairs.

sqlpp11sqlpp23 
// Simulating multiple when/then pairs with
// nested case_when expressions.
case_when(cond)
    .then(val)
    .else_(case_when(cond2)
               .then(val2)
               .else_(val3));
 
case_when(cond)
    .then(val)
    .when(cond2)
    .then(val2)
    .else_(val3);

Data types in then and else_ now must be the same (plus/minus optional).

sqlpp11sqlpp23 
case_when(cond)
    .then(17)     // integral
    .else_(17.4); // float is comparable.
                  // Overall type: integral.
 
case_when(cond)
    .then(17)     // integral
    .else_(17.4); // Compile error.
                  // float is not integral.

std::nullopt is now allowed as then argument as long as either another then argument or the else_ argument has a data type.

sqlpp11sqlpp23 
// Need to invert condition and move the
// `sqlpp::null` to the `else_`
case_when(cond)
    .then(sqlpp::null) // Compile error.
    .else_(17.4);
 
case_when(cond)
    .then(std::nullopt) // This is fine
    .else_(17.4); // Overall data type:
                  // optional<floating_point>

IN / NOT IN

in and not_in remain unchanged for discrete values, e.g. x.in(1, 2, 3) or x.in(my_tuple), but using std::vector has become simpler.

sqlpp11sqlpp23 
const auto values = std::vector<int>{1, 2, 3};

x.in(sqlpp::value_list(values));

x.not_in(sqlpp::value_list(values));
 
const auto values = std::vector<int>{1, 2, 3};

x.in(values);

x.not_in(values);

IS DISTINCT FROM and friends

Note

Different databases have different ways of spelling IS DISTINCT FROM. The connectors serialize appropriately.

sqlpp11sqlpp23

DISTINCT FROM

not is_equal_to_or_null(
    foo.textN,
    sqlpp::value_or_null("SQL"));

not is_equal_to_or_null(
    foo.textN,
    sqlpp::value_or_null<sqlpp::text>(
        sqlpp::null));

// Cannot be used with parameters.
 
foo.textN.is_distinct_from("SQL");

foo.textN.is_distinct_from(std::nullopt);

// Can be used with parameter
foo.textN.is_distinct_from(parameter(foo.textN));

NOT DISTINCT FROM

is_equal_to_or_null(
    foo.textN,
    sqlpp::value_or_null("SQL"));

is_equal_to_or_null(
    foo.textN,
    sqlpp::value_or_null<sqlpp::text>(
        sqlpp::null));

// Cannot be used with parameters.
 
foo.textN.is_not_distinct_from("SQL");

foo.textN.is_not_distinct_from(std::nullopt);

// Can be used with parameter
foo.textN.is_not_distinct_from(
    parameter(foo.textN));

< Differences