Laravel Dynamic Columns Table: Group by 2-Level Relationship

Laravel Dynamic Columns Table: Group by 2-Level Relationship
Admin
Monday, June 19, 2023 8 mins to read
Share
Laravel Dynamic Columns Table: Group by 2-Level Relationship

Notice: this tutorial has both video version (see above) and text version, and also has links to the repository at the end.

I received an email from a person asking for help showing the table of data with dynamic columns. In this tutorial, I will show you exactly how I did it.

This will also be a good practice on these topics:

  • groupBy() on Eloquent/Collections
  • Seeding Data with Minimizing DB Queries
  • Generating a Field Value with str()-> Multiple Transformations
  • Eager Loading to avoid N+1 Query Problem

The link to the GitHub Repository is provided at the end of the tutorial.

The Original Question

Notice: the question is shortened for clarity.

I have been struggling to generate a HTML table with php/laravel.

See below:

The sizes (headers) will be created by the user, so they are dynamic and therefore might be in different order, which makes it a bit harder for me to match the header with the row.

Another point is, even though there is no color/size associated with the product, I still want to display the table as an empty field for those ones, like in the example above.

The challenge -> The Plan

So, to rephrase, we have two main challenges here:

  • Dynamic columns that should come from DB
  • Showing the cell/column with empty values

So, what we need to do:

  1. Prepare the project and seed the data
  2. Get the Product data grouped by color name
  3. Get all Sizes to populate the column names
  4. Present it all in Blade, including empty values

Here's the screenshot of the table I got at the end:

Now, let's build it step by step.

Preparing DB Schema

I will summarize the DB structure to just the Migration files. If you want all the details of Models, there's a repository link at the end of this tutorial.

Migration for "products":

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('code');
    $table->timestamps();
});

Migration for "colors":

Schema::create('colors', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

Migration for "sizes":

Schema::create('sizes', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

Migration for "sizes":

Schema::create('product_color_sizes', function (Blueprint $table) {
    $table->foreignId('product_id')->constrained()->onDelete('cascade');
    $table->foreignId('color_id')->constrained()->onDelete('cascade');
    $table->foreignId('size_id')->constrained()->onDelete('cascade');
    $table->string('reference_number');
 
    $table->timestamps();
});

Here's the visual DB schema generated with DBeaver:

Seeding Semi-Fake Data

Typically, developers seed data with one Seeder per model and use Factories. But in this case, I decided to create one seeder file and generate data that would be realistic and similar to what the question author posted.

php artisan make:seeder FakeDataSeeder

Then you may or may not add it to the main DatabaseSeeder:

database/seeders/DatabaseSeeder.php:

class DatabaseSeeder extends Seeder
{
    public function run(): void
    {
        $this->call(FakeDataSeeder::class);
    }
}

Then what's inside the Seeder? It's a pretty long piece of code. Let's divide it into two steps.

First, we seed sizes and colors from a list:

database/seeders/FakeDataSeeder.php:

use App\Models\Color;
use App\Models\Size;
 
class FakeDataSeeder extends Seeder
{
    public function run()
    {
        $sizes = [
            'XS',
            'S',
            'M',
            'L',
            'XL',
            'XXL',
        ];
 
        foreach ($sizes as $size) {
            Size::create(['name' => $size]);
        }
 
        $colors = [
            'Red',
            'Blue',
            'Green',
            'Yellow',
            'Black',
            'White',
        ];
 
        foreach ($colors as $color) {
            Color::create(['name' => $color]);
        }
 
        // ...
    }
}

Then, we prepare the data: we take all those records into Collections and choose which product names/types we seed. This way, we don't need to query the DB to select color/size/name each time.

Finally, we make 10 iterations to create a product with various names/colors/sizes.

I will comment on the code itself for more clarity.

database/seeders/FakeDataSeeder.php:

use App\Models\Product;
use App\Models\Color;
use App\Models\Size;
 
class FakeDataSeeder extends Seeder
{
    public function run()
    {
        // $sizes = [...];
        // $colors = [...];
 
        $productNames = collect([
            'T-Shirt',
            'Polo Shirt',
            'Hoodie',
            'Sweatshirt',
            'Jacket',
            'Jeans',
            'Trousers',
        ]);
 
        $productColors = Color::pluck('name', 'id');
        $productSizes = Size::all();
 
        // We will create 10 products
        for ($i = 1; $i <= 10; $i++) {
            $product = Product::create([
                // Pick one random name from the Collection
                'name' => $productNames->random(),
                'code' => rand(1, 1000)
            ]);
 
            foreach ($productColors as $colorId => $colorName) {
                // We generate up to 6 random sizes
                // But they shouldn't be repeated, so we have $usedSizes
                $usedSizes = [];
                for ($j = 0; $j < rand(0, 6); $j++) {
                    $size = $productSizes->whereNotIn('id', $usedSizes)->random();
                    if ($size && $colorId) {
                        $usedSizes[] = $size->id;
 
                        // We use hasMany relationship to create record
                        $product->productColorSizes()->create([
                            'size_id' => $size->id,
                            'color_id' => $colorId,
                            'reference_number' => str($product->code)->append(
                                '-',
                                str($colorName)
                                    ->limit(2, '')
                                    ->upper(),
                                '-',
                                str($size->name)
                                    ->upper(),
                            )
                        ]);
                    }
                }
            }
        }
    }
}

I like two things about this method:

  • We filter the data from pre-filled Collections with ->whereNotIn() and ->random() without touching the database. It's much faster.
  • We generate the reference_number with a chained operation using Laravel str() helper. This beauty needs individual attention.

So you will understand better, this is the result.

I enjoy using str()-> methods if I have multiple string transformations.

Ok, so now, to seed the data, you can run the primary seed or a specific class:

php artisan db:seed

Or:

php artisan db:seed --class=FakeDataSeeder

And here's what we have in our database:

Getting and Grouping Data into Table

I know, I know. The tutorial is about grouping, and we get to it only now. But that's the point: some of you may find it helpful to also see the "boring" parts of preparing demos :)

Anyway, we will have this Route:

routes/web.php:

use App\Http\Controllers\ProductController;
 
Route::get('products/{product}', ProductController::class);

The Controller is invokable with one __invoke() method, which will use Route Model Binding for the product:

app/Http/Controllers/ProductController.php:

use App\Models\Product;
use App\Models\Size;
 
class ProductController extends Controller
{
    public function __invoke(Product $product)
    {
        // This is to avoid N+1 query: will explain later in detail
        $product->load(['productColorSizes.color', 'productColorSizes.size']);
 
        // This is to populate the columns for the table
        $sizes = Size::pluck('name');
 
        // We're using groupBy from the relationship's relationship
        $productSizingTable = $product->productColorSizes->groupBy('color.name');
 
        return view('products.show',
            compact('product', 'productSizingTable', 'sizes'));
    }
}

And then, in Blade, we have this for the table:

resources/views/products/show.blade.php:

// ...
<table class="table-auto w-full">
    <thead>
    <tr>
        <th class="px-4 py-2">#</th>
        @foreach($sizes as $size)
            <th class="px-4 py-2">{{ $size }}</th>
        @endforeach
    </tr>
    </thead>
    <tbody>
    @foreach($productSizingTable as $color => $colorData)
        <tr>
            <td class="border px-4 py-2">{{ $color }}</td>
            @foreach($sizes as $size)
                <td class="border px-4 py-2">{{ $colorData->where('size.name', $size)->first()?->reference_number }}</td>
            @endforeach
        </tr>
    @endforeach
    </tbody>
</table>

This line is the most important for showing the specific cell:

$colorData->where('size.name', $size)->first()?->reference_number

Things to notice here:

  • The $colorData is a Collection, so we don't query the DB anymore
  • We just use ->where() on the Collection. That Collection is multi-dimensional, so we need to query the second level value of size.name
  • We use PHP nullsafe operator to check if there's the first() record at all, and then we return its reference_number if it exists. This syntax avoids additional if-statement to check if the record exists.

And this is the visual result:

Avoiding N+1 Query with Eager Loading

I already showed you the Controller line to ->load() additional data. But I want to emphasize it and show you before/after if we didn't do this.

For that, I've installed Laravel Debugbar to see the SQL queries running under the hood.

composer require barryvdh/laravel-debugbar

Notice: You don't necessarily need this specific package. I have this tutorial with alternatives: Laravel Eloquent: 4 Tools to Debug Slow SQL Queries

So, this is what we see at the bottom of the page:

5 DB queries, no big deal.

And now, let's comment out one line in Controller:

public function __invoke(Product $product)
{
    // $product->load(['productColorSizes.color', 'productColorSizes.size']);
 
    $sizes = Size::pluck('name');
 
    $productSizingTable = $product->productColorSizes->groupBy('color.name');

Refresh the page and see this in the Debugbar:

33 queries, 26 of them duplicated! Wow.

Why is this happening?

Look at this statement in our Controller:

$productSizingTable = $product->productColorSizes->groupBy('color.name');

We're trying to group by productColorSizes.color relationship, with two levels. The first level doesn't need to be eager loaded in our case, but the second level causes additional color queries if we don't use ->load().

Also, another line of grouping, this time in the Blade:

$colorData->where('size.name', $size)->first()?->reference_number

If we don't load the productColorSizes.size in the Controller, Laravel will query the database each time for each size.

So, use Eager Loading if you search or group by two-level relationships.

And, of course, prevent/log N+1 queries, I have a tutorial about it: Laravel: Automatically Log N+1 Queries to Bugsnag or Similar Tool.

GitHub Repository

That's it for this tutorial. You can find the complete repository here on GitHub.

Popular Articles
Store Public and Private Files/Images in Laravel: Ultimate Guide
4 mins read Tuesday, March 21, 2023
Filament Appointment Booking: Re-Use Admin Panel Form on Public Page
8 mins read Thursday, August 17, 2023
Laravel Structure: Move Code From Controller to... Where?
8 mins read Tuesday, January 3, 2023
FilePond in Laravel: File Upload Guide
8 mins read Thursday, September 29, 2022
Timeslot Checkout Page with Reservation Timer: Livewire and Alpine
4 mins read Wednesday, August 23, 2023
Multi-Step Form with Vue.js, Inertia and Laravel Breeze
8 mins read Tuesday, November 19, 2024