Android room: query list items against string column

Question

I have an list of strings:

val mylist = listOf("cat","flower")

and a table that has a string typed column named question I can write the query to find questions that are exactly matched with one of list items:

@Query("SELECT * FROM objects WHERE question IN (:mylist)")
List<Object> queryObjects(List<String> mylist);

But in fact the question column data is not of single word type, but string. I need to find results that every one of the list items are in that strings .for example the record : is this a cat

Answer 1

The use of IN is basically an = test of the expression on the the left of the IN clause against the list of values on the right. That is only exact matches are considered.

However, what you want is multiple LIKE's with wild characters, and an OR between each LIKE e.g question LIKE '%cat%' OR question LIKE '%flower%' or perhaps CASE WHEN THEN ELSE END or perhaps a recursive common table expression (CTE).

The former two (LIKEs or CASEs) would probably have to be done via an @RawQuery where the LIKE/CASE clauses are built at run time.

The Recursive CTE option would basically build a list of words (but could get further complicated if, anything other than spaces, such as punctuation marks were included.)

Another option could be to consider Full Text Search (FTS). You may wish to refer to https://www.raywenderlich.com/14292824-full-text-search-in-room-tutorial-getting-started

Working Example LIKE's

Here's an example of implementing the simplest, multiple LIKEs clauses separated with ORs:-

Objects (the Entity):-

@Entity
data class Objects(
    @PrimaryKey
    val id: Long? = null,
    val question: String
) 

AllDAO (the Daos):-

@Dao
interface AllDAO {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(objects: Objects)

    @RawQuery
    fun getObjectsRawQuery(query: SupportSQLiteQuery): List<Objects>

    fun getObjects(values: List<String>): List<Objects> {
        var i = 0
        val sb = StringBuilder().append("SELECT * FROM objects WHERE ")
        for(v in values) {
            if (i++ > 0) {
                sb.append(" OR ")
            }
            sb.append(" question LIKE '%${v}%'")
        }
        sb.append(";")
        return getObjectsRawQuery(SimpleSQLiteQuery(sb.toString()))
    }
}

TheDatabase (not uses .allowMainThreadQueries for convenience and brevity):-

@Database(entities = [Objects::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAO(): AllDAO

    companion object {
        var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

Putting it all together, loading some test data and running some extracts:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDAO
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAllDAO()

        dao.insert(Objects(question = "This is a cat."))
        dao.insert(Objects(question = "This is a flower."))
        dao.insert(Objects(question = "this is nothing."))
        dao.insert(Objects(question = "The quick brown fox jumped over the lazy dog"))

        logObjects(dao.getObjects(listOf("cat","dog")),"Extract1\t")
        logObjects(dao.getObjects(listOf("flower","cat")),"Extract2\t")
        logObjects(dao.getObjects(listOf("brown","nothing")),"Extract3\t")
    }

    fun logObjects(objects: List<Objects>,prefix: String) {
        for (o in objects) {
            Log.d("OBJECTINFO","$prefix Question is ${o.question} ID is ${o.id}")
        }
    }
}

Result

2022-04-18 04:58:05.471 D/OBJECTINFO: Extract1   Question is This is a cat. ID is 1
2022-04-18 04:58:05.471 D/OBJECTINFO: Extract1   Question is The quick brown fox jumped over the lazy dog ID is 4

2022-04-18 04:58:05.473 D/OBJECTINFO: Extract2   Question is This is a cat. ID is 1
2022-04-18 04:58:05.473 D/OBJECTINFO: Extract2   Question is This is a flower. ID is 2

2022-04-18 04:58:05.474 D/OBJECTINFO: Extract3   Question is this is nothing. ID is 3
2022-04-18 04:58:05.474 D/OBJECTINFO: Extract3   Question is The quick brown fox jumped over the lazy dog ID is 4

• Note in the above no consideration has been given to handling an empty list (a failure would occur due to the syntax error of SELECT * FROM objects WHERE ;). That is the example is just intended to demonstrate the basic principle.