sqlWhereBy function

String sqlWhereBy(Map<String, dynamic> whereValues, [ String? append ])

Returns the where criteria (without where) by concatenating all values found in whereValues with and.

Each key will be enclosed with a pair of double quotations, such as foo => "foo". However, if it starts with a number or contains ( or ", it'll be output directly. In other words, it is considered as an expression.

If a value in whereValues is null, it will generate "name" is null. Furthermore, you can use inList, notIn, and notNull to generate more sophisticated conditions. For example,

{
  "foo": inList(['a', 'b']),
  "moo": notIn([1, 5]),
  "boo": notNull,
  "qoo": null,
  "xoo": not(90),
}

Furthermore, you can put the order-by and limit clause in the key with empty value. For example,

{
  "foo": foo,
  "": 'order by value desc limt 5',
}

Implementation

String sqlWhereBy(Map<String, dynamic> whereValues, [String? append]) {
  final cvter = _pool!.typeConverter,
    sql = StringBuffer();
  var first = true;
  whereValues.forEach((name, value) {
    if (name.isEmpty) { //value is a SQL fragment to append
      assert(value is String);
      append = append == null ? value.toString(): '$value $append';
      return;
    }

    if (first) first = false;
    else sql.write(' and ');

    bool negate;
    if (negate = value is NotCondition) value = value.value;

    if (value is InCondition) {
      value = value.value;
      if (value == null || value.isEmpty) {
        sql.write(negate ? 'true': 'false');
        return;
      }

      _appendField(sql, name);
      if (negate) sql.write(' not');
      sql.write(' in (');

      var first = true;
      for (final item in value) {
        if (first) first = false;
        else sql.write(',');
        sql.write(cvter.encode(item, null));
      }
      sql.write(')');
      return;
    }

    _appendField(sql, name);
    if (value is LikeCondition) {
      if (negate) sql.write(' not');
      sql.write(' like ');

      //#10: don't encode again if escape is specified
      //i.e., assume pattern is encoded properly.
      final escape = value.escape;
      if (escape != null)
        sql..write("E'")..write(value.pattern)
          ..write("' escape '")..write(escape)..write("'");
      else
        sql.write(cvter.encode(value.pattern, null));

    } else if (value != null) {
      if (negate) sql.write('!');
      sql..write('=')..write(cvter.encode(value, null));

    } else {
      sql.write(' is ');
      if (negate) sql.write("not ");
      sql.write('null');
    }
  });

  if (append != null) sql..write(' ')..write(append);
  return sql.toString();
}