Laravel: Database Management with Eloquent ORM

Laravel: Database Management with Eloquent ORM

Introduction

In Laravel, we have several ways to work with a database. One of them is using raw SQL queries and commands via the query builder.
The other way is using Eloquent ORM. Eloquent ORM is more common in everyday tasks since it simplifies our life
and saves our time in the most basic things like inserting data or updating it.

To be short, with Eloquent you have a PHP model class, for example `Product` or `Category`. The class maps on a database table, for instance `products` and `categories`. The class has attributes which map on the fields of the table. When you want to insert a model in the database (or update it), you just call the method `save()` on it. When you want to query for a model (or several models), you do that in almost the same way you would do with the query builder, by using the fluid interface.

Find open PHP jobs on our website

Let’s demonstrate how to use Eloquent ORM on an example of an e-commerce website. Such kind of websites is very popular ourdays.
On an e-commerce website you typically see the list of products, and you can manage them.
For simplicity, let’s have two tables: `categories` for product categories, and `products` for products. Each product may belong to one category. A category may have several products (this is also called the one-to-many relationship).

 

Migrations

In Laravel, you typically create database tables via migrations. A migration is a PHP class which has two methods: `up()` and `down()`.
The first one creates tables, the latter one reverts the database state back.

Let’s create a migration class that will create two tables for us.
Type the following command in your console:

 

```
php artisan make:migration create_two_tables
```

The Artisan tool will create a PHP migration file for you inside the *database/migrations* folder. Initially it will be just a stub, and
we will extend it to create our `categories` and `products` tables. The final contents of the file will be as presented below:

```php
 $table->string('name')->unique();
$table->timestamps();
});

Schema::create('products', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name', 512)->index();
$table->string('sku', 128)->unique();
$table->decimal('price')->default(0);
$table->unsignedInteger('category_id')->index();
$table->timestamps();
$table->foreign('category_id')
->references('id')->on('categories')
->onDelete('cascade');
});

}

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

We are trying to keep the tables as simple as possible:

  • The `categories` table will have the `id` field (auto-incremented unsigned integer),
    and the `name` field (string). This field will also have a unique index so users won’t be able
    to insert duplicate categories. The table will automatically contain timestamp fields `created_at` and `updated_at`.
  • The `products` table has the `id` field (auto-incremented unsigned big integer). We use the big integer because there may be a lot of products.
    The `name` (string), `sku` (string unique) and `price` (decimal) fields allow to define properties of a particular product. The `category_id` field sets a relationship with
    the `categories` table (it will have a foreign key attached, for checking the data integrity).

Once the migration file is ready, you need to apply this migration to your database by typing:

 

```
php artisan migrate
```

The above command will create the tables in your database.

If you are unhappy with the result and want to drop the tables and revert your database to the state before the migration, type this:

```
composer dump-autoload

php artisan migrate:rollback
```

For our e-commerse website, we need to insert some product categories. You can do that in your database shell by typing:

```
INSERT INTO categories(name) VALUES('Tools');
INSERT INTO categories(name) VALUES('Food');
INSERT INTO categories(name) VALUES('Clothes');
```

Eloquent Models

The next thing we will do is creating Eloquent models. As we said previously, an Eloquent model maps on a table.

Create two files in your *app* folder:

* *Category.php*

```php
 {
/**
* The table associated with the model.
*
* @var string
*/
protected $table = 'categories';
}
```

* *Product.php*

```php
 }
}
```

You can see that each model represents a database table. A model has the `$table` property telling Laravel which table to map.

An interesting thing to notice about the `Product` table is that it contains the `$fillable` property telling Laravel
which table fields may be mass-assigned by the user. This is for security.

And take a look at the `Product::category()` method which allows you to elegantly query the relationship of the `Product` model:

```php
// Get the category name of a product.
$product->category->name;
```

Using Eloquent Models

Once your model classes are ready, you can easily work with them without writing those boring SQL queries.

For instance, to insert a product into our database, use the following code:

```php
$product = new App\Product();
$product->name = 'Bottle of Water';
$product->sku = 'BW-12345';
$product->price = 12.50;
$product->category_id = 1;
$product->save();
```

You can mass-assign product attributes from your HTTP request (as you will do most commonly):

```php
$data = $request->all();
$product = new Product($data);
$product->save();
```

To find an already existing model, use the fluent interface of Eloquent ORM:

```php
// Find one product by ID
$product = Product::where('id', 1)->firstOrFail();

// Find one product by SKU
$product = Product::where('sku', $sku)->firstOrFail();

// Find several products by some criteria joined with AND
$product = Product::where('name', 'like', 'Bottle%')->where('price', '>=', 10)->get();
```

You can also update a product once you have found it:

```php
$product->price = 10.50; // discount the price
$product->save();
```

If you want to delete an existing product, use the following code:

```php
Product::where('id', 1)->delete();
```

With Eloquent, you can also retrieve relationships of a model easily:

```php $categoryName = $product->category->name; ``` Route

To demonstrate how to use the above in a real-life website, we will create web pages allowing to display the list of products, insert, view, edit and delete products.

To let Laravel know about these pages, add the following line to your *routes/web.php* file:

```php
Route::resource('products', 'ProductsController');
```

This will create a *route*, so you can, for instance, see the list of products by the following URL: *http://yourwebsite.com/products*.

Controller

We will create a resource controller file which will display our pages.
Create the file *app/Http/Controllers/ProductsController.php*:

```php
 }

/**
* Show the form for creating a new resource.
*
* @return \Illuminate\Http\Response
*/
public function create()
{
$categories = Category::get();

return view('products.create')->with('categories', $categories);
}

/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
$data = $request->all();
$product = new Product($data);
$product->save();

return redirect()->route('products.show', ['id' => $product->id]);
}

/**
* Display the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function show($id)
{
$product = Product::where('id', $id)->firstOrFail();

return view('products.show')->with('product', $product);
}

/**
* Show the form for editing the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function edit($id)
{
$categories = Category::get();
$product = Product::where('id', $id)->firstOrFail();

return view('products.edit')->with('product', $product)->with('categories', $categories);
}

/**
* Update the specified resource in storage.
*
* @param \Illuminate\Http\Request $request
* @param int $id
* @return \Illuminate\Http\Response
*/
public function update(Request $request, $id)
{
$data = $request->all();
$product = Product::where('id', $id)->firstOrFail();
$product->fill($data);
$product->save();

return redirect()->route('products.show', ['id' => $product]);
}

/**
* Remove the specified resource from storage.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function destroy($id)
{
Product::where('id', $id)->delete();

return redirect('products');
}
}
```

If you take a look at the controller action methods, you will see that we use the Eloquent ORM
interface described in a section above. It is really simple.

Views

To finalize our work, let’s create the Blade view scripts for each of our controller actions, plus we will
need a layout view (common to all our action views).

* *resources/views/layout.blade.php*

```php

@yield('content')
```

 

In the code above, we just define the HTML document and insert the Bootstrap 4 CSS framework into it.

* `resources/views/products/index.blade.php`

```php
@extends('layout')

@section('content')

Products

 Add Product

[email protected] ($products->count() == 0)@[email protected] ($products as $product)@endforeach
No products to display.
{{ $product->category->name }} {{ $product->name }} {{ $product->sku }} ${{ $product->price }} Edit

@method(‘DELETE’) @csrf

{{$products->count()}} product(s) in total. @endsection “`

In the code above, we just display an HTML table with the list of our products. If you now enter the URL http://yourwebsite.com/products, you should see something like below:

products

* *resources/views/products/create.blade.php*
```php
@extends('layout')
@section('content')

Create Product

@csrf @method(‘POST’)

 

 

 

@endsection “`

In the code above, we display a form for entering a new product. If you now enter the URL

http://yourwebsite.com/products/create, you should see something like below:

create_product

* *resources/views/products/edit.blade.php*

```php
@extends('layout')

@section('content')

Edit Product {{ $product->name }}
@csrf @method(‘PATCH’)

 

 

 

@endsection “`

In the code above, we just display a form for editing an existing product:
If you now enter the URL http://yourwebsite.com/products/1/edit, you should see something like below:

edit_product

* *resources/views/products/show.blade.php*

```php
@extends('layout')

@section('content')

Product

{{ $product->name }}
Products
Edit
@method(‘DELETE’) @csrf

Name: {{ $product->name }} SKU: {{ $product->sku }} Price: {{ $product->price }} Category: {{ $product->category->name }} @endsection “`

In the code above, we display the HTML code for rendering a product.

If you now enter the URL *http://yourwebsite.com/products/1/show*,
you should see something like below:

show_product

Conclusion

When you create a website with Laravel, you can use Eloquent ORM to simplify managing your database.
You can consider Eloquent ORM as a wrapper over the raw SQL queries and commands.

Each Eloquent model is related to its corresponding database table. This makes it easy to insert,
update and delete models. Eloquent ORM has a fluent interface for building queries.

Find open PHP jobs on our website

Leave a Reply

Your email address will not be published. Required fields are marked *