Phase 2 Comprehensive Exercise—ShopMetrics Backend CRUD

Q: There's a lot of duplicate code in CRUD pages. How can I reduce it?

Use Blade components to encapsulate form fields ( <x-input> , <x-select> ), and use artisan make:controller --resource to generate boilerplate code. Duplication is normal; make sure the functionality works correctly before considering abstraction.

Phase 2 Comprehensive Practice focuses on "Delivering Core Functionality"—bringing together all the knowledge from Lessons 8-13 to build a fully functional backend system.

1. What You'll Learn

Complete migration dataset: 10+ tables, including foreign keys and indexes
Eloquent Model Layer: Associations + Scopes + Accessors/Mutators
Resource Controller CRUD: List/Create/Edit/Delete with validation
Data populating: 100+ simulated tenants/users/orders
N+1 Detection and Optimization: Debugbar + Query Analysis

2. Bob's Phase 2 Acceptance Story

(1) Pain Point: Backend features are scattered across various courses, making it impossible to form a complete product

Alice has completed all the lessons in Phase 2, but migration is in Lesson 8, the model is in Lesson 9, and validation is in Lesson 12—each lesson runs fine on its own, but when combined, problems arise: foreign key constraints don't match, model associations are missing, and validation rules don't align with the database column types.

(2) Solutions to the Comprehensive Exercises

This lesson takes you step by step from database design to CRUD pages, weaving all the knowledge into a complete system—with each step building on the output of the previous one to ensure a seamless progression.

BASH

# Phase 2 deliverable: A complete admin CRUD system
php artisan migrate:fresh --seed
# → 10+ tables, 100+ records, all CRUD working

(3) Revenue

After completing the comprehensive exercise, Alice now has a fully functional ShopMetrics backend CRUD system—from creating tables to inserting, updating, deleting, and querying data, all in one go.

3. Database Design

(1) ShopMetrics Multitenant ER Diagram

erDiagram
    Tenant ||--o{ User : "has many"
    Tenant ||--o| Subscription : "has one"
    Tenant ||--o{ Shop : "has many"
    Plan ||--o{ Subscription : "has many"
    Shop ||--o{ Product : "has many"
    Shop ||--o{ Order : "has many"
    User ||--o{ Order : "places"
    Product }o--o{ Category : "belongs to many"
    Order ||--o{ OrderItem : "contains"
    Product ||--o{ OrderItem : "included in"

(2) Migration Checklist

#	Migration File	Table Name	Key Fields
1	create_plans_table	plans	name, slug, price, shop_limit, features(json)
2	create_tenants_table	tenants	name, slug, domain, plan_id (FK), status
3	create_subscriptions_table	subscriptions	tenant_id (FK), plan_id (FK), stripe_id, status
4	create_users_table	users	tenant_id (FK), name, email, password, role
5	create_shops_table	shops	tenant_id (FK), name, slug, status, revenue
6	create_categories_table	categories	name, slug
7	create_products_table	products	shop_id (FK), name, sku, price, stock, is_active
8	create_category_product_table	category_product	category_id (FK), product_id (FK)
9	create_orders_table	orders	tenant_id (FK), shop_id (FK), user_id (FK), total, status
10	create_order_items_table	order_items	order_id (FK), product_id (FK), qty, price

(1) ▶ Example: Core Migration File

PHP

// database/migrations/2024_01_01_000001_create_plans_table.php
Schema::create('plans', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('slug')->unique();
    $table->decimal('price', 8, 2);
    $table->integer('shop_limit')->default(5);
    $table->json('features')->nullable();
    $table->boolean('is_active')->default(true);
    $table->timestamps();
});

// database/migrations/2024_01_01_000005_create_shops_table.php
Schema::create('shops', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
    $table->string('name');
    $table->string('slug');
    $table->text('description')->nullable();
    $table->enum('status', ['active', 'suspended', 'closed'])->default('active');
    $table->decimal('revenue', 12, 2)->default(0);
    $table->timestamps();
    $table->softDeletes();
    $table->unique(['tenant_id', 'slug']);
    $table->index(['tenant_id', 'status']);
});

Output:

TEXT

// Execution Successful

4. Eloquent Model Layer

(1) Model List and Associations

Model	Association	Scope	Accessor/Mutator
Tenant	shops(), users(), subscription()	active()	domain_url
Shop	products(), orders(), tenant()	active()	revenue_formatted
Product	categories(), shop()	active(), inStock()	price_formatted
Order	items(), shop(), user()	completed(), thisMonth()	status_badge
OrderItem	product(), order()	—	subtotal

(1) ▶ Example: ShopMetrics Core Model

PHP

// app/Models/Tenant.php
class Tenant extends Model
{
    protected $fillable = ['name', 'slug', 'domain', 'plan_id', 'status'];
    protected $casts = ['status' => 'string'];

    public function shops(): HasMany
    {
        return $this->hasMany(Shop::class);
    }

    public function users(): HasMany
    {
        return $this->hasMany(User::class);
    }

    public function subscription(): HasOne
    {
        return $this->hasOne(Subscription::class)->where('status', 'active');
    }

    public function scopeActive(Builder $query): Builder
    {
        return $query->where('status', 'active');
    }

    public function getDomainUrlAttribute(): string
    {
        return 'https://' . $this->domain;
    }
}

// app/Models/Shop.php
class Shop extends Model
{
    use SoftDeletes;

    protected $fillable = ['tenant_id', 'name', 'slug', 'description', 'status', 'revenue'];
    protected $casts = ['revenue' => 'decimal:2', 'deleted_at' => 'datetime'];

    public function tenant(): BelongsTo
    {
        return $this->belongsTo(Tenant::class);
    }

    public function products(): HasMany
    {
        return $this->hasMany(Product::class);
    }

    public function orders(): HasMany
    {
        return $this->hasMany(Order::class);
    }

    public function scopeActive(Builder $query): Builder
    {
        return $query->where('status', 'active');
    }

    public function getRevenueFormattedAttribute(): string
    {
        return '$' . number_format($this->revenue, 2);
    }
}

// app/Models/Product.php
class Product extends Model
{
    protected $fillable = ['shop_id', 'name', 'sku', 'price', 'stock', 'is_active'];
    protected $casts = ['price' => 'decimal:2', 'is_active' => 'boolean'];

    public function shop(): BelongsTo
    {
        return $this->belongsTo(Shop::class);
    }

    public function categories(): BelongsToMany
    {
        return $this->belongsToMany(Category::class)->withTimestamps();
    }

    public function scopeActive(Builder $query): Builder
    {
        return $query->where('is_active', true);
    }

    public function scopeInStock(Builder $query): Builder
    {
        return $query->where('stock', '>', 0);
    }

    public function getPriceFormattedAttribute(): string
    {
        return '$' . number_format($this->price, 2);
    }
}

Output:

TEXT

// Execution Successful

5. Resource Controller CRUD

(1) List of Controllers and Validation Classes

Controller	Method	Form Request
ShopController	index/create/store/show/edit/update/destroy	StoreShopRequest/UpdateShopRequest
ProductController	index/create/store/show/edit/update/destroy	StoreProductRequest/UpdateProductRequest
OrderController	index/show/update	UpdateOrderRequest

(1) ▶ Example: Complete CRUD for ShopController

PHP

// app/Http/Controllers/ShopController.php
class ShopController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');
        $this->middleware('tenant.resolve');
    }

    public function index(Request $request): View
    {
        $shops = Shop::where('tenant_id', tenant()->id)
            ->withCount(['products', 'orders as recent_orders' => fn ($q) => $q->where('created_at', '>=', now()->subDays(30))])
            ->when($request->search, fn ($q, $s) => $q->where('name', 'like', "%{$s}%"))
            ->when($request->status, fn ($q, $s) => $q->where('status', $s))
            ->orderBy($request->sort ?? 'created_at', $request->direction ?? 'desc')
            ->paginate(15);

        return view('shops.index', compact('shops'));
    }

    public function create(): View
    {
        return view('shops.create');
    }

    public function store(StoreShopRequest $request): RedirectResponse
    {
        $shop = Shop::create(array_merge(
            $request->validated(),
            ['tenant_id' => tenant()->id],
        ));

        return redirect()->route('shops.show', $shop)
            ->with('success', 'Shop created successfully.');
    }

    public function show(Shop $shop): View
    {
        $shop->load(['products' => fn ($q) => $q->active()->latest()->take(10),
                      'orders' => fn ($q) => $q->with('user')->latest()->take(10)]);

        $stats = [
            'total_revenue' => $shop->orders()->completed()->sum('total'),
            'total_orders' => $shop->orders()->count(),
            'total_products' => $shop->products()->active()->count(),
        ];

        return view('shops.show', compact('shop', 'stats'));
    }

    public function edit(Shop $shop): View
    {
        return view('shops.edit', compact('shop'));
    }

    public function update(UpdateShopRequest $request, Shop $shop): RedirectResponse
    {
        $shop->update($request->validated());
        return redirect()->route('shops.show', $shop)
            ->with('success', 'Shop updated.');
    }

    public function destroy(Shop $shop): RedirectResponse
    {
        $shop->delete();
        return redirect()->route('shops.index')
            ->with('success', 'Shop deleted.');
    }
}

Output:

TEXT

// Execution Successful

6. Implementing the Verifier Class

(1) ▶ Example: ShopMetrics Form Request Class

PHP

// app/Http/Requests/StoreShopRequest.php
class StoreShopRequest extends FormRequest
{
    public function authorize(): bool
    {
        return auth()->user()->role === 'tenant_owner';
    }

    public function rules(): array
    {
        return [
            'name' => 'required|string|max:255',
            'slug' => 'required|alpha_dash|unique:shops,slug,NULL,id,tenant_id,' . tenant()->id,
            'description' => 'nullable|string|max:5000',
            'status' => 'sometimes|in:active,suspended',
        ];
    }

    protected function prepareForValidation(): void
    {
        $this->merge(['slug' => Str::slug($this->slug ?? $this->name)]);
    }
}

// app/Http/Requests/StoreProductRequest.php
class StoreProductRequest extends FormRequest
{
    public function rules(): array
    {
        return [
            'name' => 'required|string|max:255',
            'sku' => 'required|string|unique:products,sku',
            'price' => 'required|numeric|min:0.01|max:999999.99',
            'stock' => 'required|integer|min:0',
            'description' => 'nullable|string|max:5000',
            'is_active' => 'boolean',
            'categories' => 'sometimes|array',
            'categories.*' => 'exists:categories,id',
            'image' => 'sometimes|image|max:2048',
        ];
    }
}

Output:

TEXT

// Execution Successful

7. Data Population

(1) ▶ Example: ShopMetrics Complete Seeder

PHP

// database/seeders/ShopMetricsSeeder.php
class ShopMetricsSeeder extends Seeder
{
    public function run(): void
    {
        $starter = Plan::create(['name' => 'Starter', 'slug' => 'starter', 'price' => 29, 'shop_limit' => 5, 'features' => ['basic_analytics']]);
        $pro = Plan::create(['name' => 'Pro', 'slug' => 'pro', 'price' => 79, 'shop_limit' => 25, 'features' => ['advanced_analytics', 'api']]);
        $enterprise = Plan::create(['name' => 'Enterprise', 'slug' => 'enterprise', 'price' => 199, 'shop_limit' => null, 'features' => ['all']]);

        User::factory()->create(['email' => 'admin@shopmetrics.io', 'role' => 'super_admin']);

        Category::factory()->count(10)->create();

        Tenant::factory()->count(20)->create()->each(function ($tenant) use ($starter, $pro, $enterprise) {
            $plan = fake()->randomElement([$starter, $pro, $enterprise]);
            Subscription::create(['tenant_id' => $tenant->id, 'plan_id' => $plan->id, 'status' => 'active']);
            $owner = User::factory()->for($tenant)->create(['role' => 'tenant_owner']);
            $analysts = User::factory()->count(3)->for($tenant)->create(['role' => 'analyst']);

            $shopCount = min(fake()->numberBetween(2, 5), $plan->shop_limit ?? 99);
            Shop::factory()->count($shopCount)->for($tenant)->create()->each(function ($shop) use ($tenant, $owner, $analysts) {
                $products = Product::factory()->count(fake()->numberBetween(10, 30))->for($shop)->create();
                $products->each(fn ($p) => $p->categories()->attach(Category::inRandomOrder()->take(rand(1, 3))->pluck('id')));

                Order::factory()->count(fake()->numberBetween(20, 60))->for($tenant)->for($shop)->for(fake()->randomElement(array_merge([$owner], $analysts->all())))->create()->each(function ($order) use ($products) {
                    $items = $products->random(rand(1, 5));
                    foreach ($items as $product) {
                        $qty = rand(1, 3);
                        $order->items()->create(['product_id' => $product->id, 'quantity' => $qty, 'price' => $product->price]);
                    }
                    $order->update(['total' => $order->items->sum(fn ($i) => $i->price * $i->quantity)]);
                });
            });
        });
    }
}

Output:

TEXT

// Execution Successful

8. N+1 Detection and Optimization

(1) Install Debugbar

BASH

composer require barryvdh/laravel-debugbar --dev

(2) Common N+1 Scenarios and Solutions

Scenario	N+1 Problem	Solution
Store List	`$shop->products->count()`	`withCount('products')`
Order List	`$order->user->name`	`with('user')`
Product Details	`$product->categories`	`with('categories')`
Dashboard	Simultaneous Access to Multiple Linked Accounts	`with(['user', 'shop.items'])`

(1) ▶ Example: Debugbar Query Analysis

PHP

// In development, Debugbar shows query count on each page
// Optimize N+1 step by step:

// Step 1: Identify — Debugbar shows "41 queries" on shops.index
// Step 2: Add eager loading
$shops = Shop::with(['products', 'tenant', 'orders'])->get(); // 4 queries
// Step 3: Use withCount instead of loading full relations
$shops = Shop::withCount('products', 'orders')->with('tenant')->get(); // 3 queries
// Step 4: Verify — Debugbar shows "3 queries"

Output:

TEXT

// Execution Successful

9. Comprehensive Example: ShopMetrics Backend CRUD System

PHP

// ============================================
// Comprehensive: ShopMetrics Admin CRUD Setup
// Covers: migrations, models, controllers, requests, seeders
// ============================================

// Setup commands (run in order)
// 1. php artisan migrate:fresh --seed
// 2. php artisan storage:link
// 3. php artisan serve

// routes/web.php — Complete admin routes
Route::middleware('auth')->prefix('dashboard')->name('dashboard.')->group(function () {
    Route::get('/', [DashboardController::class, 'index'])->name('index');

    // Shops CRUD
    Route::resource('shops', ShopController::class);
    Route::post('/shops/{shop}/logo', [ShopLogoController::class, 'update'])->name('shops.logo');

    // Products CRUD (nested under shop)
    Route::resource('shops.products', ProductController::class)->shallow();

    // Orders (read-only + status update)
    Route::resource('orders', OrderController::class)->only(['index', 'show', 'update']);
    Route::post('/orders/{order}/export', ExportOrdersController::class)->name('orders.export');

    // Categories (simple CRUD)
    Route::resource('categories', CategoryController::class)->except('show');

    // Analytics
    Route::get('/analytics', [AnalyticsController::class, 'index'])->name('analytics');
});

// DashboardController with optimized queries
class DashboardController extends Controller
{
    public function index(): View
    {
        $tenant = tenant();
        $stats = Cache::remember("dashboard.{$tenant->id}", 300, function () use ($tenant) {
            return [
                'total_revenue' => $tenant->shops()->sum('revenue'),
                'active_shops' => $tenant->shops()->active()->count(),
                'monthly_orders' => $tenant->orders()->whereBetween('created_at', [now()->startOfMonth(), now()->endOfMonth()])->count(),
                'total_products' => Product::whereHas('shop', fn ($q) => $q->where('tenant_id', $tenant->id))->active()->count(),
            ];
        });

        $recentOrders = Order::where('tenant_id', $tenant->id)
            ->with(['shop', 'user'])
            ->latest()
            ->take(5)
            ->get();

        return view('dashboard.index', compact('stats', 'recentOrders'));
    }
}

❓ FAQ

Q How long does the Phase 2 exercise take?

A About 4-6 hours. Database design: 1 hour; models and relationships: 1.5 hours; CRUD controllers: 1.5 hours; data population and debugging: 1 hour. Do not skip any steps; verify each step before moving on to the next.

Q How can I ensure the migration order is correct?

A Tables with foreign key dependencies must be created first. The timestamps in the migration filenames determine the execution order—tables that are referenced are created first, followed by the tables that reference them. Alternatively, use php artisan migrate:status to check the order.

Q There's a lot of duplicate code in CRUD pages. How can I reduce it?

A Use Blade components to encapsulate form fields (<x-input>, <x-select>), and use artisan make:controller --resource to generate boilerplate code. Duplication is normal; make sure the functionality works correctly before considering abstraction.

Q Should Debugbar be disabled in a production environment?

A It must be disabled. Debugbar exposes sensitive information (SQL queries, memory usage, configuration values). It is enabled only when APP_DEBUG=true; in a production environment, where APP_DEBUG=false, it is automatically disabled.

Q What is the difference between Seeder data and test data?

A Seeder data generates the basic data for the development environment (administrator accounts, plans), which is persistently stored in the database; test data is generated using Factory in PHPUnit and is automatically rolled back after each test.

Q How can I verify that N+1 has been fully optimized?

A The number of queries displayed in the Debugbar should equal 1 (the main query) plus the number of preloaded joins. For example, with(['user', 'shop']) should have 3 queries. If there are more than expected, check for any missed lazy loads.

📖 Summary

When designing a database, start by drawing an ER diagram, then write the migrations to ensure the foreign key dependencies are in the correct order.
Eloquent models define three levels of functionality: associations, scopes, and accessors/mutators
Form Request encapsulates validation and authorization, keeping the controller lean
Data is populated in the following order: Plan → Tenant → User → Shop → Product → Order
Optimize N+1 queries using with() and withCount(); use Debugbar to monitor the number of queries
The CRUD system starts with a resource controller and gradually adds filtering, searching, and pagination

📝 Exercises

Basic Task (⭐): Complete the migration of the 10 tables in the ShopMetrics admin panel and define the models. Run migrate:fresh --seed to verify that all data is generated correctly and that there are no foreign key errors.
Advanced Exercise (⭐⭐): Implement full CRUD functionality for Shop and Product (including form request validation, search filtering, and pagination), ensuring that the Debugbar displays no more than 5 records per page.
Challenge (⭐⭐⭐): Implement cache-optimized data aggregation for the Dashboard—cache statistical queries for 5 minutes, and automatically clear the cache via model events when a new order is received to ensure a balance between data real-time performance and overall system performance.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

Phase 2 Comprehensive Exercise—ShopMetrics Backend CRUD

1. What You'll Learn

2. Bob's Phase 2 Acceptance Story

(1) Pain Point: Backend features are scattered across various courses, making it impossible to form a complete product

(2) Solutions to the Comprehensive Exercises

(3) Revenue

3. Database Design

(1) ShopMetrics Multitenant ER Diagram

(2) Migration Checklist

(1) ▶ Example: Core Migration File

4. Eloquent Model Layer

(1) Model List and Associations

(1) ▶ Example: ShopMetrics Core Model

5. Resource Controller CRUD

(1) List of Controllers and Validation Classes

(1) ▶ Example: Complete CRUD for ShopController

6. Implementing the Verifier Class

(1) ▶ Example: ShopMetrics Form Request Class

7. Data Population

(1) ▶ Example: ShopMetrics Complete Seeder

8. N+1 Detection and Optimization

(1) Install Debugbar

(2) Common N+1 Scenarios and Solutions

(1) ▶ Example: Debugbar Query Analysis

9. Comprehensive Example: ShopMetrics Backend CRUD System

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好