Query Builder and Eloquent ORM(Object-relational mapping) – Laravel

Eloquent ORM(Object-relational mapping) vs query builder

Today I’ll let you know about the working with DB in 2 different way using laravel framework:

1. Query Builder

2. Eloquent ORM (Object-relational mapping)

1. Query Builder

So without wasting much time we start what is query builder, eloquent ORM and examples query using that.

Laravel’s database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works on all supported database systems.

You may use the table method on the DB facade to begin a query. The table method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally get the results using the get method:

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::table('users')->get();

        return view('user.index', compact('users'));
    }
}
?>

The get method returns an Illuminate\Support\Collection containing the results where each result is an instance of the PHP stdClass object. You may access each column’s value by accessing the column as a property of the object:

<?php
foreach ($users as $user) {
    echo $user->email; // display email from user table.
}
?>

Retrieving A Single Row / Column From A Table

If you just need to retrieve a single row from the database table, you may use the first method. This method will return a single stdClass object:

<?php
 $user = DB::table('users')->where('company', 'EcodeBlog')->first(); 
 // this will return first record from DB
 echo $user->name;
?>

Using first() you will get the first record from the DB.

If you don’t even need an entire row, you may extract a single value from a record using the value method. This method will return the value of the column directly:

<?php
 $email = DB::table('users')->where('name', 'John')->value('email');
  echo $email; // For fetching single value from record using value() method
?>

Retrieve a single row by its id column value, use the find method:

<?php
  $user = DB::table('users')->find(3);  // Find by id
?>

Retrieving A List Of Column Values

If you would like to retrieve a Collection containing the values of a single column, you may use the pluck method. In this example, we’ll retrieve a Collection of role titles:

<?php
 $titles = DB::table('roles')->pluck('title');
 foreach ($titles as $title) {
     echo $title;
 }
?>

You may also specify a custom key column for the returned Collection:

<?php
$roles = DB::table('users')->pluck('name', 'email');

foreach ($roles as $name => $email) {
    echo $email;
}
?>

2. Eloquent ORM (Object-relational mapping)

The Eloquent ORM in that Each database table has a “Model” which is used to interact with that table. Models allow you to query for data in your tables, as well as insert new records into the table.

To get started, let’s create an Eloquent model. Models typically live in the app directory, but you are free to place them anywhere that can be auto-loaded according to your composer.json file. All Eloquent models extend Illuminate\Database\Eloquent\Model class.

The easiest way to create a model instance is using artisan command

php artisan make:model User

if you would like to generate a database migration with generate the model, you may use the –migration or -m option:

php artisan make:model User --migration
php artisan make:model User -m

Eloquent Model Protocol

Let’s look at an example User model, which we will use to retrieve and store information from our users database table:

If you add table name in model then it will take same name but if you want to rename and add custom name to table you can use following way, for the primary key it’s same use keyword for primary key and add field name

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model{
   protected $table = 'tbl_users';
   protected $primaryKey = 'user_id';
}

?>

Add Timestamps inside model

By default, Eloquent expects created_at and updated_at columns to exist on your tables. If you do not wish to have these columns automatically managed by Eloquent, set the $timestamps property on your model to false

<?php
namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model{
     public $timestamps = false;
}
?>

Retrieving data using Models

Once you have created a model and its associated database table, you are ready to start retrieving data from your database. Think of each Eloquent model as a powerful query builder allowing you to fluently query the database table associated with the model.

<?php
$users = App\User::all();
foreach ($users as $user) {
    echo $user->name .' - '. $user->email;
}
?>

The Eloquent all method will return all of the results in the model’s table. 

You can use same method as query builder have for getting the record.

How you decide that you have to Query builder / Eloquent ORM

When you have tables in application and not models with you then you may use Query Builder.

When you have models available in application then you can go with the Eloquent ORM

Basically if you want to use Eloquent ORM then it’s necessary to have Model with you for that table and for query builder it’s not necessary to have Models with you.

I hope you clear now with Query Builder and Eloquent ORM 🙂 🙂 🙂

Let me know if you have any question in comment, Thank you 😀

“For more on ORM and other Laravel essentials, check out this Laravel hiring guide

Leave a Reply

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