Dec 10

Laravel Query Builder (Laravel 5.3)

$users = DB::table('users')->get(); //get all users //returns collections of objects
foreach ($users as $user) {
    echo $user->name;
}

--
 
$user = DB::table('users')->where('name', 'John')->first(); //first row as an object
$email = DB::table('users')->where('name', 'John')->value('email'); //return the values of email column
$titles = DB::table('roles')->pluck('title'); //array of values
---
$roles = DB::table('roles')->pluck('title', 'name'); //custom key
foreach ($roles as $name => $title) {
    echo $title;
}
--


work chunk by chunk
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {
        //
    }
});

stop further chunk processing using return false
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    // Process the records...

    return false;
});
---

Aggregates
$users = DB::table('users')->count();

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

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

---

Select some columns
$users = DB::table('users')->select('name', 'email as user_email')->get();
---

Distinct
$users = DB::table('users')->distinct()->get();
---
add columns to existing query
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
---

RAW SQL

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

---


Joins

$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();

---



left join

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


Cross Joins

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

---

Advanced Join clauses
DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
        })
        ->get();




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

---

unions

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

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

---


Simple where

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

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

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

Pass an array of conditions

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


---


or statements

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

---


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

$users = DB::table('users')
                    ->whereNotIn('id', [1, 2, 3])
                    ->get();

$users = DB::table('users')
                    ->whereNull('updated_at')
                    ->get();

$users = DB::table('users')
                    ->whereNotNull('updated_at')
                    ->get();
$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();

column equality
$users = DB::table('users')
                ->whereColumn('first_name', 'last_name')
                ->get();
$users = DB::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

$users = DB::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at']
                ])->get();

---

Parameter Grouping
DB::table('users')
            ->where('name', '=', 'John')
            ->orWhere(function ($query) {
                $query->where('votes', '>', 100)
                      ->where('title', '<>', 'Admin');
            })
            ->get();


Exists test
DB::table('users')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('orders')
                      ->whereRaw('orders.user_id = users.id');
            })
            ->get();
--


Query on JSON Column Types

$users = DB::table('users')
                ->where('options->language', 'en')
                ->get();

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


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

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


RandomOrder

$randomUser = DB::table('users')
                ->inRandomOrder()
                ->first();

--


Group by --  having
$users = DB::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();

$users = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > 2500')
                ->get();

skip/take
$users = DB::table('users')->skip(10)->take(5)->get();

$users = DB::table('users')
                ->offset(10)
                ->limit(5)
                ->get();

----

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

$users = DB::table('users')
                ->when($role, function ($query) use ($role) {
                    return $query->where('role_id', $role);
                })
                ->get();


Insert
$sortBy = null;

$users = DB::table('users')
                ->when($sortBy, function ($query) use ($sortBy) {
                    return $query->orderBy($sortBy);
                }, function ($query) {
                    return $query->orderBy('name');
                })
                ->get();


inserts

DB::table('users')->insert(
    ['email' => 'john@example.com', 'votes' => 0]
);

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]
);



updates
DB::table('users')
            ->where('id', 1)
            ->update(['votes' => 1]);



update JSON columns
DB::table('users')
            ->where('id', 1)
            ->update(['options->enabled' => true]);


increment - decrement
DB::table('users')->increment('votes');

DB::table('users')->increment('votes', 5);

DB::table('users')->decrement('votes');

DB::table('users')->decrement('votes', 5);


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

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


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


pessimistic locking
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();



 


 


















 
 





Skip to toolbar