Is this way of filtering data in ROOM database wrong?

Question

I have a todo list app in which i utilized the ROOM database. also this app has some filtering features so the user can filter the list based on title, priority or description. I want to know if the way i implemented filtering in my viewModel is efficient or not? and if it isn't what is the best way to do so?

my DAO:

@Dao
interface ToDoDao {

    @Insert(ToDoData::class, onConflict = OnConflictStrategy.IGNORE)
    suspend fun insert(toDoData: ToDoData)

    @Delete(ToDoData::class)
    suspend fun delete(item: ToDoData)

    @Update(ToDoData::class,onConflict = OnConflictStrategy.IGNORE)
    suspend fun update(item: ToDoData)

    @Query("DELETE FROM TODO_TABLE")
    suspend fun deleteAllData()

    @Query("SELECT * FROM TODO_TABLE ORDER BY id ASC ")
    fun getAllData():LiveData<List<ToDoData>>


    @Query("SELECT * FROM TODO_TABLE ORDER BY CASE WHEN priority LIKE 'H%' THEN 1 WHEN priority LIKE 'M%' THEN 2 WHEN priority LIKE 'L%' THEN 3 END")
    fun getAllDataSortedByHighPriority():LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE ORDER BY CASE WHEN priority LIKE 'L%' THEN 1 WHEN priority LIKE 'M%' THEN 2 WHEN priority LIKE 'H%' THEN 3 END")
    fun getAllDataSortedByLowPriority():LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE WHERE title LIKE '%' || :titleContains || '%' AND priority IN (:prioritiesIn) AND description LIKE '%' || :descContains || '%' ORDER BY ID ASC")
    fun filterDataSortedByID(titleContains: String, prioritiesIn:Array<Priority>,descContains:String):LiveData<List<ToDoData>>

}

my repository:

class ToDoRepository(private val dao: ToDoDao) {

    val allData: LiveData<List<ToDoData>> = dao.getAllData()
    val allDataSortedByHighPriority = dao.getAllDataSortedByHighPriority()
    val allDataSortedByLowPriority = dao.getAllDataSortedByLowPriority()


    suspend fun insert(item: ToDoData) {
        dao.insert(item)
    }

    suspend fun delete(item: ToDoData) {
        dao.delete(item)
    }

    suspend fun update(item: ToDoData) {
        dao.update(item)
    }

    suspend fun deleteAll(){
        dao.deleteAllData()
    }

    fun filterDataSortedByID(titleContains: String, prioritiesIn:Array<Priority>,descContains:String):LiveData<List<ToDoData>>{
        return dao.filterDataSortedByID(titleContains,prioritiesIn,descContains)
    }

}

my viewModel:

class ToDoViewModel(context: Application) : AndroidViewModel(context) {

    private val toDoDao = ToDoDatabase.getDataBase(context.applicationContext).toDoDao()
    private val repository = ToDoRepository(toDoDao)

    private val filters: MutableLiveData<MutableMap<String, Any>> =
        MutableLiveData(
            mutableMapOf(
                "titleContains" to "",
                "prioritiesIn" to arrayOf(Priority.HIGH, Priority.MEDIUM, Priority.LOW),
                "descContains" to "",
            )
        )


    var titleContains: String
        get() = filters.value?.get("titleContains") as String
        set(value) {
            filters.value = filters.value?.apply {
                set("titleContains", value)
            }
        }
    var prioritiesIn: Array<Priority>
        get() = filters.value?.get("prioritiesIn") as Array<Priority>
        set(value) {
            filters.value = filters.value?.apply {
                set("prioritiesIn", value)
            }
        }
    var descContains: String
        get() = filters.value?.get("descContains") as String
        set(value) {
            filters.value = filters.value?.apply {
                set("descContains", value)
            }
        }


    val filteredData = filters.switchMap { _ ->
        if (titleContains.isEmpty() && descContains.isEmpty()) {
            repository.allData
        } else {
            repository.filterDataSortedByID(titleContains, prioritiesIn, descContains)
        }
    }


    fun insertData(item: ToDoData) {
        viewModelScope.launch {
            repository.insert(item)
        }
    }

    fun updateData(item: ToDoData) {
        viewModelScope.launch {
            repository.update(item)
        }
    }

    fun deleteData(item: ToDoData) {
        viewModelScope.launch {
            repository.delete(item)
        }
    }

    fun deleteAll() {
        viewModelScope.launch {
            repository.deleteAll()
        }
    }


}

Answer 1

Be careful of LIKE '%xyz%' - it requires a full table scan to get results as it can't use an index.

Take a look at FTS (Full-Text Search). Search for "Android Room FTS" and you'll find several articles and tutorials. This one looks pretty good but is a few years old.

FTS will also allow you to search across title and description (or any other columns) at once. That might be more flexible for the user, rather than requiring separate entries.

Your filters adds a good bit of complexity and indirection to trigger that switchMap to fetch the next round of results. You might want to consider simplifying the setup. (switchMap works, but it can be confusing, especially if you're not using the incoming value in your transform.)

For example, you could make titleContains and descContains be MutableLiveData and directly use MediatorLiveData. The drawback is that you'd need to use titleContains.value instead of just titleContains:

// Copyright 2023 Google LLC.
// SPDX-License-Identifier: Apache-2.0
class ToDoViewModel... {
    var titleContains = MutableLiveData("")
    var descContains = MutableLiveData("")

    val filteredData = MediatorLiveData<List<String>>().apply { 
        addSource(titleContains) { updateQuery() }
        addSource(descContains) { updateQuery() }
    }

    private fun updateQuery() =
        if (titleContains.value!!.isBlank() && descContains.value!!.isBlank()) {
            repository.allData
        } else {
            repository.filterDataSortedByID(titleContains.value!!, prioritiesIn, descContains.value!!)
        }
}

You could add facade properties to get rid of that limitation:

// Copyright 2023 Google LLC.
// SPDX-License-Identifier: Apache-2.0
class ToDoViewModel... {
    private var _titleContains = MutableLiveData("")
    private var _descContains = MutableLiveData("")
    var titleContains: String
        get() = _titleContains.value!!
        set(value) { _titleContains.value = value }
    var descContains: String
        get() = _descContains.value!!
        set(value) { _descContains.value = value }

    val filteredData = MediatorLiveData<List<String>>().apply {
        addSource(_titleContains) { updateQuery() }
        addSource(_descContains) { updateQuery() }
    }

    private fun updateQuery() =
        if (titleContains.isBlank() && descContains.isBlank()) {
            repository.allData
        } else {
            repository.filterDataSortedByID(titleContains, prioritiesIn, descContains)
        }
}

(Do something similar for prioritiesIn)