How to use the same column multiple times in the where hash in Qore's SqlUtil

Question

How to use a column multiple times in the where hash in Qore's SqlUtil ?

Example SQL: colname in (...) and colname not in (...)

Here a where hash would look like:

hash sh = ('where': (
              'colname': op_in(...),
              'colname': op_not(op_in(...)),
          ));

Of course the same key cannot be used more than once in a hash.

Answer 1

This is possible - see: https://docs.qore.org/current/modules/SqlUtil/html/sql_operations.html#where_clauses

from the docs:

To reference a column more than once in a where clause, prefix the column specification with a unique number and a colon as in the following example:

hash w = ("0:created": op_ge(mindate), "1:created": op_lt(maxdate)); 

the numeric prefix as in the example above (along with the colon) is removed when generating the query and is used only to allow for the same column name to appear more than once in the generated query.

Your example could look like:

hash sh = (
    "where": (
        "0:colname": op_in(...),
        "1:colname": op_not(op_in(...)),
    ),
);