sqflite IN operator with AND operator

3
votes

I have following query which select from employee table where name is "max" and ID not in 123 and 444. Not in IDs can grow in future. But I am receiving error as

Error

( 8023): [ERROR:flutter/lib/ui/ui_dart_state.cc(148)] Unhandled Exception: DatabaseException(near "?": syntax error (code 1 SQLITE_ERROR): , while compiling:

Query

     List<String> a = [];
        a.add("123");
        a.add("444");

        var table = await mydb.rawQuery(
            "SELECT value from employee  WHERE employeename = ? AND id NOT IN ? ORDER BY timestamp DESC",
            ["max", a]);
2
flutterdartsqflite
This is in flutter, Which uses Dart Language. – max
Yes i got that. Is the LIST value fixed? I mean is it 2 always? If so,one way is that the parameter (?) should match no of values. something like ..NOT IN (?,?) ... and put the values in whereArgs: a or you have to join the values with ,..something like id IN (${a.join(', ')}) – Arun Palanisamy
List of values will be changing, Now 2 but in future can be 5 , 6. depend on scenario – max

2 Answers

7
votes

If the LIST is unpredictable, one way is that you can use JOIN to create your select statement with required value of NOT IN. Below is one sample.

void main() {
  List<String> a = [];
  a.add("123");
  a.add("444");

  var select =
      'SELECT value from employee  WHERE employeename = ? AND id NOT IN (\'' +
          (a.join('\',\'')).toString() +
          '\') ORDER BY timestamp DESC';

  var table = await mydb.rawQuery(select, ["max"]);
}

If you print the variable select you will get 

SELECT value from employee  WHERE employeename = ? AND id NOT IN ('123','444') 
ORDER BY timestamp DESC.

Then you can pass the above statement to rawquery and get your result.

P.S:Use your single quote and double quote accordingly.

2
votes

I'd go for @arun-palanisamy 's solution, see his comment. Props go to him. I just tried the following -- with Groovy/Postgres, but the error seems to be the same, so you might want to give it a try:

String[] a = ['123', '444']

// your code, throws 'ERROR: syntax error at or near "$2"':
// def table = sql.execute("SELECT value from employee WHERE employeename = ? AND id NOT IN ? ORDER BY timestamp DESC", ["max", a])

// code of arun-palanisamy, compiles:
def table = sql.execute("SELECT value from employee WHERE employeename = ? AND id NOT IN (${a.join(', ')}) ORDER BY timestamp DESC", ["max", a])

Side notes:

  • You might want to try a different type for a, such as Array in my code, or even a HashMap.
  • There are examples (like here) where the number of ? are generated dynamically.

Update: Go for this answer, we posted simultaneously