Laravel 5 Eloquent Relationships

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. 

Topics to be covered

In this tutorial, we will cover the following topics

• Tutorial prerequisites
• Tutorial Starter Code
• 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 Complete Code

Tutorial prerequisites

This tutorial assumes;

• You understand the basics of Laravel 5. If you do then I recommend you start with these Laravel 5 Tutorial series.
• You have PHP, MySQL and a web server i.e. Apache up and running
• Laravel Homestead – this is optional but provides a great development environment. If you are new to Laravel homestead then I recommend you read the series on Laravel Homestead
• You have composer installed
• You have a text editor or IDE that supports PHP.
• You have a modern web browser that supports HTML5
• Git – this is optional but highly recommended. This tutorial has a repository on GitHub, with Git, you will be able to clone starter code for each tutorial.

Tutorial Starter Code

This tutorial builds on from the previous tutorial. If you have not been following the tutorial series, then you can download starter code for this tutorial from GitHub
If you have git installed, run the following command to clone the project

git clone -b 03_views https://github.com/KodeBlog/Laradmin.git laradmin

HERE,

• git clone -b 03_views clones the branch laradmin from the repository https://github.com/KodeBlog/Laradmin.git into a local folder laradmin.

Laradmin Database Entity Relationship Diagram (ERD)

The Entity Relationship Diagram (ERD) is a modeling 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.
We already looked at the diagrams in the above example. In the next section, we will translate the ERD diagram into a database dictionary.

Laradmin Database Dictionary

The following table describes the database tables and fields for our Laravel admin panel.

 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
3	deleted_at	Timestamp	Timestamp when the record was deleted

USERS, ROLES AND PERMISSIONS

Table: Users | contains records of all registered users

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	VARCHAR(255)	Primary key
2	name	VARCHAR(255)	The user’s name
3	email	VARCHAR(255)	The user’s email address
4	password	VARCHAR(255)	The user’s password
5	remember_token	VARCHAR(100)	The remember me token for logged in users

Tables: Password Resets | used to keep track to reset password requests from the users.

S/N	FIELD	DATA TYPE	DESCRIPTION
1	email	VARCHAR(255)	User’s registered email address
2	token	VARCHAR(255)	Password reset token

Table: Roles | contains user roles. system users will have roles which will be used for authorization purposes.

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	VARCHAR(255)	Primary key
2	name	VARCHAR(255)	The name of the role i.e. admin
3	display_name	VARCHAR(255)	The name that we will display when listing the roles
4	description	VARCHAR(255)	The description of the user role

Table: Role User | intermediate table for linking users to roles. Nature of the relationship | At any given time, a single user can have more than one role and a single role can belong to more than one user. Therefore, the nature of the relationship is many to many.

S/N	FIELD	DATA TYPE	DESCRIPTION
1	user_id	INT (10)	User’s id in users table
2	role_id	INT (10)	Role id in roles table

Table: Permissions | contains role permissions. Users will only be able to perform an action that they have been authorized to perform.

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT	Primary key
2	name	VARCHAR(255)	Permission name
3	display_name	VARCHAR(255)	Human readable permission name

Table: Permission Role | links the permissions and roles tables. Nature of the relationship | At any given time, a single role can have more than one permission and a permission can belong to more than one role. Therefore, the nature of the relationship is many to many.

S/N	FIELD	DATA TYPE	DESCRIPTION
1	permission_id	INT	Permission id
2	role_id	INT	Role id

Transactions Tables

Table: Brands | contains product brands

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT	Primary key
2	name	VARCHAR(255)	The name of the brand
3	description	VARCHAR(255)	The description of the brand

Table: Categories | contains product categories

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT	Primary key
2	name	VARCHAR(255)	The name of the category
3	description	VARCHAR(255)	The description of the category

Table: Products | contains products

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT	Primary key
2	product_code	VARCHAR(255)	A unique code that identifies the product
3	product_name	VARCHAR(255)	The name of the product
4	description	VARCHAR(255)	The description of the product
5	price	VARCHAR(255)	The price of the product
6	brand_id	INT(10)	The brand id foreign key
7	category_id	INT(10)	The category id foreign key

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

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT	Primary key
2	first_name	VARCHAR(255)	Customer's first name
3	last_name	VARCHAR(255)	Customer's lastname
4	email	VARCHAR(255)	Email address for the customer
5	postal_address	VARCHAR(255)	Postal address for the customer
6	physical_address	VARCHAR(255)	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	Primary key
2	order_number	VARCHAR(255)	Auto generated order unique number
3	transaction_date	TIMESTAMP	Date that the order was placed
4	customer_id	INT	The id for the customer who placed the order
5	status	VARCHAR(45)	Status can either be pending or processed
6	total_amount	DOUBLE(8,2)

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

 

S/N	FIELD	DATA TYPE	DESCRIPTION
1	id	INT	Primary key
2	order_id	INT(10)	Order id
3	product_id	INT(10)	Product primary key value
4	quantity	INT(11)	The quantity of the ordered product
5	price	DOUBLE(8,2)	The price of the product at the time of the order
6	sub_total	DOUBLE(8,2)	The total of the quantity multiplied by the price

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.
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
Browse to the root of the project.
Let’s start with the model for the brands table. As a convention, the model name should be singular. The created table name will be plural.
Run the following command

php artisan make:model Models\Brand –m

HERE,

• artisan make:model Models\Brand creates a model Brand in the directory app/Models. The parameter –m tells artisan to also create the respective database migration file.

The following artisan commands will create the rest of the models and migration files required for now. Note: we will not create models and migration files for roles, permissions, role user and permission role. We will cover these when we look at Laravel authentication and authorization.

php artisan make:model Models\Category -m
php artisan make:model Models\Product -m

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

Database migration files

We will now update the generated migration tables with the actual code that will create the various tables and relationships that we looked at in the data dictionary above.
Migration files are created in /database/migrations directory. We will be using soft deletes on our models. A soft delete does not completely remove the record from the database but marks it as deleted with a timestamp. Towards that end, all of our table definitions will add the field $table->softDeletes();
Update the respective migration files as follows
Open /database/migrations/timestamp_create_brands_table.php

<?php

use Illuminate\Support\Facades\Schema;
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')->unique();
            $table->string('description')->nullable();
            $table->timestamps();
            $table->softDeletes();
        });
    }

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

Open and modify /database/migrations/timestamp_create_categories_table.php as follows

<?php

use Illuminate\Support\Facades\Schema;
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')->unique();
            $table->string('description')->nullable();
            $table->timestamps();
            $table->softDeletes();
        });
    }

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

Open and modify /database/migrations/timestamp_create_products_table.php as follows

<?php

use Illuminate\Support\Facades\Schema;
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('product_code')->unique();
            $table->string('product_name')->unique();
            $table->string('description');
            $table->float('price')->default(0);
            $table->integer('brand_id')->unsigned()->index();
            $table->integer('category_id')->unsigned()->index();
            $table->timestamps();
            $table->softDeletes();

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

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

HERE,

• $table->integer('brand_id')->unsigned()->index(); 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->integer('category_id')->unsigned()->index(); creates the foreign key category_id.
• $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.
• $table->foreign('category_id')->references('id')->on('categories'); creates a relationship between products table and categories table using the id in categories and category_id in products table.

Open and modify /database/migrations/timestamp_create_customers_table.php as follows

<?php

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

class CreateCustomersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('customers', function (Blueprint $table) {
            $table->increments('id');
            $table->string('first_name');
            $table->string('last_name');
            $table->string('email')->unique();
            $table->string('postal_address');
            $table->string('physical_address');
            $table->timestamps();
            $table->softDeletes();
        });
    }

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

Open and modify /database/migrations/timestamp_create_orders_table.php as follows

<?php

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

class CreateOrdersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('orders', function (Blueprint $table) {
            $table->increments('id');
            $table->string('order_number')->unique();
            $table->string('transaction_date');
            $table->integer('customer_id')->unsigned()->index();
            $table->float('total_amount');
            $table->string('status');
            $table->timestamps();
            $table->softDeletes();

            $table->foreign('customer_id')->references('id')->on('customers');
        });
    }

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

HERE,

• $table->string('order_number')->unique(); the order number will be unique and auto generated by the program
• $table->integer('customer_id')->unsigned()->index(); creates an indexed foreign key customer_id
• $table->foreign('customer_id')->references('id')->on('customers'); creates a relationship between orders and customers using the id in customers and customer_id in orders

Open and modify /database/migrations/timestamp_create_ order_details_table.php as follows

<?php

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

class CreateOrderDetailsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('order_details', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('order_id')->unsigned();
            $table->integer('product_id')->unsigned()->index();
            $table->integer('quantity');
            $table->float('price');
            $table->float('sub_total');
            $table->timestamps();
            $table->softDeletes();

            $table->foreign('order_id')->references('id')->on('orders');
        });
    }

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

HERE,

• $table->integer('order_id')->unsigned(); Creates the foreign key order_id
• $table->integer('product_id')->unsigned()->index(); creates the foreign key product_id
• $table->foreign('order_id')->references('id')->on('orders'); creates a relationship between orders and order_details using id on orders table and order_id on order_details table.

Run the following artisan command to create the tables in the database.

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.
The following image shows the Eloquent ORM Object Relationship Diagram

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.
Let’s start with the Brand model
Open /app/Models/Brand.php
Replace the code with the following.

<?php

namespace Larashop\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Brand extends Model
{
    use SoftDeletes;

    /**
     * The attributes that should be mutated to dates.
     *
     * @var array
     */
    protected $dates = ['deleted_at'];

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name',
        'description',
    ];

    /**
     * Get the products for the brand.
     */
    public function products()
    {
        return $this->hasMany('Larashop\Models\Product','brand_id','id');
    }
}

HERE,

• use Illuminate\Database\Eloquent\SoftDeletes; imports the soft deletes trait
• use SoftDeletes; applies the trait to the model Brand.
• protected $fillable = ['name', 'remarks']; specifies fields that can be mass assigned
• public function products() { return $this->hasMany('Larashop\Models\Product','brand_id','id'); } hasMany defines a one to many relationship between 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 _id is appended to it to determine the foreign key brand_id. Alternatively, the relationship can be defined as return $this->hasMany('Larashop\Models\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('Larashop\Models\App\Brand');
}

HERE,

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

Laravel Eloquent many to many relationship

In the tutorial project, for the sake of simplicity, the relationship between categories and products is one to many.
But for completeness ‘sake we will assume that the nature of the relationship is many to many. We will need an intermediate table for that called product_category We will now create a many to many relationship between Product and Category models via the intermediate table.

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

HERE,

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

Laradmin Models

The code for the other models is as follows
Open and modify /app/Models/Category.php as follows

<?php

namespace Larashop\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Category extends Model
{
    use SoftDeletes;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name',
        'description',
    ];
}

Open and modify /app/Models/Customer.php as follows

<?php

namespace Larashop\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Customer extends Model
{
    use SoftDeletes;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'first_name',
        'last_name',
        'email',
        'postal_address',
        'physical_address',
    ];
}

Open and modify /app/Model/Order.php as follows

<?php

namespace Larashop\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Order extends Model
{
    use SoftDeletes;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'order_number',
        'transaction_date',
        'customer_id',
        'total_amount',
        'status',
    ];
}

Open and modify /app/Models/OrderDetail.php as follows

<?php

namespace Larashop\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class OrderDetail extends Model
{
    use SoftDeletes;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'order_id',
        'product_id',
        'quantity',
        'price',
        'sub_total',
    ];
}

Open and modify /app/Models/Product.php as follows

<?php

namespace Larashop\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Product extends Model
{
    use SoftDeletes;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'product_code',
        'product_name',
        'description',
        'price',
        'brand_id',
        'category_id',
    ];

    /**
     * Get the brand that the product belongs to.
     */
    public function brand()
    {
        return $this->belongsTo('Larashop\Models\Brand','brand_id');
    }

    /**
     * Get the category that the product belongs to.
     */
    public function category()
    {
        return $this->belongsTo('Larashop\Models\Category','category_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.
Browse to the root of the project
Run the following artisan command to run tinker

php artisan tinker

You will be presented with the following console window

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

$brand = new Larashop\Models\Brand;

HERE,

• $brand = new Larashop\Models\Brand; creates a php variable $brand and initializes it to an instance of the Brand model. Notice we included the namespace for the model too Larashop\Models.

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->description = "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

Your console window should now appear as follows

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

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 Larashop\Models\Product;

$product->product_code = "T-Shirt";
$product->product_name = "T-Shirt";
$product->description = "Men's cool T-Shirt";
$product->price = "65";
$product->brand_id = "1";
$product->category_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

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 [Simple]: use tinker to

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

Complete Tutorial Code

The complete code for this tutorial can be cloned from the following branch

git clone -b 04_database https://github.com/KodeBlog/Laradmin.git laradmin

Run the following command to browser to the root of the project

cd laradmin

We will now use composer to install dependencies.

composer install

When the installation is completed, run the following artisan command

php artisan serve

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.

What’s next?

The next tutorial will be Laravel 5 Eloquent Relationships. We will create the database for our admin panel using migrations and define the table relationship.
Kode Blog Tutorials is dedicated to bring you update to date, high quality free tutorials. You can support us by using the social media buttons to like and share the tutorial and subscribing to our newsletter. Please use the comments section below to give us feedback.
Each week, a new Laravel tutorial is added to our collection. Subscribe to our newsletter, like our Facebook fan page or follow us on Twitter to get free updates when the collection is updated.