laravel Query Builder

Query BuilderのSQL確認方法
$builder->toSql()
$builder->getBindings()

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.

extract a single value

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

Retrieving A List Of Column Values

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

aggregate methods

->count()/max('price')/min('price')/avg('price')/sum('price')

->exists()/doesntExist()

Select Clause

$users = DB::table('users')->select('name', 'email as user_email')->get();
->distinct()
add a column to its existing select clause
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

Raw Expressions

$users = DB::table('users')
                     ->select(DB::raw('count(*) as user_count, status'))
                     ->where('status', '<>', 1)
                     ->groupBy('status')
                     ->get();

selectRaw/whereRaw / orWhereRaw/havingRaw / orHavingRaw/orderByRaw

Where Clauses

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

->where('votes', '>', 100)
->orWhere('name', 'John')

whereNotXxxx/orWhereXxxx
->whereBetween('votes', [1, 100])

->whereIn('id', [1, 2, 3])

->whereNull('updated_at')

->whereDate('created_at', '2016-12-31')
->whereMonth('created_at', '12')
->whereDay('created_at', '31')
->whereYear('created_at', '2016')
->whereTime('created_at', '=', '11:20:45')

The whereColumn method may be used to verify that two columns are equal:
->whereColumn('first_name', 'last_name')
You may also pass a comparison operator to the method:
->whereColumn('updated_at', '>', 'created_at')

Parameter Grouping

DB::table('users')
            ->where('name', '=', 'John')
            ->where(function ($query) {
                $query->where('votes', '>', 100)
                      ->orWhere('title', '=', 'Admin');
            })
            ->get();
The example above will produce the following SQL:
select * from users where name = 'John' and (votes > 100 or title = 'Admin')

order by

->orderBy('name', 'desc')
->latest ()/ oldest()
By default, result will be ordered by the created_at column.

groupBy/having/skip/take/offset/limit

Conditional Clauses

The when method only executes the given Closure when the first parameter is true. If the first parameter is false, the Closure will not be executed.

$role = $request->input('role');

$users = DB::table('users')

                ->when($role, function ($query, $role) {

                    return $query->where('role_id', $role);

                })

                ->get();

Insert

DB::table('users')->insert([

    ['email' => 'taylor@example.com', 'votes' => 0],

    ['email' => 'dayle@example.com', 'votes' => 0]

]);

$id = DB::table('users')->insertGetId(

    ['email' => 'john@example.com', 'votes' => 0]

);

Update

DB::table('users')

            ->where('id', 1)

            ->update(['votes' => 1]);

The updateOrInsert method will first attempt to locate a matching database record using the first argument's column and value pairs. If the record exists, it will be updated with the values in the second argument. If the record can not be found, a new record will be inserted with the merged attributes of both arguments:

DB::table('users')

    ->updateOrInsert(

        ['email' => 'john@example.com', 'name' => 'John'],

        ['votes' => '2']

    );

Delete

DB::table('users')->delete();

DB::table('users')->where('votes', '>', 100)->delete();

DB::table('users')->truncate();

Join

$users = DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();