Laravel 5 Eloquent Relationships

Laravel 04:41:00

Introduction

In the previous tutorial, we created the views, basic controllers and routes for our Laravel admin panel. In this tutorial, we will focus on Laradmin database design and development. We will use Eloquent ORM and Laravel Migrations to achieve that. Laradmin is a tutorial project that we are working with throughout these tutorial series.

Topics to be covered

In this tutorial, we will cover the following topics

Tutorial prerequisites
Laradmin Database Entity Relationship Diagram (ERD)
Laradmin Database Dictionary
Laradmin Models and Migrations
- Database configuration
- Database migration files
- Laravel Eloquent one to many relationship
- Laravel Eloquent many to many relationship
Laravel Tinker
Tutorial Challenge

Tutorial prerequisites

You understand the basics of Laravel 5. If you do then I recommend you start with these Laravel 5 Tutorial series. This tutorial also assumes you know eloquent ORM basics. If you do not then I recommend you start with this tutorial Laravel 5 Eloquent ORM
You have PHP, MySQL and Apache up and running
You have composer installed
You have a text editor or IDE that supports PHP.
You have a modern web browser that supports HTML5

Laradmin Database Entity Relationship Diagram (ERD)

The Entity Relationship Diagram (ERD) is a modelling tool that provides a graphical representation of the database objects (tables, views), attributes and the relationships between them. The following image shows the tables and relationships for our admin panel.
Laravel Admin Panel ERD

We used a modular approach to organizing our views and controllers. We have used the same concept to group the tables.

Laradmin Database Dictionary

The following table describes the database tables and fields for our Laravel admin panel.
Author: Rodrick Kazembe | Date: 2016-02-17 | Version: 1
All tables will have the following files common fields for record audit purposes

S/N	FIELD	DATA TYPE	DESCRIPTION
1	created_at	Timestamp	Timestamp when record was created
2	updated_at	Timestamp	Timestamp when record was last updated

E-Shop Data Entry Tables

Table: Brands

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT (AUTOINCREMENT)	Primary key
2	name	VARCHAR(245)	Brand name
3	remarks	VARCHAR(500)	Brand remarks

Table: Categories

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT (AUTOINCREMENT)	Primary key
2	name	VARCHAR(245)	Category name
3	remarks	VARCHAR(500)	Category remarks

Table: Products | Assumptions: At any given time, a product can only have a single brand but can belong to more than one category.

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT (AUTOINCREMENT)	Primary key
2	name	VARCHAR(255)	Product name
3	description	VARCHAR(500)	Product description
4	price	INTEGER	Product price
5	brand_id	INTEGER	Brand id Foreign key

Table: Products_categories | the relationship between products and categories is many to many. This is the intermediate table used to create the many to many relationship. The primary key is a composite key made up of the product_id and category_id.

S/N	FIELD	DATA TYPE	DESCRIPTION
1	product_id	INTEGER	Primary key [composite]
2	category_id	INTEGER	Primary key [composite]

E-Shop Transactions

Table: Customers | this table is populated when a customer signs up from the frontend

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT (AUTOINCREMENT)	Primary key
2	name	VARCHAR(145)	Customer name
3	email	VARCHAR(145)	Email address for the customer
4	contact_number	VARCHAR(75)	Contact number for the customer
5	delivery_address	VARCHAR(750)	Customer’s physical address for delivery

Table: Orders | this table is populated when a customer places an order from the frontend

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT (AUTOINCREMENT)	Primary key
2	order_id	VARCHAR(145)	Auto generated order unique number
3	transaction_date	TIMESTAMP	Date that the order was placed
4	customer_id	INTEGER	The id for the customer who placed the order
5	status	VARCHAR(45)	Status can either be pending or processed
6	total_amount	INTEGER	Total amount of all items purchased

Table: Order_Details | this table contains detailed information about the order

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT (AUTOINCREMENT)	Primary key
2	order_id	VARCHAR(145)	Programmatically generated order unique number
3	product_id	INTEGER	Product primary key value
4	quantity	INTEGER	The quantity of the ordered product
5	price	INTEGER	The price of the product at the time of the order
6	sub_total	INTEGER	The total of the quantity multiplied by the price

Frontend Data Entry

Table: Pages | frontend static pages i.e. about Larashop, privacy policy etc.

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT (AUTOINCREMENT)	Primary key
2	title	VARCHAR(145)	Page title
3	meta_description	VARCHAR(145)	Meta description displayed in search engines
4	keywords	VARCHAR(75)	Page keywords
5	content	TEXT	Page content
6	published_at	TIMESTAMP	Date that the post was published
7	user_id	INTEGER	The id of the user who published the page

Table: Blog_Posts | blog posts

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT (AUTOINCREMENT)	Primary key
2	title	VARCHAR(145)	Blog post title
3	meta_description	VARCHAR(145)	Meta description displayed in search engines
4	keywords	VARCHAR(75)	Blog post keywords
5	content	TEXT	Blog post content
6	published_at	TIMESTAMP	Date that the blog post was published
7	user_id	INTEGER	The id of the user who published the blog post

Laradmin Models and Migrations

Now that we have covered the basics of our database design, let’s get our hands dirty. This section assumes you successfully completed the previous tutorial. We will work with the same project that we created in the previous tutorial.
The following image shows the Eloquent ORM Object Relationship Diagram
Laravel Admin Panel ERD

HERE,

Brand.php has a function products() that we will used to retrieve all products associated with a particular brand.
Product.php has a function brand() that we will use to retrieve product brand information
Product.php has a function categories() that we will use to retrieve all categories associated with a product
Category.php has a function products() that we will use to retrieve all products associated with a given category

Note: functions that return multiple records use plural names while functions that return a single record use singular names. This is considered a best practice.

Database configurations

Create a database laradmin in MySQL

CREATE SCHEMA laradmin;
Open laradmin/.env file
Update the connection parameters as follows
DB_HOST=localhost
DB_DATABASE=laradmin
DB_USERNAME=root
DB_PASSWORD=stonecutter

Save the changes
Creating models and migration files with one command
Open the command prompt / terminal and run the following command

cd C:\xampp\htdocs\laradmin

The above command assumes you have windows and XAMPP installed to drive C. you can modify the path if necessary to match the setup of your development machine
Let’s start with the model for the brands table. As a convention, the model name should be singular in Laravel while the table name should be plural.
Run the following command

php artisan make:model Brand –m

HERE,

artisan make:model Brand -m creates a model Brand. The parameter –m tells artisan to also create the respective database migration file.

The model Brand.php will be created in the directory /laradmin/app/ while the migration file will be created in the directory /laradmin/database/migrations.
The following artisan will create the rest of the models and migration files required for now.

php artisan make:model Category -m
php artisan make:model Product -m
php artisan make:model ProductCategory -m

php artisan make:model Customer -m
php artisan make:model Order -m
php artisan make:model OrderDetail -m

php artisan make:model Page -m
php artisan make:model BlogPosts -m

Database migration files

Update the respective migration files as follows
open xxxxx_create_brands_table.php

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateBrandsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('brands', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name',245);
            $table->string('remarks',500);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('brands');
    }
}

open xxxxx_create_categories_table.php

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateCategoriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name',245);
            $table->string('remarks',500);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('categories');
    }
}

open xxxxx_create_products_table.php

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProductsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name',245);
            $table->string('description',500);
            $table->integer('price');
            $table->unsignedInteger('brand_id');
            $table->timestamps();

            $table->foreign('brand_id')->references('id')->on('brands');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('products');
    }
}

HERE,

$table->unsignedInteger('brand_id'); creates the foreign key brand_id. Note we are using unsignedInteger as the data type. This is because schema builder increments uses unsignedInteger. If you use integer, creating the relationship will fail.
$table->foreign('brand_id')->references('id')->on('brands'); creates a relationship between products table and brands table using the id in brands and brand_id field in products table.

open xxxxx_create_product_categories_table.php

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProductCategoriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('product_categories', function (Blueprint $table) {
            $table->unsignedInteger('product_id');
            $table->unsignedInteger('category_id');
            $table->primary(array('product_id', 'category_id'));
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('product_categories');
    }
}

HERE,

$table->unsignedInteger('product_id'); $table->unsignedInteger('category_id'); creates two foreign keys namely product_id and category_id
$table->primary(array('product_id', 'category_id')); defines a composite primary key made up of product_id and category_id

Run the following artisan command to run the migration files

php artisan migrate

Laravel Eloquent one to many relationship

The models are responsible for interacting with our database. We used migration files to create the database tables and define the relationships. We will now create relationships between models.
Let’s start with the brand model
Open /laradmin/app/Brand.php
Replace the code with the following.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Brand extends Model
{
    protected $fillable = ['name', 'remarks'];

    public function products()
    {
        return $this->hasMany('App\Product');
    }
}

HERE,

protected $fillable = ['name', 'remarks']; specifies fields that can be mass assigned
public function products() { return $this->hasMany('App\Product'); }hasMany defines a one to many relationship between the brands table and products table. By default, Laravel will use brand_id as the foreign key in products table. Brand comes from the model name and it’s prefixed with _id to determine the foreign key brand_id. Alternatively, the relationship can be defined as return $this->hasMany('App\Product', 'foreign_key', 'local_key'); the latter is useful if the foreign and local keys do not follow the default Laravel conventions.

The inverse relationship definition in Product.php will be defined by the following function

public function brand()
{
    return $this->belongsTo('App\Brand');
}

HERE,

$this->belongsTo('App\Brand'); defines the many to one relationship with the Brand model.

Laravel Eloquent many to many relationship

A single product can belong to more than one category and a single category can belong to more than one product. The nature of the relationship between products and categories is many to many. In our database design, we designed this relationship by introducing an intermediate table products_categories. We will now create a many to many relationship between Product and Category models via the intermediate table.
Open /laradmin/app/Product.php
Replace the code with the following

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $fillable = ['name', 'description','price','brand_id'];

    public function brand()
    {
        return $this->belongsTo('App\Brand');
    }

    public function categories()
    {
        return $this->belongsToMany('App\Category','product_categories','product_id','category_id');
    }
}

HERE,

$this->belongsToMany('App\Category','product_categories','product_id','category_id'); defines the many to many relationship with the Category model via the intermediate table product_categories. product_id is the local key and category_id is the foreign key.

Open /laradmin/app/Category.php
Replace the code with the following

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    protected $fillable = ['name', 'remarks'];

    public function products()
    {
        return $this->belongsToMany('App\Product','product_categories','category_id','product_id');
    }
}

Laravel Tinker

Tinker is a command line utility that allows you to interact with the Laravel environment. It is a good tool for testing function without the use of the web browser.
Open the command prompt / terminal.
Assuming you are using windows and xampp, browse to the following directory

cd C:\xampp\htdocs\laradmin

Run the following artisan command to run tinker

php artisan tinker

You will be presented with the following console window
Laravel Tinker

Run the following command to create an instance of the Brand model

$brand = new App\Brand;

HERE,

$brand = new App\Brand; creates a php object variable $brand and initializes it to an instance of the Brand model. Notice we included the namespace App for the model. That’s the beauty of tinker. You write plain PHP code and it will execute it for you in the Laravel environment

We could have seeded the database records but we will use tinker to add a few records.
Run the following command to create a brand record

$brand->name = "Chanel";
$brand->remarks = "It is pronounced as Shanel";
$brand->save();

HERE,

$brand->name = "Chanel"; assigns a value to the name field
$brand->remarks = "It is pronounced as Shanel"; assigns a value to the remarks field
$brand->save(); saves the new record into the database

You console window should now appear as follows
Laravel Tinker Eloquent Model

Let’s now see the contents of the brands table. Run the following command

$brand->all();

The above command will give you the following results
Laravel Tinker Eloquent Model

If you check the database, you will be able to see the record. We will now add a new product with a brand_id of 1.
Run the following commands

$product = new App\Product;

$product->name = "T-Shirt";
$product->description = "Men's cool T-Shirt";
$product->name = "65";
$product->brand_id = "1";
$product->save();

Let’s now call the products method that we defined in the Brand model.
Run the following command

$brand::find(1)->products;

HERE,

$brand::find(1)->products; retrieves the brand record with id 1 and lists all products that have the brand_id 1.

You will get the following results
Laravel Tinker Eloquent Model

Note: via the products method in Brand model, we are able to retrieve all products with brand_id 1. Try to add more products and call the products method of the Brand model.

Tutorial Challenge

The best way to learn is by doing it yourself. This challenge gives you an opportunity to implement the functionality that has been left out on purpose.
Challenge 1 [Simple]: use tinker to

Create instance variables of Category model and create 2 to 3 categories.
Populate the product_categories table with values that exist in both products and categories table
Call the products method in the Category model to list all products associated with the category.

Challenge 2 [a bit complex compared to challenge 1]

Write the code for the migration files for E-Shop Transactions and Frontend Data entry tables based on the database dictionary above. You have to create the relationships as well
Define the relationships in the models.
Use tinker to add test records and test retrieving records

Summary

Eloquent ORM makes it super easy for us to define relationships and retrieve data in an object oriented way. Tinker is a great tool that allows us to play with our models in development mode. This speeds up developing and testing the functionality.

Laravel 5 Eloquent Relationships

Introduction

Topics to be covered

Tutorial prerequisites

Laradmin Database Dictionary

E-Shop Data Entry Tables

E-Shop Transactions

Frontend Data Entry

Laradmin Models and Migrations

Database configurations

Database migration files

Laravel Eloquent one to many relationship

Laravel Eloquent many to many relationship

Laravel Tinker

Tutorial Challenge

Summary

Please Subscribe

Do You Want Translate

Search here

Popular Posts

Category