How to ignore a part of a WHERE clause in Room?

Question

I want a user to be able to build custom filters with multiple conditions. However the user may not want to search by all of them. For example, the user wants to filter Transactions only by account and ignores categories. In this case, how do I ignore a category part of the given WHERE clause?

@Query("SELECT * FROM Transactions WHERE account_id IN (:account_ids) AND category_id IN (:category_ids) ")
fun getTransactions(account_ids: List<Long>?, category_ids: List<Long>?): Flow<List<Transaction>>

This query is just an example, there are many more conditions in the real query. Passing NULLs to the function crashes the app, passing empty lists returns an empty list.

Answer 1

passing empty lists returns an empty list.

With AND then this is correct as AND implies that the row MUST be IN both and the empty list, which equates to IN() cannot match anything.

I have tried almost everything, I've read similar posts here, but nothing works for me.

Here's a solution (at least in principle). This is based upon using an @RawQuery which can cater for dynamically generated SQL. However, the down side is that Room cannot syntax check and or validate the SQL at compile time.

First start with the innocent looking:-

@RawQuery
fun rawQueryForTransactions(simpleSQLiteQuery: SimpleSQLiteQuery): List<Transactions>

To use the above you need a function that will build the SQL. Which is where matters can get a little complex.

For your purposes, plus a few added extras, I believe the following will do as you wish:-

fun getTransactions(
    account_ids: List<Long>?,
    category_ids: List<Long>?,
    orInsteadOfAnd: Boolean = false,
    returnAllIfNoLists: Boolean = false
): List<Transactions> {

    /* Prep fields */
    val sql = StringBuilder().append("SELECT * FROM transactions ")
    var condition = " AND "
    if (orInsteadOfAnd) condition = " OR "
    var whereKeywordApplied = false
    var account_ids_csv = StringBuilder()
    var category_ids_csv = StringBuilder()

    /* Handle Account Id List */
    if (account_ids != null && account_ids.size > 0) {
        for(aic in account_ids) {
            if (account_ids_csv.length > 0) account_ids_csv.append(",")
            account_ids_csv.append("'$aic'")
        }
        if (!whereKeywordApplied) {
            sql.append(" WHERE ")
            whereKeywordApplied = true
        } else {
            sql.append(condition)
        }
        sql.append(" account_id IN($account_ids_csv)")
    }

    /* Handle Category Id List*/
    if (category_ids != null && category_ids.size > 0) {
        for(cic in category_ids) {
            if (category_ids_csv.length > 0) category_ids_csv.append(",")
            category_ids_csv.append("'$cic'")
        }
        if ((!whereKeywordApplied)) {
            sql.append(" WHERE ")
            whereKeywordApplied = true
        } else {
            sql.append(condition)
        }
        sql.append(" category_id IN($category_ids_csv)")
    }
    /* Return None if no WHERE clause AND returnAllIfNoLists is false */
    if (!whereKeywordApplied && !returnAllIfNoLists) return emptyList()
    return rawQueryForTransactions(SimpleSQLiteQuery(sql.toString()))
} 

• Note that two extra (optional) parameters have been added.

• orInsteadOfAnd to switch between WHERE account_id IN(...) AND category_id IN(...) and WHERE account_id IN(...) OR category_id IN(...) (i.e. OR instead of AND)

• returnAllIfNoLists to, in the absence of valid lists (null or empty) to return all rows instead of no rows

Test/Demo

Using the following in an activity (.allowMainThreadQueries used to allow running on the main thread for brevity):-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAllDAOs()
        addSomeData(200)

        var transactions1 = dao.getTransactions(null,null)
        var transactions2 = dao.getTransactions(listOf(1L,3L),null)
        var transactions3 = dao.getTransactions(null, listOf(2L,3L))
        var transactions4 = dao.getTransactions(listOf(1L,2L,3L), listOf(4L), orInsteadOfAnd = true)
        var transactions5 = dao.getTransactions(listOf(), listOf())
        var transactions6 = dao.getTransactions(listOf(), listOf(), returnAllIfNoLists = true)
        var debugStop = false

    }

    private fun addSomeData(numberToAdd: Int) {
        for (i in 1..numberToAdd) {
            dao.insert(Transactions(account_id = (i % 3).toLong(), category_id = (i % 4).toLong()))
        }
    }
}

i.e. after adding some data various permutations have been run. With a BreakPoint on debugStop (only added to get a breakpoint after all premutations). The the debug shows:-

• transactions1 empty as expected
• transactions2 got about 1/3 (account id's will be 1 or 2 so as expected)
• transactions3 got 1/2 perhaps could have expected more
• transactions4 got over 2/3rds a little more than expected
• transactions5 got none as expected
• transactions6 got all as expected

An alternative approach could be to have multiple queries (perhaps otherwise unused) such as:-

@Query("SELECT * FROM Transactions WHERE account_id IN(:account_ids) AND category_id IN(:category_ids);")
fun getTransactionsByBoth(account_ids: List<Long>?, category_ids: List<Long>?): List<Transactions>
@Query("SELECT * FROM Transactions WHERE account_id IN(:account_ids);")
fun getTransactionsByAccountIds(account_ids: List<Long>?): List<Transactions>
@Query("SELECT * FROM Transactions WHERE category_id IN(:category_ids);")
fun getTransactionsByCategoryIds(category_ids: List<Long>?): List<Transactions>
@Query("SELECT * FROM Transactions;")

And to then have the core/called function that determines the underlying Query, from the above queries, to invoke.

e.g. :-

fun getTransactions2(account_ids: List<Long>?, category_ids: List<Long>?, returnAllIfNoLists: Boolean = false): List<Transactions> {
    if ((account_ids == null || account_ids.isEmpty()) && (category_ids == null  || category_ids.isEmpty())) {
        if (returnAllIfNoLists) return getAllTransactions()
        else return emptyList()
    }
    if (account_ids != null && !account_ids.isEmpty() && (category_ids == null || category_ids.isEmpty())) return getTransactionsByAccountIds(account_ids)
    if (account_ids == null || account_ids.isEmpty() && (category_ids != null && !category_ids.isEmpty())) return getTransactionsByCategoryIds(category_ids)
    return getTransactionsByBoth(account_ids,category_ids)
}

• function name getTransactions2 to differentiate between the two demos and thus allowing both to be used.
• unlike getTransactions above, the switch between OR and AND hasn't been catered for

If the activity code were then :-

    var transactions1 = dao.getTransactions(null,null)
    var transactions2 = dao.getTransactions(listOf(1L,3L),null)
    var transactions3 = dao.getTransactions(null, listOf(2L,3L))
    var transactions4 = dao.getTransactions(listOf(1L,2L,3L), listOf(4L), orInsteadOfAnd = true)
    var transactions5 = dao.getTransactions(listOf(), listOf())
    var transactions6 = dao.getTransactions(listOf(), listOf(), returnAllIfNoLists = true)

    var t1 = dao.getTransactions2(null,null)
    var t2 = dao.getTransactions2(listOf(1L,3L),null)
    var t3 = dao.getTransactions2(null, listOf(2L,3L))
    var t4 = dao.getTransactions2(listOf(1L,2L,3L), listOf(4L))
    var t5 = dao.getTransactions2(listOf(), listOf())
    var t6 = dao.getTransactions2(listOf(), listOf(), returnAllIfNoLists = true)

    var debugStop = false

The an example of the result, when the breakpoint is reached:-

• i.e. the same results either except for t4 which returns 0 as there is no option for the use of OR instead of AND.

• Note that only limited testing has been undertaken, so there might be some issues