Laravel Dynamic Columns Table: Group by 2-Level Relationship

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...