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);
}
}
No responses yet