SQL search multiple values in same field

Question

I have a list of id's to search. I want to call all data matching the values in this list. Matching data is returned as a list.

I tried to do this in a loop and using IN but it didn't work.

When I write the values in parentheses one by one, it gives results, but how can I give the data in the list as a parameter?

db.query(_tableName, where: "id IN ('0001','00002','00003')")  

When I type in the form, the query takes place. Can I send the incoming array directly into parentheses, not one by one? For example,

 db.query(_tableName, where: "id IN (_ids)")

Query method:

Future<List<ContentModel>> getDiscoverContent(List<String> _ids) async {
    Database db = await instance.database;

    var contents = await db.query(_tableName, where: "id IN ('')");

    List<ContentModel> _recommendedList = contents.isNotEmpty
        ? contents.map((e) => ContentModel.fromMap(e)).toList()
        : [];

    return _recommendedList;
  }

The simplest solution I found:

var contents = await db.query(_tableName,
        where:
            "id IN('${_ids[0]}','${_ids[1]}','${_ids[2]}','${_ids[3]}','${_ids[4]}',"
                "'${_ids[5]}','${_ids[6]}','${_ids[7]}','${_ids[8]}','${_ids[9]}')");

If you have a different solution, feel free to tell me.

Answer 1

You may try like this. This worked for my use case.

List<int> _ids = [1, 2, 3, 4];
 
// Loop through the _ids list and append quotes to each id
// add the element into a new List called new_Ids

List<String> new_Ids = [];

for (var i = 0; i < _ids.length; i++) {
  new_Ids.add("'" + _ids[i].toString() + "'");
}

// use join to make a string from the new list where ids are separated by comma

String ids_Str = new_Ids.join(',');

Use db.query and pass ids string in where itself. Other columns in where condition can be passed in whereArgs if you need.

var resultList = await db.query('your_table_name',
            columns: ['id', 'column2'],
            where: 'id IN ($ids_Str)');

OR use rawQuery

var resultList = await db.rawQuery("SELECT id, column2 FROM your_table_name WHERE id IN(" + ids_Str + ")");

You may need to be careful of SQL Injection as we are passing dynamic values. Not sure about the best practices here.