Laravel Query Builder – All Possible Queries explained in 1 article

Get all Data from a table

<?php 
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;

class BlogController extends controller {
  public function index() {
  $blog = DB::table('blogs')->get();
  return $blog;
}
}

Get Single Row

if you want to retrieve only data, you should use first method. This method will return a single stdClass object.

$blog = DB::table('blogs')->where('id', 3)->first();
// or
$slug = 'my-first-blog-post';
$blog = DB::table('blogs')->where('slug', $slug)->first();

To retrieve a single row by id column use the find method

$blog = DB::table('blogs')->find(4);

Retrieving a single value

Use pluck method to retrieve a single value. Use Select to get multiple values.

$blog = DB::table('blogs')->pluck('title');

Chunk Results – Important & very useful

If you are looking to fetch thousands of database records at a time, use chunk method. Chunk method will retrieve a small chunk of results at a time and feeds each chunk into a closure for processing.

$blog = DB::table('blogs')->orderBy('id')->chunk(100, function($blogs) {
  foreach($blogs as $blog) {
    //
  }

})

Aggregates Query

For retrieving values like count, max, min, avg and sum.

$blog = DB::table('blogs')->count();

$products = DB::table('products')->max('price');

$orderTotal = DB::table('orders')->where('user_id', 1)->sum('orderValue');

$orderAverage = DB::table('orders')->where('user_id', 1)->avg('orderValue');

Select Statements in Laravel Query Builder

$blog = DB::table('blogs')->select('title', 'slug', 'description')->get();

$blogByAuthor = DB::table('blogs')->Select('title', 'slug', 'description')->where('user_id', 1)->get();

Join Query

$categories = DB::table('categories')
->join('users', 'users.id', '=', 'categories.user_id')
->join('blogs', 'blog.category_id','=', 'categories.id')
->select('category.title', 'blogs.title', 'blogs.slug', 'users.name')
->get();

Where Clause

$users = DB::table('users')
                ->where('votes', '=', 100)
                ->where('age', '>', 35)
                ->get();

orWhere

$users = DB::table('users')
                    ->where('votes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

JSON Where Clauses

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

$users = DB::table('users')
                ->whereJsonContains('options->languages', ['en', 'de'])
                ->get();

whereBetween

whereIn / whereNotIn / orWhereIn / orWhereNotIn

$users = DB::table('users')
           ->whereBetween('votes', [1, 100])
           ->get();

Date / Month / Day

$users = DB::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();

$users = DB::table('users')
                ->whereMonth('created_at', '12')
                ->get();

$users = DB::table('users')
                ->whereDay('created_at', '31')
                ->get();

$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->get();

OrderBy

$users = DB::table('users')
                ->orderBy('name', 'desc')
                ->get();

Latest & Oldest

$user = DB::table('users')
                ->latest()
                ->first();