Home > database >  How to query subcollection of each individual document in Firebase Cloud Firestore?
How to query subcollection of each individual document in Firebase Cloud Firestore?

Time:12-06

My project is a personal expense tracker. The structure of my Firestore database is as follows:

user1 > categoryTags > categoryTag1 > Expenses  >  Expense1
                                                   Expense2
                                                   Expense3

                       categoryTag2 > Expenses  >  Expense1
                                                   Expense2
                                                   Expense3

                       categoryTag3 > Expenses  >  Expense1
                                                   Expense2
                                                   Expense3

The categoryTag is basically a category. Since I am making a pie chart out of this data, I want to go into each of the categoryTags' and retrieve each of their Expense sub-collection. This is so that I can display a pie chart with each category name and the total amount spent on that category. The pie chart will be displayed in 3 views (today, this week, this month) detailing the expenses of the user as can be seen in the image below.

Current month Expense pie chart view

This is what I attempted:

val currentMonthExpenseEntries = arrayListOf<PieChartEntryModel>()

val eachtag = arrayListOf<PieChartEntryModel>()
val exp = arrayListOf<Double>()

try{
    val snapshot = repo.getCategoryTags()!!
    snapshot.documents.forEach {

        var moo = it.reference.collection(Constants.EXPENSES).get().await()

        if(moo != null) {
            moo.documents.forEach {
                val at  = it.get("expenseAmount") as Double
                exp.add(at)
                val amt = exp.sum()
                val catname = it.get("expenseCategory")

                 
                val pem = PieChartEntryModel(amt, catname as String)
                eachtag.add(pem)
            }
      //this eachtag array returns null when printed 
            println(eachtag)
        } else {
            print("Expense document snapshot query failed.")
        }

I got a snapshot of my categoryTags. And then tried a forEach loop on each categoryTag document to access each of the categoryTag's Expense subcollection.

However, this returned a null array.

Below is the PieChartEntryModel used for the pie chart entries.

data class PieChartEntryModel(
    var expenseTotal : Double,
    var expenseCategory : String
)

How do I go about this?

Edit 1: As suggested by @FrankvanPuffelen in the comments, I tried using collection group query to query all expenses. It returned this arraylist of PieChartEntryModel objects which is not what I want:

I/System.out: [PieChartEntryModel(expenseTotal=28.5, expenseCategory=Food), PieChartEntryModel(expenseTotal=28.5, expenseCategory=Food), PieChartEntryModel(expenseTotal=28.5, expenseCategory=Maintenance),PieChartEntryModel(expenseTotal=28.5, expenseCategory=Takeaway), PieChartEntryModel(expenseTotal=28.5, expenseCategory=Chores)]

Note, this is using dummy data so that is why the expenseTotal is the same. What I want is for each categoryTag to be returned with its corresponding total. E.g [(Food, 28.5), (Maintenance, 28.5), (Takewaway, 28.5), (Chores, 28.5)]

Edit 2- Solved.:

The issue has been solved. I am posting this edit for anyone who needs it in the future. Hope it helps.

This is how I have done it:

  1. Query the 'categoryTag' collection.
  2. Get the documentReference of each of the documents inside 'categoryTag' collection using forEach loop.

3.Use that documentReference to make another query for the 'Expense' subcollection.

  1. Used forEach loop to go through the document snapshots of the 'Expense' subcollection I retrieved by query to extract the total sum of expenses of each 'categoryTag' made in the current month.

Code:

class GetMonthlyExpenseDataUseCase @Inject constructor(private val repo : ExpenseFirebaseRepository) {
suspend fun invoke() : ArrayList<PieChartEntryModel> {
    val currentMonthExpenseEntries = arrayListOf<PieChartEntryModel>()
    var currentMonth = LocalDate.now().month.toString()

    try{
        /* Queried categoryTag collection */
        val snapshot = repo.getCategoryTags()!!

        /*for Each categoryTag*/
        snapshot.documents.forEach {
           val docRef = it.reference
            val categoryTagTitle = it.get("categoryTagTitle") as String

            /* for each categoryTag document a.k.a category, its docReference is gotten
         to make another query with to retrieve each categoryTag's subcollection of 'Expenses'. */
            val q = repo.getEachCategoryTagExpenseCollection(docRef)!!
            val expenseSnapshot = q.documents

            if(expenseSnapshot != null) {
                val categoryMonthExpenseAmtArray = arrayListOf<Double>()

                /*For each expense of a categoryTag*/
                q.forEach { expenseSnapshot ->
                        val expense = expenseSnapshot.toObject(Expense::class.java)

                    /* To retrieve current month's expenses of the categoryTag. Add into array to calculate total sum. */
                        if( expense !=null && expense.month == currentMonth) {
                            val k = expense.amount
                            categoryMonthExpenseAmtArray.add(k)
                        }
                }
                /* Sum up all expenses made in current month of categoryTag */
                val currentMonthSum = categoryMonthExpenseAmtArray.sum()

                /*One pie chart entry for every categoryTag user has.*/
                var p = PieChartEntryModel(currentMonthSum, categoryTagTitle)

                currentMonthExpenseEntries.add(p)
            }

        }

    }catch (e :Exception) {
        Log.d("TAG", "${e.message}")
    }
    println("Current month entries:$currentMonthExpenseEntries")
    return currentMonthExpenseEntries
}

}

The output I originally wanted and got:

I/System.out: Current month entries:[PieChartEntryModel(expenseTotal=85.5, expenseCategory=Food), PieChartEntryModel(expenseTotal=57.0, expenseCategory=Maintenance), PieChartEntryModel(expenseTotal=28.5, expenseCategory=Takeaway), PieChartEntryModel(expenseTotal=57.0, expenseCategory=Chores)]

Thank you.

CodePudding user response:

There is no operator on Firestore that returns the sum of stored values.

You can:

  • either collection group query, and then calculate the sum for each category in your application code,
  • or you can store the sum in each category document, update that with each write to the expenses under that, and then read only the category documents.
  • Related