Learning Management Platform for Written Tutorial Series.

Laravel 5 Eloquent Relationships

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.

Laravel Tinker

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 Users Roles Permissions

Laradmin Transaction Tables

Laradmin Database Dictionary

The following table describes the database tables and fields for our Laravel admin panel. Author: Rodrick Kazembe | Date: 2017-03-10 | 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
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

Laravel Eloquent Relationship

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

Laravel Tinker

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

Laravel Tinker

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

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

Laravel Tinker

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.


...