[Feature] Support for `NOT condition` QueryMod

[JMounier]

I was looking for a way to negate a whole groupe of QueryMod while building a request but I couldn't find any call equivalent to qm.Or2 which would apply a not to its given qm.QueryMod.

Syntax

the NOT condition syntax seems to be supported by most DBMS :

• https://docs.microsoft.com/en-us/sql/t-sql/language-elements/not-transact-sql?view=sql-server-ver15
• https://www.postgresql.org/docs/9.1/functions-logical.html
• https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html

How To

NOT can be handle exactly as OR is handled. Meaning having a SetLastWhereAsNot and setting a boolean (such as notSeparator) in the where structure.

The last modification would be in the query_builder as follow :

// whereClause parses a where slice and converts it into a
// ...
// startAt specifies what number placeholders start at
func whereClause(q *Query, startAt int) (string, []interface{}) {
    ...
    notFirstExpression := false
    buf.WriteString(" WHERE ")
    for _, where := range q.where {
        if where.kind != whereKindRightParen {
            if notFirstExpression {
                if where.orSeparator {
                    buf.WriteString(" OR ")
                } else {
                    buf.WriteString(" AND ")
                }
            }
            if where.notSeparator {
                buf.WriteString(" NOT ")
            }
        } else
    ...
}

I hope this feature is useful enough to make it to production.

[aarondl]

I'm indifferent. We could add this so long as it plays nicely with -everything- and every DBMS. Keep in mind MariaDB/sqlite3/cockroachdb are unofficially part of that list.