matching multiple title in single query using like keyword

Question

matching multiple title in single query using like keyword

I am trying to get all records if that matches with given titles.

below is the structure of database please see database screenshot

when i pass single like query it returns data

 @Query("SELECT * FROM task WHERE task_tags LIKE '%\"title\":\"Priority\"%'")

when i try to generate query dynamically to search multiple match it return 0 data

    val stringBuilder = StringBuilder()
    for (i in 0 until tags.size) {
        val firstQuery = "%\"title\":\"Priority\"%"
        if (i == 0) {
            stringBuilder.append(firstQuery)
        } else stringBuilder.append(" OR '%\"title\":\"${tags[i].title}\"%'")

    }

this is function I have made

  @Query("SELECT * FROM task WHERE task_tags LIKE:tagQuery ")
fun getTaskByTag(stringBuilder.toString() : String): List<Task>

Answer 1

The single data is fine. However, you simply cannot use the second method.

First you are omitting the space after LIKE,

Then you are omitting the full test i.e. you have task_ tags LIKE ? OR ?? when it should be task_tags LIKE ? OR task_tags LIKE ?? ....

And even then, due to the way that a parameter is handled by room the entire parameter is wrapped/encased as a single string, so the OR/OR LIKE's all become part of what is being searched for as a single test.

• The correct solution, as least from a database perspective, would be to not have a single column with a JSON representation of the list of the tags, but to have a table for the tags and then, as you want a many-many relationship (a task can have many tags and a single tag could be used by many tasks) an associative table and you could then do the test using a IN clause.

As a get around though, you could utilise a RawQuery where the SQL statement is built accordingly.

As an example:-

@RawQuery
fun rawQuery(qry: SimpleSQLiteQuery): Cursor
@SuppressLint("Range")
fun getTaskByManyTags(tags: List<String>): List<Task> {
    val rv = ArrayList<Task>()
    val sb=StringBuilder()
    var afterFirst = false
    for (tag in tags) {
        if (afterFirst) {
            sb.append(" OR task_tags ")
        }
        sb.append(" LIKE '%").append(tag).append("%'")
        afterFirst = true
    }
    if (sb.isNotEmpty()) {
        val csr: Cursor = rawQuery(SimpleSQLiteQuery("SELECT * FROM task WHERE task_tags $sb"))
        while (csr.moveToNext()) {
            rv.add(
                Task(
                    csr.getLong(csr.getColumnIndex("tid")),
                    csr.getString(csr.getColumnIndex("task_title")),
                    csr.getString(csr.getColumnIndex("task_tags"))))
                    // other columns ....
        }
        csr.close()
    }
    return rv
}

• Note that the complex string with the embedded double quotes is, in this example, passed rather than built into the function (relatively simple change to incorporate) e.g. could be called using

  • val tasks1 = taskDao.getTaskByManyTags(listOf()) would return no tasks (handling no passed tags something you would need to decide upon)
  • val tasks2 = taskDao.getTaskByManyTags(listOf("\"title\":\"Priority\""))
  • val tasks3 = taskDao.getTaskByManyTags(listOf("\"title\":\"Priority\"","\"title\":\"Priority\"","\"title\":\"Priority\"")) obviously the tags would change

Very limited testing has been undertaken (hence just the 3 columns) but the result of running all 3 (as per the above 3 invocations) against a very limited database (basically the same row) results in the expected (as per breakpoint):-

• the first returns the empty list as there are no search arguments.
• the second and third both return all 4 rows as "title":"Priority" is in all 4 rows
  • the main reason for the 3 search args was to check the syntax of multiple args, rather than whether or not the correct selections were made.

The resultant query of the last (3 passed tags) being (as extracted from the getTaskaByManyTags function):-

SELECT * FROM task WHERE task_tags  LIKE '%"title":"Priority"%' OR task_tags  LIKE '%"title":"Priority"%' OR task_tags  LIKE '%"title":"Priority"%'