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:
- Query the 'categoryTag' collection.
- 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.
- 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.