Laravel Report Example: Subcategories and Transactions Total

hello guys today i want to show you a demo project of how to organize the report from eloquent query so imagine a scenario you have a category then you have sub categories and in those categories there are transactions and you need to show the report for last three months for example for total amount of transactions per sub-category and then at the bottom you would have total with the total for all the categories how to do that in eloquent and most important question how to do that in terms of performance to not load too much data to calculate only what you need to calculate especially if there are a lot of transactions so i will show you my way that i've created with my colleague david if you have any other suggestions or if you want to change something in my idea shoot in the comments below so first the database in the database we have categories categories with null are parents and otherwise parent id belongs to the same category so it's a has many relationship has many belongs to and then there are transactions and each transaction belongs to some category and there are a thousand transactions in total and this is the report as you have seen on the screen already and this is the code in the controller of that report let's run through that code step by step and you may learn a thing or two about optimization so first we load the categories where parent-id nulls so parent categories with their children children is a relationship name with has many of the same class which may be called static class or you can reference the same category with parent id field and there are also transactions has many so we load the children which means we'll load the categories with subcategories and then we can call a callback function how do we load the children we'll load the children with transactions and typical mistake here would be to load children.transactions which means we load children and transactions and then calculate the sum amount of every sub-category but that would be ineffective because we don't need all the transactions we don't need to load all transactions into memory and pollute the memory this way instead we need to calculate only the sum only the aggregate number that's why willow children with sum of transactions and not only that that with some also accepts potentially a condition which is a callback function paid at for example last three months so we're calculating transactions price and it is calculated as a field called transactions underscore some underscore price and then in the blade of that report we have for each of the categories we load the name and then for each of the children we load transactions underscore some underscore price as i mentioned we format that as the money format and show in the table and then at the bottom we have total for everything you can calculate the total even from the categories here in the blade but i prefer to put the logic in the controller so i do recalculate from the same categories so we don't load the database for the second time from the same children we flatten the array of children and we sum transactions some price if you don't know what that plug or flatten means let's actually debug that let's dump categories and then dump categories plug children then let's dump flatten duplicate flatten let's do line between those like this copy paste paste and if we reload the page again at the top you see this so categories is the array of items of category which is cool then category children will be each item will be array of category like this so each category is an array but then when we go to flatten we have 30 categories so we see only the children so as you can see in the categories there are 40 categories 10 parents and 30 children and from those 30 each of them has original attribute of transaction sum price which is calculated by with some here for more information you can take a look at plug or flatten in the collections documentation of laravel and you can play around with those methods as well so this is my way how to deal with the reports of categories and subcategories and transactions and if you want more videos on eloquent or tips you can subscribe to the channel or if you want collected videos on specific topics i put them as courses so on mylarvaldaily.teachable.com you can subscribe to yearly membership which is currently 27 courses or pick something from the list which includes better eloquent performance like i discussed in this lesson in this video or how to structure databases so a lot of things around it bases are in this list but as i said the best way is to subscribe to yearly membership to join my journey for the long run that's it for this time and see you guys in other videos

Channel: Laravel Daily

Views: 12,911

Keywords:

Id: PXtrJb1fioY

Length: 5min 8sec (308 seconds)

Published: Thu Mar 17 2022