Laravel Report: 2-Level GroupBy with Dynamic Columns

hello guys in this video I will be answering a question I received via email displaying this table but it has a few challenges so this is the email text you can read it you can pause and read it all but I will summarize it for you so there's products colors and sizes tables and the challenge is to have sizes in the table as Dynamic columns so it's not set in stone how many columns will there be there may be two columns or six columns and also even if that color or that size doesn't have the record in the database we still need to show that table cell table row and table column so in this video I will show you exactly how I did it and also I decided that some of the videos on this YouTube channel will have premium versions on laravel dailycom so for laravel daily premium members there's a text version of the same lesson with a bit more content so in this YouTube video I will show you the grouping and in the longer eight minute video which is accompanied with this tutorial and GitHub repository I will show you additionally how I seated the data and how I avoided n plus one query problem and also how I used string helpers of laravel to populate this reference from code uppercased color and uppercased size this will be kind of intention to myself to create longer and more in-depth videos which will be available for subscribers but still providing the value for YouTube daily so here's our controller but before we get into that let's start with the database structure it's actually pretty simple so migration for colors migration for products which is actually product type more like product name than migration 4 sizes and this is the main table product color sizes it looks kind of like a pivot table for many-to-many relationship but it's not used as pivot table in this example it's just three foreign keys where with reference number which is generated in The Cedars so the result in the database is something like this color size product and 10 products seated in my case and the visual result is this I've recreated that project and we're showing the product code first two letters of color uppercase and size and empty cells if there's no record for that so how to achieve that in the controller let's get back to our main solution Point here so in here we have so-called invokable controller single action controller with just invoke method and in the routes we have route get product with route model binding so we have the product object from the database we load the additional relationships because we are grouping by them so this is the grouping by sentence and this sentence is for all the column names Dynamic headers for the columns so we'll have for each of the sizes in the blade to show the table and this will be our data and look how it's done we're getting the sizes so we're getting all the records from this table by grouped by two level relationships so product color size is actually if we take a look at the model this is has many relationship to that table and then that table has relationships belongs to to all the other tables so we're grouping by product color size color or in fact color name and the result will be name as the key which is exactly what we need for the table look this one will be the key of our array and this one will be the value which we will iterate and show the data so now if we go to the blade file of show blade we have for each as I said in the table for headers and in the body we're doing for each of the color name again as I said and we have color data and then again we repeat for each of the sizes and if there's a data for that color and that size we show that and this is the main Syntax for that so we have color data where and that where goes through the collection it doesn't query the database so from that collection we get the first record and if that exists we show the reference number this is called null safe operator appeared in PHP 8.0 and I hope you're all using at least PHP 8.0 or even newer versions so this way we avoid if statement of that record existing or not so this is an example of grouping by or searching by two level relationships we have this table data in our collection and we Group by color name and also perform the search where size name all without touching the database because we're doing eager loading for both of them in the controller so that's it for this video video about grouping but as I mentioned there's also a premium version of that video twice as long where I explain additionally this load sentence to avoid n plus one query problem and also how I see that the data for those product sizes including generating this string from a few strings with a string helper if you want to become a premium member of laravel dailycom the links are in the description below that's it for this time and see you guys in other videos

Channel: Laravel Daily

Views: 4,132

Keywords:

Id: 6FgSThpBtWc

Length: 5min 29sec (329 seconds)

Published: Tue Jun 20 2023