Develop an E-Commerce Website With Laravel 5.4 - Part 5

All posts in this series:

• Develop an E-Commerce Website With Laravel 5.4 - Part 1
• Develop an E-Commerce Website With Laravel 5.4 - Part 2
• Develop an E-Commerce Website With Laravel 5.4 - Part 3
• Develop an E-Commerce Website With Laravel 5.4 - Part 4
• Develop an E-Commerce Website With Laravel 5.4 - Part 5

In the last post, I covered Laravel Dusk which provides an easy way to write integration tests in Laravel. In this post I am going to introduce what Laravel offers when it comes to write database driven applications. I will still be focusing on Homepage. I will make a basic Db schema for our application.I will then be using migrations to create tables and seeders for inserting some initial data for testing.

Db Design

The home page consist of 2 sections that are fetching data from database: Hot Offers and Top Products. Before I start working on it I also have to create main product and category tables to show hot and top products.Category is repeated across the site. Basic Schema given below:

Category

• id - Autoincrement Integer
• name - Name of the category
• status - Active/Inactive
• timestamps: created_at and updated_at fields generated by Laravel
• deleted_at - Soft Delete field as we will not delete the data in real

Product

• id - Autoincrement Integer
• title - Title of the product
• original_price - The original price
• discount_price - Promotional Price
• in_stock - Boolean value for whether the product is in stock or not
• status - Active/Inactive
• timestamps: created_at and updated_at fields generated by Laravel
• deleted_at - Soft Delete field as we will not delete the data in real

Hot Offers

• id - Autoincrement Integer
• product_id - Product Id set for Hot Offer
• timestamps: created_at and updated_at fields generated by Laravel
• deleted_at - Soft Delete field as we will not delete the data in real

Top Products

• id - Autoincrement Integer
• product_id - Product Id set for Hot Offer
• timestamps: created_at and updated_at fields generated by Laravel
• deleted_at - Soft Delete field as we will not delete the data in real

Initial Schema is ready, it's time to create migrations of these tables.

Laravel Migrations

Migration or Scheme Migration is a way similar to version of your source code. Just like you document your code changes and can revert to any version, similarly schema migration helps you to manage your schema changes and provides you ability to revert to previous version and document each change. You don’t need to worry about whether your db changes in some .sql file has been applied or not since migration tool will take care of it. Migration concept is available in all modern MVC frameworks in different languages.

Generating migrations in Laravel is quite easy, it's just all about to execute another artisan command. First, let's create migration for categories:

  php artisan make:migration php artisan make:migration create_categories_table

create_users_table is like alias, when the file is generated it will be stored in database/migrations folder with that alias along with timestamp.

When this migration runs, it will create a table with name categories. Laravel is smart enough that it will extract the table name from create_categories_table.

Similar command can also be called as:

php artisan make:migration create_categories_table --create=categories

--create tells the command that it has to create a table with name categories. This is useful in the case when you want to follow some other naming convention for your migration files but want to use some other convention for table names.

Alright, so command is executed and it generated file for me:

➜  golmarket.dev git:(master) ✗ php artisan make:migration create_categories_table --create=categories
Created Migration: 2017_06_17_012440_create_categories_table
➜  golmarket.dev git:(master) ✗ 

Timestamp would be different for you as it indicates the moment file was generated on your machine. Now I go go to database/migrations folder and open the file 2017_06_17_012440_create_categories_table which would initially look like this:

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

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

Migration created a class name with the name mentioned in alias. After required imports you should focus on two methods: up and down. up() is used for table creation while down() for dropping the table. You will get to know further about it later. I am going to make changes in up() method which will now look like this:

public function up() {
    Schema::create( 'categories', function ( Blueprint $table ) {
      $table->increments( 'id' );
      $table->string( 'name', 200 );
      $table->tinyInteger( 'status' )->default( 0 );
      $table->timestamps();
      $table->softDeletes();
    } );
  }

Self explainatory, Schema::create takes categories as input table name and then function parameter to define schema properties. $table->timestamps() for creating created_at and updated_at fields while $table->softDeletes(); will create deleted_at field. Now let's run this newly created migration.

Vagrant and DB operations

Before I move further, let me clarify that since I am using Homestead/Vagrant box, the DB is running within the guest machine hence localhost is local within the machine. Thefore, if you run migrate command outside of the Vagrant it will give you error like this:

➜  golmarket.dev git:(master) ✗ php artisan migrate

                                                                                                                                                               
  [Illuminate\Database\QueryException]                                                                                                                         
  SQLSTATE[HY000] [1045] Access denied for user 'homestead'@'localhost' (using password: YES) (SQL: select * from information_schema.tables where table_schem  
  a = homestead and table_name = migrations)                                                                                                                   
                                                                                                                                                               

                                                                                               
  [PDOException]                                                                               
  SQLSTATE[HY000] [1045] Access denied for user 'homestead'@'localhost' (using password: YES)

So go to the folder where Homestead exists, in my case it is in my home directory and run the command vagranat ssh to get into vagrant box, navigate to your project folder and run the following command to create db table:

php artisan migrate

If works well it would generate following tables:

vagrant@homestead:~/LaraProjects/golmarket.dev$ php artisan migrate
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table
Migrating: 2017_06_17_012440_create_categories_table
Migrated:  2017_06_17_012440_create_categories_table

You can see some some extra migrations that were executed. Laravel provides a basic structure of user registration and login system which you can use in your applications. artisan migrate run all the unmigrated files in sequential order. If you want to run a selected set of migrations then you may pass the --path switch to run selected migrations. For instance:

php artisan migrate --path=/database/migrations/selected/

OK, once it run, it will create tables for categories and password_resets and users. Beside that it also created a table with name, migrations which is being user for version controlling. In my case it looks like below:

migration column holds the name of migrations while batch tells the session in which these files were run. Since this was the very first migration, all migrations belongs to first batch.

In case you want to rollback the migration, all you have to do is to run the following:

php artisan migrate:rollback

And it will produce the following output:

vagrant@homestead:~/LaraProjects/golmarket.dev$ php artisan migrate:rollback
Rolling back: 2017_06_17_012440_create_categories_table
Rolled back:  2017_06_17_012440_create_categories_table
Rolling back: 2014_10_12_100000_create_password_resets_table
Rolled back:  2014_10_12_100000_create_password_resets_table
Rolling back: 2014_10_12_000000_create_users_table
Rolled back:  2014_10_12_000000_create_users_table

Also, migrations table is now empty, I ran the the command again and it got created. The migrations table will now look like:

Notice the id field which is not reset. It is because that internal mechanism executed the DELETE query to remove the data.

rollback just rolledback the very last batch of migration. What if you want to rollback all migrations? for that purpose, run the command:

php artisan migrate:reset

Similarly, php artisan migrate:refresh will first rollback all of your migrations and then will create all of them again.

When you run migrate:rollback, The down() method is called. By default it drops table like Schema::dropIfExists( 'categories' );. You can also write custom logic here. For instance you want to backup entire db before rolling back so this method would be helpful for that purpose.

Like categories we will be creating migrations for products, hot products and top products

vagrant@homestead:~/LaraProjects/golmarket.dev$ php artisan make:migration create_products_table
Created Migration: 2017_06_17_050426_create_products_table
vagrant@homestead:~/LaraProjects/golmarket.dev$ php artisan make:migration create_hot_products_table
Created Migration: 2017_06_17_050437_create_hot_products_table
vagrant@homestead:~/LaraProjects/golmarket.dev$ php artisan make:migration create_top_products_table
Created Migration: 2017_06_17_050443_create_top_products_table
vagrant@homestead:~/LaraProjects/golmarket.dev$ 

For products up() method look like this:

public function up() {
    Schema::create( 'products', function ( Blueprint $table ) {
      $table->increments( 'id' );
      $table->integer( 'category_id' )->unsigned();
      $table->string( 'title' );
      $table->double( 'original_price' );
      $table->double( 'discount_price' );
      $table->tinyInteger( 'in_stock' )->default( 1 );
      $table->tinyInteger( 'status' )->default( 0 );
      $table->timestamps();
      $table->softDeletes();

      $table->foreign( 'category_id' )
            ->references( 'id' )->on( 'categories' )
            ->onDelete( 'cascade' )
            ->onUpdate( 'cascade' );
    } );


  }

notice $table->foreign which defines foriegn key. Here category_id is FK for categories table. If a category is removed, all of it's products will get removed as well.

For hot products:

public function up() {
    Schema::create( 'hot_products', function ( Blueprint $table ) {
      $table->increments( 'id' );
      $table->integer( 'product_id' )->unsigned();
      $table->timestamps();
      $table->softDeletes();

      $table->foreign( 'product_id' )
            ->references( 'id' )->on( 'products' )
            ->onDelete( 'cascade' )
            ->onUpdate( 'cascade' );
    } );
  }

For Top Products

  Schema::create( 'top_products', function ( Blueprint $table ) {
      $table->increments( 'id' );
      $table->integer( 'product_id' )->unsigned();
      $table->timestamps();
      $table->softDeletes();

      $table->foreign( 'product_id' )
            ->references( 'id' )->on( 'products' )
            ->onDelete( 'cascade' )
            ->onUpdate( 'cascade' );
    } );

Now our required tables are created, it's time to fill them up with some data by using Seeding

What is Seeding?

Seeding is a process to pre-fill database with data. This process is helpful if you want to avoid data submission forms to store data just for sake of making it available on page.

Creating Seeders in Laravel is nothing but another artisan command, here I am going to create seeder for categories:

php artisan make:seeder CategoriesTableSeeder

This will create the class CategoriesTableSeeder in database/seeds

We have to implement run() method. Here I take $categories array with some categories to be stored:

public function run() {
    $categories = [ 'Branded Foods', 'Households', 'Veggies & Fruits', 'Kitchen', 'Bread & Bakery' ];
    foreach ( $categories as $category ) {
      DB::table( 'categories' )->insert( [
        'name'       => trim( strtolower( $category ) ),
        'status'     => 1,
        'created_at' => Carbon::now(),
      ] );
    }

  }

On eaach iteration it is fetching array element and creating a record. For date functions I am using Carbon. Carbon is shipped with Laravel and all of it's date instances are of type Carbon as well.

To make things happen run the following artisan command from vagrant:

php artisan db:seed --class=CategoriesTableSeeder

If all goes well data will be stored in categories table. Now let's make ProductTableSeeder

php artisan make:seeder ProductsTableSeeder

A new seeder class will be generated in database\seeds.

public function run() {
    $categories = [ 'Branded Foods', 'Households', 'Veggies & Fruits', 'Kitchen', 'Bread & Bakery' ];
    foreach ( $categories as $category ) {
      # First fetch category Id
      $category        = DB::table( 'categories' )->where( 'name', '=', trim( strtolower( $category ) ) )->get();
      $category_id     = $category[0]->id;
      $actual_price    = rand( 15.5, 100.5 );
      $discount_factor = ( 10 / 100 ) * $actual_price; // 10% of actual price
      $discount_price  = $actual_price - $discount_factor;

      DB::table( 'products' )->insert( [
        'title'          => 'Product for ' . $category[0]->name,
        'category_id'    => $category_id,
        'original_price' => $actual_price,
        'discount_price' => $discount_price,
        'in_stock'       => 1,
        'status'         => 1,
        'created_at'     => Carbon::now(),
      ] );

    }
  }

I created random product title based on existing category and associated category_id with each product.
While working on product seeder I found a field missing. Guess which one? yeah, image field. Now let's quickly create a migration for adding an image field.

php artisan make:migration add_image_to_products_table --table=products

It will create a new migration file with name add_image_to_products_table. Notice --table switch here. The up() function looks like this:

public function up()
    {
        Schema::table('products', function (Blueprint $table) {
            $table->string('image')->after('title');
        });
    }

Self explainatory. The image field will be added after title field.

The down() function now looks like this:

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

So unlike creation of table, what we want that when migrate:rollback is run it simply removes the newly added column.

Getting back to ProductTableSeeder, run() now look like this:

DB::table( 'products' )->insert( [
        'title'          => 'Product for ' . $category[0]->name,
        'image'          => 'http://via.placeholder.com/600x400',
        'category_id'    => $category_id,
        'original_price' => $actual_price,
        'discount_price' => $discount_price,
        'in_stock'       => 1,
        'status'         => 1,
        'created_at'     => Carbon::now(),
      ] );

I am using placeholder.com for image place holders. The size I picked will be used for individual product page. Run the seeder again and this time image urls should be there.

Let's quickly create seeders for hot Products and top products:

php artisan make:seeder HotProductsTableSeeder

run() looks like:

public function run() {
    $product = DB::table( 'products' )->where( 'title', '=',
      trim( strtolower( 'Product for branded foods' ) ) )->get();

    $product_id = $product[0]->id;
    DB::table( 'hot_products' )->insert( [
      'product_id' => $product_id,
      'created_at' => Carbon::now(),
    ] );
  }

Same will go with Top Products so I am not adding it here.

That's it for now. Wow! this post became quite lengthy. Sorry for that but it was necessary to cover it up in a single post. In next post I will discuss how to display these seeded data onto our Homepage. Stay tuned.

The updated code is available on Github.