Hello guys. I have implemented a simple booking system. What i want is to group Bookings for a user by year and by month(based on date_started) , calculate the total cost for every month, and also carry some additional properties like resource id in order to display them in my browser. Have anyone some ideas how to implement that? Below is my collection schema iam using .Thank you in advance
Hello @petridis_panagiotis, You can use an aggregation query (db.collection.aggregate()) to get the desired result. The aggregation stage $group allows you group by user+year+month, and calculate (or sum) the total cost (for the month).
See $group aggregation example. You can use the aggregation operator $first to capture other fields like resource id from within the group stage.
Hello i read what $first is but what i understood this carries other fields only for the first doc. To be more precise i will give my favourite result.
Year 2021
January
[{Booking id:124,resource id total_cost},{Booking id:453,rrsource id total_cost} , month cost:40]
Feb the same
March same
…
2022 the same
Now, i will show you what at the end would like to have in my browser. So this page implemented with some silly array if-statements,hence the way to structurize like that my data was very struggling. As you can see from the image, in the link bar we take the user id from the parameter and query for this user. Then for one year( my dataset has at this time bookings only in 2021) we display bookings for every month and calculate the monthly cost (like June in my image). So, finally, is there any query that will free me up from complex and multiple arrays and array loop iterations? .If my desired result is imposibble using one mongodb command and maybe a few javacript iterations, i will again do that using nested arrays
Thank you very much. In addition i would like to display some properties for every booking of each month, like in my image. You can see under the Month name there are the details of their respective bookings, is that possible?