CODEWITHSUNDEEP
androidkotlinandroid-sqliteandroid-room
baltekg
baltekg

Reputation: 1223

Room DAO with multiple non-obligatory filters

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.

Upvotes: 8

Views: 1498

Answers (3)

baltekg
baltekg

Reputation: 1223

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.

Upvotes: 3

mohsen sameti
mohsen sameti

Reputation: 446

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.

Upvotes: 2

TimB
TimB

Reputation: 591

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)

Upvotes: 5

Related Questions