Only first DAO Flow of Room database returns values

Question

I want to retrieve a specific column from my Android ROOM Database but it kept returning an empty list.

Here is the code on my DAO interface:

@Query("SELECT * FROM expense_table ORDER BY category ASC")
fun getAllExpenses(): Flow<List<Expense>>

@Query("SELECT DISTINCT category FROM expense_table ORDER BY category ASC")
fun getAllCategories(): Flow<List<String>>

getAllExpenses works perfectly, but for some reason getAllCategories kept returning empty lists. I even made getAllExpenses ordered by category to make sure the column does exist and it does. Reducing the query to a simple "SELECT category FROM expense_table" doesn't work by the way.

I tried looking up on how to solve this but I can't find any solutions. Which is to be expected as this is such a simple query that I'm sure I'm just doing something incredibly stupid and overlooking it.

Here are my other codes, I largely just copied the getAllExpenses code so I think the DAO is the problem since it's the only one that's different but I'm not sure.

Repository:

fun getAllExpenses() = expenseDao.getAllExpenses()

fun getAllCategories() = expenseDao.getAllCategories()

ViewModel:

var expenseRepository: ExpenseRepository
private val _expenseList = MutableStateFlow<List<Expense>>(emptyList())
val expenseList: StateFlow<List<Expense>> get() = _expenseList
private val _categoryList = MutableStateFlow<List<String>>(emptyList())
val categoryList: StateFlow<List<String>> get() = _categoryList     

init {   
    expenseRepository = ExpenseRepository(expenseDatabase.expenseDao())
    viewModelScope.launch {
        expenseRepository.getAllExpenses().collect { expenses ->
            _expenseList.value = expenses
        }
        expenseRepository.getAllCategories().collect { categories ->
            _categoryList.value = categories
        }
    }
}

Activity (this is just a placeholder screen for testing)

composable(route = AppScreen.TEST_SCREEN.name) {
    val list by viewModel.expenseList.collectAsState()
    val catList by viewModel.categoryList.collectAsState()

    TestScreen(list, catList)
}

The screen would display the list size and list contents for each list. The expense list is displayed correctly but the category list would always show a size of 0.

tyg · Accepted Answer · 2025-11-09 18:47:36Z

As the other answer already explains, collect only returns when the Flow finishes - which never happens for a Room Flow. That's why the second collect is never reached and _categoryList is never populated.

Although the proposed solution to launch separate coroutines for each collector solves that, this isn't how you should handle Room Flows in the view model at all. As a rule of thumb: Never collect Flows in the view model.

Instead, only pass the Flows through. You can even transform them on the way (using map, combine and the sorts), but at the end you should convert them into StateFlows by calling stateIn. Your view model should look like this:

val expenseRepository = ExpenseRepository(expenseDatabase.expenseDao())

val expenseList: StateFlow<List<Expense>> = expenseRepository.getAllExpenses()
    .stateIn(
        scope = viewModelScope,
        started = SharingStarted.WhileSubscribed(5.seconds),
        initialValue = emptyList(),
    )

val categoryList: StateFlow<List<String>> = expenseRepository.getAllCategories()
    .stateIn(
        scope = viewModelScope,
        started = SharingStarted.WhileSubscribed(5.seconds),
        initialValue = emptyList(),
    )

This way your composables can unsubscribe from the StateFlow (when they leave the composition, for example; make sure to replace collectAsState() by collectAsStateWithLifecycle() for that to work), and then the StateFlow can stop the Room Flow in turn, saving resources by preventing collecting Flows that no one is listening to. That wouldn't be possible by blindly collecting the Flows in the init block.

Bashu · Accepted Answer · 2025-11-09 11:36:56Z

2

The problem is collect method (collect is a suspend function that does not return until the flow is completed) ->

init {   
        expenseRepository = ExpenseRepository(expenseDatabase.expenseDao())
        viewModelScope.launch {
            /********* The collect fun is suspend fun *********/
            expenseRepository.getAllExpenses().collect { expenses ->
                _expenseList.value = expenses
            }
            /********* This line will never be executed due to the collect of getAllExpenses *********/
            expenseRepository.getAllCategories().collect { categories ->
                _categoryList.value = categories
            }
        }
    }

So the correct solution would be ->

init {
        expenseRepository = ExpenseRepository(expenseDatabase.expenseDao())
        viewModelScope.launch {
            expenseRepository.getAllExpenses().collect { expenses ->
                _expenseList.value = expenses
            }
        }

        viewModelScope.launch {
            expenseRepository.getAllCategories().collect { categories ->
                _categoryList.value = categories
            }
        }
    }

answered Nov 9 at 11:36

Bashu

2,21115 silver badges22 bronze badges

2 Comments

tyg Nov 9 at 18:49

Although this allows the second Flow to be collected, please don't collect flows in the view model at all. Flows should be collected at the latest possible moment only, and that are the composables. Otherwise the Flows cannot be properly managed. See my answer for more details and how Flows should be handled in a view model instead.

JazzAnt Nov 10 at 9:28

Thank you for the answer. Though I ended up using tyq's answer instead but it's good to learn how collect works

Collectives™ on Stack Overflow

Only first DAO Flow of Room database returns values

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related