Select multiple data from SQLite database based on multiple tags

user_10238202 0
Viewed 330

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

enter image description here

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);  
    .......
    .......
 }
android-sqlite sqlite java
2 Answers

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.

user_104988280

You can use regexp for that.

Note, that regexp function is not provided by default, https://sqlite.org/lang_expr.html#regexp:

No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)".

You can load regexp function with ICU extension: https://sqlite.org/src/artifact?ci=trunk&filename=ext/icu/README.txt. With that:

select
  "To do list"
from table

where 1==1
and (    "Tags" regexp '.*\bMom\b.*'
      or "Tags" regexp '.*\bMe\b.*'  )

ICU regexp syntax reference: https://unicode-org.github.io/icu/userguide/strings/regexp.html#regular-expression-metacharacters

user_7887000
Related