TASKS 04: Advanced MySQL Concepts & Laravel Relationships - RadLeoOFC/laravel-admin-panel GitHub Wiki

The goal of this task was to implement a categories table and establish a "one-to-many" relationship between categories and products.

• Created a migration with the command:

php artisan make:migration create_categories_table --create=categories

• Added the following structure to the up method of the migration:

Schema::create('categories', function (Blueprint $table) {
    $table->id(); // Primary key
    $table->string('name'); // Category name
    $table->timestamps(); // created_at and updated_at columns
});

• Executed the migration:

php artisan migrate

• Migration file create_categories_table.

This screenshot shows the migration file that creates the categories table.

• Database categories table (phpMyAdmin).

Screenshot of the categories table in the database after migration.

• Created a migration:

php artisan make:migration add_category_id_to_products_table --table=products

• Added a foreign key in the up method:

Schema::table('products', function (Blueprint $table) {
    $table->foreignId('category_id')->nullable()->constrained('categories')->onDelete('cascade');
});

• Executed the migration:

php artisan migrate

• ** Migration add_category_id_to_products_table.**

• Database products table with category_id (phpMyAdmin).

Screenshot of the products table with the added category_id column.

• In the Product.php model, added the category method:

public function category()
{
    return $this->belongsTo(Category::class);
}

• In the Category.php model, added the products method:

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

• Product.php with category method.

This screenshot shows the category method in the Product model, representing the belongsTo relationship.

• Category.php with products method.

This screenshot shows the products method in the Category model, representing the hasMany relationship.

• Added a dropdown for selecting categories in the product creation/edit form:

<label for="category_id">Category</label>
<select name="category_id" id="category_id">
    @foreach($categories as $category)
        <option value="{{ $category->id }}">{{ $category->name }}</option>
    @endforeach
</select>

• Loaded categories in the controller:

public function create()
{
    $categories = Category::all();
    return view('products.create', compact('categories'));
}

• Ensured category_id is saved during product creation:

public function store(Request $request)
{
    $validated = $request->validate([
        'name' => 'required|string|max:255',
        'category_id' => 'nullable|exists:categories,id',
    ]);

    Product::create($validated);

    return redirect()->route('products.index');
}

• Product creation/edit form with dropdown.

This screenshot shows the product creation/edit form with a dropdown for category selection.

• Product table in web

• Controller's create method.

This screenshot shows the create method in the controller, which loads categories for the form.

• Created a migration: php artisan make:migration add_index_to_category_id_on_products_table --table=products

• Added an index to the category_id column:

Schema::table('products', function (Blueprint $table) {
    $table->index('category_id');
});

• Executed the migration:

php artisan migrate

• Migration file add_index_to_category_id_on_products_table.

This screenshot shows the migration file that adds an index to the category_id column in the products table.

Relationships in the database:

• Data Integrity: Relationships ensure that data between tables is connected and valid using foreign keys.

• Simplified Code: With relationships, accessing related data is straightforward using methods like belongsTo or hasMany.

• Improved Query Performance: Using relationships with eager loading (with) reduces the number of database queries.

• Maintainability: Relationships make the codebase clean and maintainable by abstracting the complexity of SQL joins.

Example: A Category has many Products.

• In Category.php:

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

• In Product.php:

public function category()
{
    return $this->belongsTo(Category::class);
}

Example: A User has one Profile.

• In User.php:

public function profile()
{
    return $this->hasOne(Profile::class);
}

• In Profile.php:

public function user()
{
    return $this->belongsTo(User::class);
}

Example: Post can have many Tags, and Tag can belong to many Posts.

• In Post.php:

public function tags()
{
    return $this->belongsToMany(Tag::class);
}

• In Tag.php:

public function posts()
{
    return $this->belongsToMany(Post::class);
}

** Example:** A Photo can belong to a Post or a User.

• In Photo.php:

public function imageable()
{
    return $this->morphTo();
}

• In Post.php:

public function photos()
{
    return $this->morphMany(Photo::class, 'imageable');
}

• In User.php:

public function photos()
{
    return $this->morphMany(Photo::class, 'imageable');
}

By following these steps and examples, any type of relationship in Laravel can be set up effectively, ensuring efficient and maintainable code.