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.

I think you should specify what filters are there so we can consider the feasibility. — m0skit0, Feb 27 '21 at 14:09
@m0skit0 I updated the question, basically whatever is possible. — baltekg, Feb 27 '21 at 14:35
Since each filter might or not might be selected, imho it is more readable and simpler to filter this on Kotlin side after fetching the data. Maybe a first filtering done at the database, but in my experience maintaining complicated SQL queries is worse than actually filtering on the receiver side (if the performance drop can be dealt with, that is) — m0skit0, Feb 27 '21 at 15:37
You might consider [using `@RawQuery`](https://developer.android.com/reference/androidx/room/RawQuery) and assembling the SQL at runtime for this particular scenario. — CommonsWare, Feb 27 '21 at 17:01
@m0skit0 i was worried that this might be the solution. And yes, performance drop by doing so will cause some problems, that is why i was trying to solve that in a different way. — baltekg, Feb 27 '21 at 23:34

score 4 · Answer 1 · answered Mar 02 '21 at 20:57

4

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)

answered Mar 02 '21 at 20:57

TimB

493
3
16

How do you think your solution will perform compared to building raw queries at runtime? Cuz even though they woul be build at runtime, queries will be less complicated. – baltekg Mar 06 '21 at 11:23
I have not tested so I can not really say what the performance difference will be. Building the query at runtime would not worry me for that part, it does not take much of resources to do that. – TimB Mar 06 '21 at 17:22
And I don't know exactly how Sqlite processes the query. If they do it like modern SQL and programming languages do then I don't think the performance will be much different from building at runtime. Checking for "NULL IS NULL" should not be much of a job (when parameter is null and should not be checked for example), and when that's the case then the part after OR is not being checked at all. So that's my guess, but have not done performance testing with these cases. – TimB Mar 06 '21 at 17:26

score 2 · Answer 2 · answered Mar 06 '21 at 11:23

2

You can use the complicated Query mentioned by Timb here but there is a lot of null checks (complex where clauses affect performance of query), I would use RawQuery which is used for dynamic query. although it lacks the syntax highlighting that @Query provides. This Link provides a sample. and if you use LiveData, add observedEntities property of @RawQuery annotation for entities that are important.

answered Mar 06 '21 at 11:23

mohsen sameti

381
1
8

That is what I was worried about that queries might be really complex. I posted another answer which does pretty much whay you wrote and I works just fine and is scalable which is a big advantage. – baltekg Mar 06 '21 at 11:40

score 2 · Accepted Answer · answered Mar 06 '21 at 11:36

Based on a comment from @CommonsWare, I tried to use RawQuery to achieve what I wanted.

First think to do was to create Filters data class that in the future will hold all the filters. It is really easy to remove one or add more.

data class Filters(
    val query: String? = null,
    val isUploaded: Boolean? = null,
    // all the other filters
)

The function that will build the query and return the result from the db:

fun getAllFiltered(filters: Filters): DataSource.Factory<Int, UserItem> {
    val conditions = mutableListOf<Pair<String, Any?>>()
    with(filters) {
        query?.let { conditions.add("name LIKE '%' || ? || '%'" to it) }
        isUploaded?.let { conditions.add("is_uploaded = ${it.toInt()}" to null) }
        // "subqueries" to filter  specific field
    }

    if (conditions.isEmpty())
        return getAll()

    val conditionsMerged = conditions.joinToString(separator = " AND ") { it.first }
    val bindArgs = conditions.mapNotNull { it.second }

    val query = SimpleSQLiteQuery(
        "SELECT id, name, date FROM UserData WHERE $conditionsMerged",
        bindArgs.toTypedArray()
    )
    return getAllFiltered(query)
}

@RawQuery(observedEntities = [UserItem::class])
fun getAllFiltered(query: SupportSQLiteQuery): DataSource.Factory<Int, UserItem>

private fun Boolean.toInt() = if (this) 1 else 0

I don't know how it will perform because queries have to be built at runtime (but from some testing that I did, i didn't notice much of a performance loss), but the advantage is that it is really easy to add other filters or remove existing ones and if only one filter from many is applied, created query is really simple.

Room DAO with multiple non-obligatory filters

3 Answers3