Room DAO with multiple non-obligatory filters

Question

In my app user can filter their data using multiple filters in every combination (apply just one, multiple, or none).

Before that, I only had one filter so every time it was applied, I was switching the DAO method. Now I have 6 filters so there are dozens of combinations so creating a method for every combination is impossible. I cannot also modify my database a lot, because it is already available to the users.

My current code looks like this:

@Query("SELECT id, name, date FROM UserData")
fun getAll(): DataSource.Factory<Int, UserItem> //no filters

@Query("SELECT id, name, date FROM UserData WHERE name LIKE '%' || :search  || '%'")
fun getAllFiltered(query: String): DataSource.Factory<Int, UserItem> //one filter applied

Is there a way to modify the query so that there is one method for all filter combinations?

Update:

This is my data class, which instances I would like to filter:

@Entity(tableName = "UserItem")
data class UserItem(

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    val id: Long? = null,

    @ColumnInfo(name = "created_at")
    val createdAt: Date,

    @ColumnInfo(name = "is_uploaded")
    val isUploaded: Boolean,

    @ColumnInfo(name = "name")
    val name: String,

    @ColumnInfo(name = "item_sum")
    val sum: Int = 0,

    @ColumnInfo(name = "tags")
    val tags: List<String> = listOf(),
)

I would like to filter/check numeric and boolean properties' equality, check whether list properties contain specified string. Basically, I would like to have the ability to filter everything I could. If it is not possible, I would be satisfied with at least some filters.

Answer 1

It depends if you're ok with having a somewhat complicated query, but here's what I would probably do. Create a method like this:

@Query("""
SELECT id, name, date FROM UserData WHERE 
(:nameQuery IS NULL OR name LIKE '%' || :nameQuery  || '%') AND
(:isUploaded IS NULL OR is_uploaded = :isUploaded) AND
(:sum IS NULL OR item_sum = sum)
""")
fun getAllFiltered(nameQuery: String?, isUploaded: Boolean?, sum: Int?
): DataSource.Factory<Int, UserItem>

Now just pass null as the parameters if there is no filter for that specific field.

I don't know how you are storing your List<> in the database, but perhaps you could do a search on that field just like a string (e.g. name field)

If you wanted to improve search speed even more, you could set up a FTS4 table for the text fields, and then join that table and run string filters with Match and other filters the way I have it here. (If you need to search special characters in FTS4 you have to set up tokenizers for the table)