Query builder for beginners in Laravel project

Laravel’s database query builder provides a convenient, fluent interface for creating and running database queries. It can be used to perform most database operations in your application and works perfectly with all of Laravel’s supported database systems.

The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean or sanitize strings passed to the query builder as query bindings.

Running Database Queries

Retrieving All Rows From A Table

You may use the table the method provided by the DB facade to begin a query. The table the method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally retrieve the results of the query using the get method:

<?php
 
namespace App\Http\Controllers;
 
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;
 
class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     */
    public function index(): View
    {
        $users = DB::table('users')->get();
 
        return view('user.index', ['users' => $users]);
    }
}

The get method returns an Illuminate\Support\Collection instance containing the results of the query where each result is an instance of the PHP stdClass object. You may access each column’s value by accessing the column as a property of the object:

use Illuminate\Support\Facades\DB;
 
$users = DB::table('users')->get();
 
foreach ($users as $user) {
    echo $user->name;
}

Retrieving A Single Row / Column From A Table

If you just need to retrieve a single row from a database table, you may use the DB facade’s first method. This method will return a single stdClass object:

$user = DB::table('users')->where('name', 'John')->first();
 
return $user->email;

If you don’t need an entire row, you may extract a single value from a record using the value method. This method will return the value of the column directly:

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

To retrieve a single row by its id column value, use the find method:

$user = DB::table('users')->find(3);

Retrieving A List Of Column Values

If you would like to retrieve an Illuminate\Support\Collection instance containing the values of a single column, you may use the pluck method. In this example, we’ll retrieve a collection of user titles:

use Illuminate\Support\Facades\DB;
 
$titles = DB::table('users')->pluck('title');
 
foreach ($titles as $title) {
    echo $title;
}

You may specify the column that the resulting collection should use as its keys by providing a second argument to the pluck method:

$titles = DB::table('users')->pluck('title', 'name');
 
foreach ($titles as $name => $title) {
    echo $title;
}

Chunking Results

If you need to work with thousands of database records, consider using the chunk method provided by the DB facade. This method retrieves a small chunk of results at a time and feeds each chunk into a closure for processing. For example, let’s retrieve the entire users table in chunks of 100 records at a time:

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
 
DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
    foreach ($users as $user) {
        // ...
    }
});

You may stop further chunks from being processed by returning false from the closure:

DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
    // Process the records...
 
    return false;
});

If you are updating database records while chunking results, your chunk results could change in unexpected ways. If you plan to update the retrieved records while chunking, it is always best to use the chunkById method instead. This method will automatically paginate the results based on the record’s primary key:

DB::table('users')->where('active', false)
    ->chunkById(100, function (Collection $users) {
        foreach ($users as $user) {
            DB::table('users')
                ->where('id', $user->id)
                ->update(['active' => true]);
        }
    });

Streaming Results Lazily

The lazy the method works similarly to the chunk method in the sense that it executes the query in chunks. However, instead of passing each chunk into a callback, the lazy() method returns a LazyCollection, which lets you interact with the results as a single stream:

use Illuminate\Support\Facades\DB;
 
DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
    // ...
});

Once again, if you plan to update the retrieved records while iterating over them, it is best to use the lazyById or lazyByIdDesc methods instead. These methods will automatically paginate the results based on the record’s primary key:

DB::table('users')->where('active', false)
    ->lazyById()->each(function (object $user) {
        DB::table('users')
            ->where('id', $user->id)
            ->update(['active' => true]);
    });

Aggregates

The query builder also provides a variety of methods for retrieving aggregate values like count, max, min, avg, and sum. You may call any of these methods after constructing your query:

use Illuminate\Support\Facades\DB;
 
$users = DB::table('users')->count();
 
$price = DB::table('orders')->max('price');

Of course, you may combine these methods with other clauses to fine-tune how your aggregate value is calculated:

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

Determining If Records Exist

Instead of using the count method to determine if any records exist that match your query’s constraints, you may use the exists and doesntExist methods:

if (DB::table('orders')->where('finalized', 1)->exists()) {
    // ...
}
 
if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
    // ...
}