Laravel Advance Database Query Builder

Today, We want to share with you Laravel Advance Database Query Builder.In this post we will show you Database: Query Builder – Laravel, hear for Advanced query using Eloquent we will give you demo and example for implement.In this post, we will learn about Laravel 5.6 Database: Query Builder with an example.

Laravel Advance Database Query Builder

There are the Following The simple About Laravel Advance Database Query Builder Full Information With Example and source code.

As I will cover this Post with live Working example to develop php – Laravel Delete Query Builder, so the laravel query builder for this example is following below.

Also Read This ๐Ÿ‘‰   MySQL full outer join โ€“ BEST 5 WAYS TO โ€œmysql full outer joinโ€

Getting All Rows From A Table using Laravel 5.6

public function index()
{
    $employees = DB::table('employees')->get();
    return view('employee.index', ['employees' => $employees]);
}

Getting A Single Row or Column From A Table

$employee = DB::table('employees')->where('name', 'Jaydeep')->first();
echo $employee->name;

or

$email = DB::table('employees')->where('name', 'Jaydeep')->value('email');

Getting A List Of Column Values using Laravel 5.6 pluck

$members = DB::table('roles')->pluck('title');

foreach ($members as $title) {
    echo $title;
}

$roles = DB::table('roles')->pluck('title', 'name');

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

Laravel 5.6 Aggregates

$employees = DB::table('employees')->count();
$rate = DB::table('memberships')->max('rate');

$rate = DB::table('memberships')
->where('finalized', 1)
->avg('rate');

Laravel 5.6 Determining If Records Exist

return DB::table('memberships')->where('finalized', 1)->exists();
return DB::table('memberships')->where('finalized', 1)->doesntExist();

Laravel 5.6 Specifying A Select Clause

$employees = DB::table('employees')->select('name', 'email as employee_email')->get();
$employees = DB::table('employees')->distinct()->get();

$query = DB::table('employees')->select('name');
$employees = $query->addSelect('age')->get();

Laravel 5.6 Raw Expressions

$employees = DB::table('employees')
->select(DB::raw('count(*) as employee_count, type_of_status'))
->where('type_of_status', '<>', 1)
->groupBy('type_of_status')
->get();

Laravel 5.6 selectRaw

$memberships = DB::table('memberships')
->selectRaw('rate * ? as rate_with_tax', [1.0825])
->get();

Laravel 5.6 whereRaw / orWhereRaw

$memberships = DB::table('memberships')
->whereRaw('rate > IF(state = "TX", ?, 100)', [200])
->get();

Laravel 5.6 havingRaw / orHavingRaw

$memberships = DB::table('memberships')
->select('department', DB::raw('SUM(rate) as total_sales'))
->groupBy('department')
->havingRaw('SUM(rate) > ?', [2500])
->get();

Laravel 5.6 orderByRaw

$memberships = DB::table('memberships')
->orderByRaw('updated_at - created_at DESC')
->get();

Laravel 5.6 Inner Join Clause

$employees = DB::table('employees')
->join('contacts', 'employees.id', '=', 'contacts.employee_id')
->join('memberships', 'employees.id', '=', 'memberships.employee_id')
->select('employees.*', 'contacts.phone', 'memberships.rate')
->get();

Left Join Clause using Laravel 5.6

$employees = DB::table('employees')
->leftJoin('posts', 'employees.id', '=', 'posts.employee_id')
->get();

<h3>Cross Join Clause</h3>

$employees = DB::table('sizes')
->crossJoin('colours')
->get();

Laravel 5.6 Advanced Join Clauses

DB::table('employees')
->join('contacts', function ($join) {
	$join->on('employees.id', '=', 'contacts.employee_id')->orOn(...);
})
->get();

DB::table('employees')
->join('contacts', function ($join) {
	$join->on('employees.id', '=', 'contacts.employee_id')
     ->where('contacts.employee_id', '>', 5);
})
->get();

Laravel 5.6 Sub-Query Joins

$latestPosts = DB::table('posts')
->select('employee_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)
->groupBy('employee_id');

$employees = DB::table('employees')
->joinSub($latestPosts, 'latest_posts', function($join) {
	$join->on('employees.id', '=', 'latest_posts.employee_id');
})->get();

Laravel 5.6 Unions

$first = DB::table('employees')
->whereNull('first_name');

$employees = DB::table('employees')
->whereNull('last_name')
->union($first)
->get();

Laravel 5.6 Where Clauses

$employees = DB::table('employees')->where('total_likes', '=', 100)->get();
$employees = DB::table('employees')->where('total_likes', 100)->get();

$employees = DB::table('employees')
->where('total_likes', '>=', 100)
->get();

$employees = DB::table('employees')
->where('total_likes', '<>', 100)
->get();

$employees = DB::table('employees')
->where('name', 'like', 'T%')
->get();

$employees = DB::table('employees')->where([
    ['type_of_status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

Laravel 5.6 Or Statements

$employees = DB::table('employees')
->where('total_likes', '>', 100)
->orWhere('name', 'Jaydeep')
->get();

Angular 6 CRUD Operations Application Tutorials

Read :

Also Read This ๐Ÿ‘‰   PHP AJAX Live Search Box Autocomplete Using MySQL Database

Summary

You can also read about AngularJS, ASP.NET, VueJs, PHP.

I hope you get an idea about Laravel Advance Database Query Builder.
I would like to have feedback on my Pakainfo.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, donโ€™t forget to share.