Query Builder Methods In Laravel

Query Builder Laravel

Query Builder

• Laravel DB facade provides select, insert, update, delete and statement methods for running each type of query.
• use Illuminate\Support\Facades\DB;

Query Builder Advantages

Eloquent is perfect for the usual CRUD operations on a single model or simple JOINs when working with a few records, but once you start getting into complex joins and working with hundreds or thousands of records in a transaction, query builder methods are more performant; if anything, raw SQL is the most performant option being that it’s a direct query.

QUERY BUILDER METHODS

• table
• get
• where
• find
• count
• insert
• insertGetId
• update –> Where Method
• delete –> Where Method

Create Table Query

CREATE TABLE employees (
  id int primary key auto_increment,
  name varchar(191) NOT NULL,
  gender varchar(191) NOT NULL,
  age int(11) NOT NULL,
  designation varchar(191)  NOT NULL,
  created_at datetime NOT NULL
);

INSERT INTO `employees` (`id`, `name`, `gender`, `age`, `designation`, `created_at`) VALUES
(1, 'Asghar', 'Male', 23, 'Manager', '2019-02-22 12:45:23'),
(2, 'Nimra', 'Female', 21, 'Operator', '2021-05-12 08:22:11'),
(3, 'Farah', 'Female', 22, 'Incharge', '2015-09-17 09:45:56'),
(4, 'Zain', 'Male', 21, 'P-A', '2019-01-11 10:09:45'),
(5, 'Usman', 'Male', 24, 'Mobile App Developer', '2022-01-22 04:23:12'),
(6, 'Humaira', 'Female', 32, 'Owner', '2022-06-13 12:45:23'),
(12, 'Atif', 'Male', 24, 'Operator', '2020-05-13 11:22:33'),
(13, 'Muskan', 'Female', 24, 'Manager', '2018-01-26 11:56:23'),
(15, 'Adeel', 'Male', 26, 'IT Specialist', '2022-06-14 02:23:12'),
(16, 'Fatima', 'Female', 22, 'IT Incharge', '2021-02-13 01:23:12');

ENV File Source Code

APP_NAME=Laravel
APP_ENV=local
APP_KEY=base64:alFbasUeMezOzpHqlcfjFjEWc1Ir2h+Xp9HE29+oyX0=
APP_DEBUG=true
APP_URL=http://QueryBuilderMethods.test

LOG_CHANNEL=stack
LOG_DEPRECATIONS_CHANNEL=null
LOG_LEVEL=debug

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=batch_1712e
DB_USERNAME=root
DB_PASSWORD=

BROADCAST_DRIVER=log
CACHE_DRIVER=file
FILESYSTEM_DRIVER=local
QUEUE_CONNECTION=sync
SESSION_DRIVER=file
SESSION_LIFETIME=120

MEMCACHED_HOST=127.0.0.1

REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379

MAIL_MAILER=smtp
MAIL_HOST=mailhog
MAIL_PORT=1025
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null
MAIL_FROM_ADDRESS=null
MAIL_FROM_NAME="${APP_NAME}"

AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
AWS_DEFAULT_REGION=us-east-1
AWS_BUCKET=
AWS_USE_PATH_STYLE_ENDPOINT=false

PUSHER_APP_ID=
PUSHER_APP_KEY=
PUSHER_APP_SECRET=
PUSHER_APP_CLUSTER=mt1

MIX_PUSHER_APP_KEY="${PUSHER_APP_KEY}"
MIX_PUSHER_APP_CLUSTER="${PUSHER_APP_CLUSTER}"

HomeController Source Code

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class HomeController extends Controller
{
    public function Index()
    {   

      // $data = DB::table('employees')->get();
        // $data = DB::table('employees')->where('name','Asghar')->get();
        // $data = DB::table('employees')->find(4);
        // $data = DB::table('employees')->count();

// $arr = array(
//                 'name'=>'Fatima',
//                 'gender'=>'Female',
//                 'age'=>22,
//                 'designation'=>'IT Incharge',
//                 'created_at'=>'2021-02-13 01:23:12'
// );

        // $data = DB::table('employees')->insert($arr);

        // $data = DB::table('employees')->insertGetId(
        //     [
        //         'name'=>'Adil',
        //         'gender'=>'Male',
        //         'age'=>29,
        //         'designation'=>'Web Developer',
        //         'created_at'=>'2020-01-22 04:23:12'
        //     ]
        // );   
        
        // $data = DB::table('employees')
        // ->where('id',5)
        // ->update(
        //     [
        //         'name'=>'Usman',
        //         'gender'=>'Male',
        //         'age'=>24,
        //         'designation'=>'Mobile App Developer',
        //         'created_at'=>'2022-01-22 04:23:12'
        //     ]
        // );   
        $data = DB::table('employees')->where('id',17)->delete();
        echo '<pre>';
        print_r($data);
    }    
}

Web.php Routes File Source Code

<?php

use Illuminate\Support\Facades\Route;

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', function () {
    return view('welcome');
});
Route::get('index','App\Http\Controllers\HomeController@Index');

QUERY BUILDER METHODS – WHERE METHODS IN LARAVEL

• where
• orWhere
• whereBetween
• whereNotBetween
• whereIn
• whereNotIn
• whereDate
• whereMonth
• whereDay
• whereYear
• whereTime

HomeController Source Code

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class HomeController extends Controller
{
    public function Index()
    {   

        // $data = DB::table('employees')
        // ->whereDay('created_at','22')
        // ->whereMonth('created_at','01')
        // ->whereYear('created_at','2022')
        // ->get();

        // $data = DB::table('employees')
        // ->whereTime('created_at','11:56:23')
        // ->get();
        // $data = DB::table('employees')
        // ->whereYear('created_at','2022')
        // ->get();

        // $data = DB::table('employees')
        // ->whereDay('created_at','13')
        // ->get();

        // $data = DB::table('employees')
        // ->whereMonth('created_at','06')
        // ->get();

        // $data = DB::table('employees')
        // ->whereDate('created_at','2022-01-22')
        // ->get();

        // $data = DB::table('employees')
        // ->whereNotIn('name',['Asghar','Fatima','Zain'])
        // ->get();
        // $data = DB::table('employees')
        // ->whereIn('name',['Asghar','Fatima','Zain'])
        // ->get();

        // $data = DB::table('employees')
        // ->whereIn('id',[1,3,5])
        // ->get();

        // $data = DB::table('employees')
        // ->whereNotBetween('id',[2,5])
        // ->get();

        // $data = DB::table('employees')
        // ->whereBetween('id',[3,6])
        // ->get();
        // $data = DB::table('employees')
        // ->where('id',2)
        // ->orWhere('name','Fatima')
        // ->orWhere('name','Asghar')
        // ->get();
        echo '<pre>';
        print_r($data);
    }    
}