Database: Query Builder

Database: Query Builder

• Introduction
• Retrieving Results
  • Chunking Results
  • Aggregates
• Selects
• Raw Expressions
• Joins
• Unions
• Where Clauses
  • Parameter Grouping
  • Where Exists Clauses
  • JSON Where Clauses
• Ordering, Grouping, Limit, & Offset
• Conditional Clauses
• Inserts
• Updates
  • Updating JSON Columns
  • Increment & Decrement
• Deletes
• Pessimistic Locking

Introduction

Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works on all 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 strings being passed as bindings.

Retrieving Results

Retrieving All Rows From A Table

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

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::table('users')->get();

        return view('user.index', ['users' => $users]);
    }
}

The get method returns an Illuminate\Support\Collection containing the results 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:

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 the database table, you may use the first method. This method will return a single StdClass object:

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

echo $user->name;

If you don't even 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');

Retrieving A List Of Column Values

If you would like to retrieve an array containing the values of a single column, you may use the pluck method. In this example, we'll retrieve an array of role titles:

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

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

You may also specify a custom key column for the returned array:

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

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