So, I've got a database collection thing I am working on. The main view is just a list of all the stuff in the database, and I'm currently trying to add optional filtering by certain parameters. I have a get all items function that I had been using to run the app to test the filtered items query in the database inspector because for a while, I kept getting SQL syntax errors while trying to make the function and that would cause the app to crash upon startup (the start view has this list). Well I finally got the list to generate using the filtered items function, but now selecting filters doesn't change the list (I even forced recompositions). I've got logging to check some things, so I know selecting a filter does properly update the filter state flow (though the view model that controls the list isn't grabbing those emitted values, but that's another problem for another time). I'm not sure where the breakdown is.

The desired behavior is that by default, no filters are selected and the entire list is returned if all filter options are set to null. There are 5 different values by which it can be filtered, 3 of them are booleans (favorites, dislikes, out of stock) and two are string lists (brand, type). The desired filtering behavior also includes the ability to select 1 or more of brand and/or type, and any kind of combination of these filters (and ignoring any other non-selected filters). For example, filter by Brand A and Brand B in favorites, or all Type A in dislikes, or all Brand A, types B and C. And taking the last example further, selecting Brand A as a filter and types B and C should return all items that fit Brand A AND Type B AND Type C, regardless of whether they are or are not in favorites, dislikes or out of stock.

The filtering is controled in a shared view model (I plan to use filtering on 2 different screens) which is scoped such that it's persistent through screen changes. I have confirmed this filtering view model does initialize first and persists and updates correctly, I've even confirmed that the selected filters get passed to the home view model where the list is and passed into the function (though again, have to force a reinitializing of the home viewmodel and recomp home screen), but the filters aren't working. The entire list is returned regardless of options.

I've got a Dao set up with an ItemsRepository and an OfflineItemsRepository (overrides ItemsRepository), and I'm not sure if the values are getting passed to the query properly. Again though, I know the query works as intended through database inspection (and many different filtering scenarios combining various values and leaving various fields blank).

Also, side note, I just realized now (as I am posting this and then going to bed), that I should probably change the outOfStock part, the filter selection is a boolean but I'm not sure if the query is right for checking that quantity = 0 is the true state and any other quantity is false... I have to think about that tomorrow, I don't remember now if I properly tested that part in the database inspector... it's been a long week of fighting this (and fighting a janky modal bottom sheet).

I can provide other code if needed, but this is the setup:

ItemsDao (in database inspector testing, this didn't work at first until I cast the booleans as integers; brand, type, favorite, disliked, quantity are the database entity values):

    @Query("""
        SELECT * FROM items WHERE
            (:brands IS NULL OR :brands = '' OR brand IN (:brands))
            AND (:types IS NULL OR :types = '' OR type IN (:types))
            AND (:favorites IS NULL OR favorite = CAST(:favorites AS INTEGER))
            AND (:dislikeds IS NULL OR disliked = CAST(:dislikeds AS INTEGER))
            AND (:outOfStock IS NULL OR quantity = 0)
        """)
    fun getFilteredItems(
        brands: List<String>?,
        types: List<String>?,
        favorites: Boolean?,
        dislikeds: Boolean?,
        outOfStock: Boolean?
    ): Flow<List<Items>>

ItemsRepository:

    fun getFilteredItems(
        brands: List<String>?,
        types: List<String>?,
        favorites: Boolean?,
        dislikeds: Boolean?,
        outOfStock: Boolean?,
    ): Flow<List<Items>>

OfflineItemsRepository (I had several attempts at the itemsDao.getFilteredItems parameters here, it was only by setting them all to null that I quit getting the SQL syntax error, not sure if this is right):

    override fun getFilteredItems(
        brands: List<String>?,
        types: List<String>?,
        favorites: Boolean?,
        dislikeds: Boolean?,
        outOfStock: Boolean?,
    ): Flow<List<Items>> =
        itemsDao.getFilteredItems(
            null,
            null,
            null,
            null,
            null,
        )

HomeViewModel (homeUiState controls the list):

    val homeUiState: StateFlow<HomeUiState> =
        combine(
            itemsRepository.getFilteredItems(
                brands = filterViewModel.selectedBrands.value,
                types = filterViewModel.selectedTypes.value,
                favorites = filterViewModel.selectedFavorites.value,
                dislikeds = filterViewModel.selectedDislikeds.value,
                outOfStock = filterViewModel.selectedOutOfStock.value
            ),
//            itemsRepository.getAllItemsStream(),
            preferencesRepo.isTableView,
        ) { items, isTableView ->
            Log.d("HomeViewModel", "disliked items: ${filterViewModel.selectedDislikeds.value}")
            HomeUiState(items, isTableView) }
        .stateIn(
            scope = viewModelScope,
            started = SharingStarted.WhileSubscribed(TIMEOUT_MILLIS),
            initialValue = HomeUiState(isLoading = true)
        )

FilterViewModel stuff, in case it's relevant:

    private val _selectedBrands = MutableStateFlow<List<String>>(emptyList())
    val selectedBrands: StateFlow<List<String>> = _selectedBrands

    private val _selectedTypes = MutableStateFlow<List<String>>(emptyList())
    val selectedTypes: StateFlow<List<String>> = _selectedTypes

    private val _selectedFavorites = MutableStateFlow(false)
    val selectedFavorites: StateFlow<Boolean> = _selectedFavorites

    private val _selectedDislikeds = MutableStateFlow(false)
    val selectedDislikeds: StateFlow<Boolean> = _selectedDislikeds

    private val _selectedOutOfStock = MutableStateFlow(false)
    val selectedOutOfStock: StateFlow<Boolean> = _selectedOutOfStock

    init {
        Log.d("FilterViewModel", "Initial selectedBrands: ${_selectedBrands.value}")
        Log.d("FilterViewModel", "Initial selectedTypes: ${_selectedTypes.value}")
        Log.d("FilterViewModel", "Initial selectedFavorites: ${_selectedFavorites.value}")
        Log.d("FilterViewModel", "Initial selectedDislikeds: ${_selectedDislikeds.value}")
        Log.d("FilterViewModel", "Initial selectedOutOfStock: ${_selectedOutOfStock.value}")
    }

    // Filtering update functions //
    fun updateSelectedBrands(brand: String, isSelected: Boolean) {
        if (isSelected) { _selectedBrands.value = listOf(brand) }
        else { _selectedBrands.value -= brand }
        Log.d("FilterViewModel", "Selected brands: ${_selectedBrands.value}")
    }

    fun updateSelectedTypes(type: String, isSelected: Boolean) {
        if (isSelected) { _selectedTypes.value += type }
        else { _selectedTypes.value -= type }
        Log.d("FilterViewModel", "Selected types: ${_selectedTypes.value}")
    }

    fun updateSelectedFavorites(isSelected: Boolean) {
        _selectedFavorites.value = isSelected
        Log.d("FilterViewModel", "Selected favorites: ${_selectedFavorites.value}")
    }

    fun updateSelectedDislikeds(isSelected: Boolean) {
        _selectedDislikeds.value = isSelected
        Log.d("FilterViewModel", "Selected dislikeds: ${_selectedDislikeds.value}")
    }

    fun updateSelectedOutOfStock(isSelected: Boolean) {
        _selectedOutOfStock.value = isSelected
        Log.d("FilterViewModel", "Selected out of stock: ${_selectedOutOfStock.value}")
    }

To me, it seems like having "null" in the OfflineItemsRepository function is hardcoding "null" rather than taking the values from the FilterViewModel, and that's why it's not working. It makes sense to me that the function should be like all my other override functions:

    override fun getFilteredItems(
        brands: List<String>?,
        types: List<String>?,
        favorites: Boolean?,
        dislikeds: Boolean?,
        outOfStock: Boolean?,
    ): Flow<List<Items>> =
        itemsDao.getFilteredItems(
            brands,
            types,
            favorites,
            dislikeds,
            outOfStock,
        )

But if I put anything other than "null" as a hardcoded value there, I get an SQL syntax error:

android.database.sqlite.SQLiteException: near "IS": syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM items WHERE
( IS NULL OR  = '' OR brand IN ())
AND ( IS NULL OR  = '' OR type IN ())
AND (? IS NULL OR favorite = CAST(? AS INTEGER))
AND (? IS NULL OR disliked = CAST(? AS INTEGER))
AND (? IS NULL OR quantity = 0)

I noticed in the above error, the values for the two parameters that are lists are just blank spaces (whereas the booleans are "?"), and I'm not sure if that's how that should be. So maybe the Query or function is not setup correctly to work together, though again, the Query works fine on it's own in the database inspector.

---

EDIT:

I was digging around and looking at the build implementation and it looks like, while the query worked in inspector, it wasn't handling list parameters in the build code implementation (from what I could tell). I've narrowed the issue down to specifically being caused by the two list inputs/parameters.

Looking around, I've been leaning towards a raw query with query builder, but I can't for the life of me figure that out at all. But I do know the problem specifically is the lists and it's breaking the query and not being compiled correctly when the app is built.

Well, I dug around and found a solution where someone fixed a list parameter issue using a string builder, so I went with RawQuery and included a string builder within the query builder and it seems to work in all testing so far.

The string builder just builds a string that appends a "?" for each brand I select to the where clause, so by the end the query builder builds the query by adding the string builder "brands" string which results in ?, ?, ? IS null OR ?, ?, ? IS '' ... IN brand (assuming I selected 3 options to put in the list) instead of IS null OR IS ''..., as was happening before, causing the syntax error when running in-app. In other words, the string builder builds the string used for the where clause for brands, and another for types.

A summary of what happens:

• The query runs like a SELECT * FROM items if no filters are selected (filter options in query builder are only added at the end by if (whereClauses.isNotEmpty()) {).
• Whenever any filter is selected, the query builder appends the query with a WHERE clause, but only if a filter is selected (if ([filterVal] != null) {).
• Each filter just builds it's own line as if it were the only WHERE clause which gets put into a list of claues (val whereClauses = mutableListOf<String>()) so that if more than one filter is selected, the query builder automatically writes the list (at the end) with the necessary " AND " separator between clauses, but only if the "whereClauses" is not empty.
• Arguments for the where clauses are also stored in a list which would be each brand, type, and a true value for booleans IF they are selected (no arguments passed and no generation done for those if boolean filter is not selected, to prevent displaying all items except those that are true for that value when the filter value is not selected), and the arguments are generated with the particular sections in which they fall, so they automatically map to the correct built "?" in the query.
• The first two are lists and a string builder builds the string of that part of the where clause by what is selected, adding a "?" for each selection and appending ", " as long as it's not the last item in the list (if (i < types.size - 1) { typeClause.append(", ") }).
• The query builder then generates the query with the correct format as if it were a specific query (example SELECT * FROM items WHERE ?, ?, ? IN brand) and then passes in the arguments from the list.

The solution I came up with (ItemsRepository stays the same):

ItemsDao:

@RawQuery(observedEntities = [Items::class])
    fun getFilteredItems(query: SupportSQLiteQuery): Flow<List<Items>>

OfflineItemsRepository:

override fun getFilteredItems(
    brands: List<String>?,
    types: List<String>?,
    favorites: Boolean?,
    dislikeds: Boolean?,
    outOfStock: Boolean?,
): Flow<List<Items>> {
    val queryBuilder = SupportSQLiteQueryBuilder.builder("items")
    val args = mutableListOf<Any>()
    val whereClauses = mutableListOf<String>()

    if (!brands.isNullOrEmpty()) {
        val brandClause = StringBuilder()
        brandClause.append("brand IN (")
        for (i in brands.indices) {
            brandClause.append("?")
            if (i < brands.size - 1) {
                brandClause.append(", ")
            }
            args.add(brands[i])
        }
        brandClause.append(")")
        whereClauses.add(brandClause.toString())
    }

    if (!types.isNullOrEmpty()) {
        val typeClause = StringBuilder()
        typeClause.append("type IN (")
        for (i in types.indices) {
            typeClause.append("?")
            if (i < types.size - 1) {
                typeClause.append(", ")
            }
            args.add(types[i])
        }
        typeClause.append(")")
        whereClauses.add(typeClause.toString())
    }

    if (favorites != null) {
        if (favorites) {
            whereClauses.add("favorite = ?")
            args.add(1)
        }
    }

    if (dislikeds != null) {
        if (dislikeds) {
            whereClauses.add("disliked = ?")
            args.add(1)
        }
    }

    if (outOfStock != null) {
        if (outOfStock) {
            whereClauses.add("quantity = 0")
        }
    }

    if (whereClauses.isNotEmpty()) {
        val whereClause = whereClauses.joinToString(" AND ")
        queryBuilder.selection(whereClause, args.toTypedArray())
    }

    val query = queryBuilder.create()

    // Log the query details
    Log.d("OfflineItemsRepository", "Query: ${query.sql}")
    for (i in 0 until query.argCount) {
        Log.d("OfflineItemsRepository", "Arg $i: ${args[i]}")
    }

    return itemsDao.getFilteredItems(query)
}