How to efficiently group expenses by category in Room (Kotlin/Android) instead of loading all rows and using groupBy in memory?

I’m building an expense tracker in Kotlin with Room. Each transaction stores its category as a String (the enum name), and I need to show the total spent per category.

Right now my DAO only returns all of a user’s transactions:

@Query("SELECT * FROM transacciones WHERE usuarioId = :usuarioId ORDER BY id DESC")
suspend fun porUsuario(usuarioId: Long): List<TransaccionEntity>

and I group them in memory:

repositorio.listar(usuarioId)
    .filter { it.tipo == TipoMovimiento.EGRESO }
    .groupBy { it.categoria }
    .mapValues { (_, lista) -> lista.sumOf { it.monto } }

It works, but it loads every row just to compute a handful of totals, and I’m worried it won’t scale as the table grows.

What is the recommended way to optimize this?

Should I use a GROUP BY query in the DAO (e.g. SELECT categoria, SUM(monto) AS total ... GROUP BY categoria) and map the result to a small data class?

Should the DAO return a Flow so the UI updates automatically when a transaction is added or deleted?

Is storing the category as a String a problem for this kind of query, or would a TypeConverter / separate categories table be better?

-2
Oct 30 at 4:32 AM
User AvatarGERARDO MORALES SANABRIA
#advice#android#kotlin#android-room

Accepted Answer

1. Create a data class for the aggregated result.

data class CategoryExpenseTotal(
    val categoria: String,
    val total: Double
)

Room can map the columns returned by the SQL query into this class.

2. Add a grouped query to the DAO.

@Dao
interface TransaccionDao {

    @Query("""
        SELECT categoria, SUM(monto) AS total
        FROM transacciones
        WHERE usuarioId = :usuarioId
          AND tipo = :tipo
        GROUP BY categoria
        ORDER BY total DESC
    """)
    fun getExpenseTotalsByCategory(
        usuarioId: Long,
        tipo: String
    ): Flow<List<CategoryExpenseTotal>>
}

3. Collect the results in the UI.

class ExpenseViewModel(
    private val dao: TransaccionDao
) : ViewModel() {

    fun observeCategoryTotals(
        usuarioId: Long
    ): Flow<List<CategoryExpenseTotal>> {
        return dao.getExpenseTotalsByCategory(
            usuarioId = usuarioId,
            tipo = "EGRESO"
        )
    }
}

Observe the results from an Activity or Fragment using a lifecycle-aware coroutine

lifecycleScope.launch {
    repeatOnLifecycle(Lifecycle.State.STARTED) {
        viewModel.observeCategoryTotals(usuarioId)
            .collect { totals ->
                // Update your RecyclerView or chart adapter.
                adapter.submitList(totals)
            }
    }
}
User AvatarDhruv Sakariya
Oct 30 at 6:27 AM
0