Laravel Eloquent ORM Query and Model Feature

Laravel Eloquent feature is object Oriented paradigm approach of database communication. In Eloquent we create Model for each Table in the database, which set the communication (Fetching, Inserting, Deleting, Modifying the Record ) with database.

You can create the Model with Migration or without migration with the help of Artisan Command. We can use -m or --migration after the artisan command to create the Model with Migration. Below code  is the Example.

Eloquent Model with Database Migration

Create Model with migration using Artisan Command. Below is the command to create Model with migration.

  php artisan make:model model-Name  -m (-m is for migration )

                  php artisan make:model model-Name  --migration (--migration is for migration )

This above two Artisan Command will create Table migration file in your databaese / migration directory which you can modify further.

Create Model without Migration. Below is the Artisan command to create Model.

  php artisan make:model model-Name 

Lets Take Example, We will create Model Named with CategoryMaster 

php artisan make:model CategoryMaster  -m

This above Artisan Command will create the Model named with CategoryMaster.  Below is the code for your reference   

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class CategoryMaster extends Model
{     // }

Eloquent Model Property

In the above model, we didn't mention the table. So we have to declare the table in CategoryMaster Model.

protected  $table = '"category_table";

In Eloquent Model, Eloquent consider by default each table has a primary key id with incrementing integer Value. How to Define a protected Primary key property:

protected  $primaryKey = '"id";

protected  $incrementing = '"false";

if Primary Key is not an integer then you must set the KeyType a string.

protected $keyType = 'string';

Once the Table is declared in the Model, By Default timestamp (Created_at, updated_at) will be true. If you don't want them then you have to set the timestamp to false.

protected $timeStamp = "false";

Eloquent Model and Database Connection

Eloquent model will use Default Database which got configured with your laravel Application. if you wish to have your model connected with other database then you have to use $connection Property. Code is below for your reference.

protected $connection = "Connection-Database-Name"; 

Fetch / Retrieve Model In Eloquent

Once the Model is Created with Associated table, We can perform all the database operation(CRUD) on it. We will Query / fetch database table associated with model step by step.

Above we have created CategoryMaster  Model, Now we will use this model for writing our Eloquent Query as a Example.

$data= App\CategoryMaster::all();

all() method in Eloquent will return all result from the specified model table. We can use various constraint on the all method to filter the table record.

$data = App\CategoryMaster::where('status', 1)->orderBy('id', 'desc')
        ->take(5)->get();

As Eloquent is Query Builder you can use all the Query Builder method for database Operation.

Laravel Query Builder Select, Join and Where

Query Builder in Laravel, from the Scratch

Query Builder in Larevel is a Package through which we create and run database query Quickly to run the application smoothly. Query Bulider Use PHP Data Object (PDO) where we dont have to worry about the SQL Injection. It can perform all Database Operation such as CRUD, DB Connection, Aggregate Function etc.

We will apply all the Laravel Query Builder Concept on the personal_detail table. Create Table Named With personal_detail,   

CREATE TABLE `personal_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(300) DEFAULT NULL,
  `dob` date DEFAULT NULL,

  `marks` int(7) DEFAULT NULL,

  `statusflag` int(7) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41092 DEFAULT CHARSET=latin1

Insert Data in personal_detail Table.

⧉  Query to Fetch / Retrieve using get() method all the data from personal_detail Table, We use get() method to do that.

$fetchData = DB::table('personal_detail ')->get();

 ⧉  Query to Fetch using first() method single row from personal_detail Table, We use first() method to do that.

$fetchData = DB::table('personal_detail ')->where('id','=',4)->first();

 ⧉  Use value() method to get single value from the table record if you don't need the entire row from personal_detail Table, We use value() method to do that.

$fetchData = DB::table('personal_detail ') 

->where('id','=',4) 

->value('student_name');

⧉  Use find() method to get the particular row using ID Column.

$fetchData = DB::table('personal_detail ')->find(5);

⧉  Use pluck() method to get the list of required column from the personal_detail Table.

$fetchData = DB::table('personal_detail ')->pluck('student_name');

Laravel Query Builder Aggregate Function

count(), max(), min(), sum(), avg() is aggregate method in Query Builder.

⧉  Use below Query to uderstan the aggregate function usage in Query Bulider.

$fetchData = DB::table('personal_detail ')->max('marks');

$fetchData = DB::table('personal_detail ')->min('marks'); 

$fetchData = DB::table('personal_detail ')->count();

$fetchData = DB::table('personal_detail ')->avg('marks');

 Laravel Query Builder SELECT

Sometime we want to multiple Column from the particular table, at that time we use select() method 

$fetchData = DB::table('personal_detail ')

->select('student_name','dob')->get();

$fetchData = DB::table('personal_detail ') 

->select('student_name','marks') 

->where('marks','>',50) 

->get();

Distinct() method in Query Builder

$fetchData = personal_detail::distinct()->select('marks') 

->where('marks', '>', 30)

->get();

Laravel Query Builder WHERE Clause

In where() method we pass three parameter, First Parameter is Table Column Name, Second Column Name contain the operator through which we want to compare (Comparison Operator supported by Database ) and third one is the value.

Suntax for where() method:

where('column-name',' Comparision operator', value)

$fetchData = DB::table('personal_detail ')->where('id','=',4)->first();

We can use multiple where Clause in the same query.

$fetchData = DB::table('personal_detail ')->where('id','>',4)

->where('marks','>',60)

 ->get();

you can also pass the array of condition in where Clause. Below is the example.

$fetchData = DB::table('personal_detail')

->where([['marks', '>', 30], ['marks','<',80]]) 

->orderby('marks', 'DESC')->get();

orWhere() method in Laravel Query Builder

orWhere() method is like or Statement. See the below query to understand the concept.

$fetchData=DB::table('personal_detail')->where('marks','>',60) 

->orWhere('id','>',6)

->get();

$fetchData=DB::table('personal_detail')->where('id','>',4) 

->orWhere(function($query){
$query->where('marks','>',60)
->where('student_name','=','NAVEENKUMAR')
})
->get();

whereBetween() method /  orWhereBetween()

$fetchData=DB::table('personal_detail')->whereBetween('id',[2,8])->get();