Select multiple data from SQLite database based on multiple tags

Question

I have an SQLite database with some to do lists. Each to do list, there is a column for tag.

Here is a sample layout of my SQLite database

Let's say I want to select the to do lists with tags containing "Me" and "Mom" (ie "Buy groceries", "Study" and " Holiday trip"), how do I do this?

I have consider this, but I dont think it works.

public   getToDoLists (String [] tags) {
    this.db = openHelper.getReadableDatabase();
    String query = " SELECT * FROM "
            + TABLE_NAME
            + " WHERE "
            + COL2 + " ='" + tags + "'";
    c = db.rawQuery(query, null);  
    .......
    .......
 } 

Answer 1

This is not a simple query.
You are passing the tags that you search for as a String array.
One option is to use a for loop which will iterate all the tags of the array and for each tag run a query, but this is bad for performance.

The option that I suggest is to join all the tags separated with , in a string and pass it to a query which in turn will split it with a recursive CTE and run a SELECT... statement with EXISTS... to fetch the matching rows:

public Cursor getToDoLists(String[] tags) {
    String joinedTags = TextUtils.join(",", tags);

    String query =
            "WITH " +
            "  list(tag) AS (SELECT ?), " +
            "  cte(tag, value) AS ( " +
            "    SELECT SUBSTR(tag, 1, INSTR(tag || ',', ',') - 1), " +
            "           SUBSTR(tag, INSTR(tag || ',', ',') + 1) " +
            "    FROM list " +
            "    UNION ALL " +
            "    SELECT SUBSTR(value, 1, INSTR(value || ',', ',') - 1), " +
            "           SUBSTR(value, INSTR(value || ',', ',') + 1) " +
            "    FROM cte " +
            "    WHERE LENGTH(value) > 0 " +
            ") " +
            "SELECT t.* " +
            "FROM " + TABLE_NAME + " t " +
            "WHERE EXISTS ( " +
            "  SELECT 1 FROM cte c " +
            "  WHERE ',' || REPLACE(t.Tags, ', ', ',') || ',' LIKE '%,' || c.tag || ',%' " +
            ")";

    SQLiteDatabase db = this.getReadableDatabase();

    return db.rawQuery(query, new String[] {joinedTags});
}

I use REPLACE(t.Tags, ', ', ',') in the sql statement because I see that the separator of the tags in the column Tags is ', ' and not ','.
If I'm mistaken then change REPLACE(t.Tags, ', ', ',') with just t.Tags.

The method getToDoLists() must be placed inside your SQLiteOpenHelper class and you can call it from your activity class like:

Cursor c = openHelper.getToDoLists(new String[] {"Me", "Mom"});

and it returns a Cursor with the rows matching your criteria, which you can iterate.

To use TextUtils.join() you will need this import:

import android.text.TextUtils;

See a demo of how this works in SQLite.