Laravel 8: MySQL Database Query Tip Sheet #4

Eloquent

Eloquent is an object-relational mapper (ORM) that make interacting with your database very simple. In the spirit of simplicity, this article will assume you have a basic knowledge of Laravel models, migrations, controllers and how they relate with a database. Now let’s take a look at how to use Eloquent to interact with a MySQL database using the following operations:

1. inserts
2. updates
3. deletes
4. selects

Case Scenario

Each model usually has a corresponding database table, controller and one or more migration files. Let’s assume we have a “students” table with a controller named StudentController and a model named Student. Each student has an id, name, age, email and address.

1. Inserts

To insert a record into a database table using a model, create an instance of the model and set its properties (columns) to the values you want. Here is an example of how to do this from within the StudentController.

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;
class StudentController extends Controller
{
    public function store(Request $request)
    {
        $student = new Student();
        $student->email = "someaone@gmail.com";
        $student->name = "Sherlock Holmes";
        $student->address = "221B Baker St.";
        $student->age = 98;
        $student->save();
        
    }
}

2. Updates

Updating a single row

To update a single row, you can use the find() method to fetch the record by its id after which you can set the properties you want to new values. After this call the save() method to persist the changes to the database.

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;
class StudentController extends Controller
{
    public function update(Request $request)
    {
        $student = Student::find(2);
        $student->name = "Kenpachi Zaraki";
        $student->email = "newemail@gmail.com";
        $student->save();
    }
}

Update Multiple Columns

To update multiple columns, you can use the update() method. Here is an example which will update the email column for all rows with the name “Sherlock Holmes”:

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;
class StudentController extends Controller
{
    public function update(Request $request)
    {
        
       $student = Student::where('name', "Sherlock Holmes")
           ->update(['email' => "trainstation@gmail.com"]);
    }
}

3. Deletes

Deleting a single row

To delete a single row, you can use the find() method to fetch the record by its id and use the delete() method to get rid of it.

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;
class StudentController extends Controller
{
    public function destroy(Request $request)
    {
        
      $student = Student::find(1);
      $student->delete();
    }
}

Delete Multiple row

Deleting multiple rows is like updating multiple columns. You can use constraining methods to specify the rows you wish to delete; then you can use the delete() method on them. Here is an example:

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;
class StudentController extends Controller
{
    public function destroy(Request $request)
    {
        
     $student = Student::where('name', 'Sherlock Holmes')->delete();
    }
}

4. Selects

Fetching all rows in a table

The all() method returns all records inside a table.

Student::all();

Selects and the query builder

Most of the constraining and returning methods discussed in tip sheet #2 and tip sheet #3 can be used here. Here are some examples:

Student::select('name', 'email')->first();
Student::select('name', 'email')->get();
Student::where('name', 'Kenpachi Zaraki')->get();
Student::where('name', 'Kenpachi Zaraki')
  ->orWhere('name', 'Tyler Harvey')
  ->get();

Here ends our Laravel 8 tip sheet series. It’s been fun; all the best !!!

Related Articles

Laravel 8: MySQL Database Query Tip Sheet #1

Laravel 8: MySQL Database Query Tip Sheet #2

Laravel 8: MySQL Database Query Tip Sheet #3