Laravel 5.5--Query Builder

Read

Db::table (' users ')->get ();                             Returns the collection containing the object
db::table (' users ')->distinct ()->get ();
Db::table (' users ')->select (' id ', ' Age as User_age ')->get ();
Db::table (' users ')->where (' name ', ' John ')->first ();    Returns the object Db::table ('
users ')->where (' id ', "n")->value (' age ');     Returns the
db::table (' users ')->orderby (' id ')->value (' age ');       Returns the Age
db::table (' users ') with the smallest ID->pluck (' age ');                      Returns the collection containing the field values
db::table (' users ')->pluck (' Age ', ' id ');                Returns the associated collection id = age, up to 2 parameters
db::table (' users ')->count ();                           Returns the number
db::table (' users ')->max (' id ');                         Returns the number or null
db::table (' users ')->where (' class_id ', ' 1 ')->average (' age ');//Returns the average of four decimal places or null

Db::table (' users ')->orderby (' id ')->chunk (function ($users) {  //Take every 100 sets of
    foreach ($users as $user) {
        // ...
        return false;                                           Ready to Exit
    }
);                                                     

$query = db::table (' users ')->select (' name ');
$users = $query->addselect (' Age ')->get ();

$users = db::table (' users ')
                ->select (Db::raw (' count (*) as User_count, Status '))
                ->where (' status ', ' <> ', 1)
                ->groupby (' status ')
                ->get ();

Selectraw can pass a parameter binding

db::table (' orders ')
                ->selectraw (' price *? as Price_with_tax ', [1.0825])
                - >get ();

$orders = db::table (' orders ')
                ->whereraw (' Price > IF ' (state = ' TX ', ', ') ', [+]])
                ->get ();

$orders = db::table (' orders ')
                ->select (' Department ', Db::raw (' SUM (price) as Total_sales '))
                ->groupby ( ' Department ')
                ->havingraw (' SUM (price) > 2500 ')
                ->get ();

$orders = db::table (' orders ')
                ->orderbyraw (' updated_at-created_at DESC ')      //-only for timestamp type        
                Get ();

# INNER JOIN $users = db::table (' users ')->join (' Contacts ', ' users.id ', ' = ', ' contacts.user_id ') ->join (' orders ', ' users.id ', ' = ', ' orders.user_id ')->select (' users.* ', ' contacts.phone ', ' orders.pric

E ')->get (); # LEFT Join $users = db::table (' users ')->leftjoin (' posts ', ' users.id ', ' = ', ' posts.user_id ')-

>get ();

# Cross Join $users = db::table (' sizes ')->crossjoin (' Colours ')->get (); # Advanced Join Db::table (' users ')->join (' Contacts ', function ($join) {$join->on (' users.id ', ' = ', ' C
        ontacts.user_id ')->oron (...);

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

})->get ();

# union $first = db::table (' users ')->wherenull (' first_name '); $Users = db::table (' users ')->wherenull (' last_name ')->union ($first)->get (); 

Wherenull (' last_name ') where (' votes ', ' = ', ') where (' votes ', ') where (' votes ', ' >= ', ') where (' votes ', ' < > ', ' Where (' name ', ' like ', ' t% ') where ([[' status ', ' = ', ' 1 '], [' subscribed ', ' <> ', ' 1 '],]) where (' V
OTEs ', ' > ',->orwhere (' name ', ' John ') wherebetween (' votes ', [1]) Wherenotbetween (' votes ', [1, 100]) Wherein (' ID ', [1, 2, 3]) Wherenotin (' id ', [1, 2, 3]) wherenotnull (' Updated_at ') wheredate (' Created_at ', ' 2016-12-31 ') whe Remonth (' Created_at ', ' Whereday ') whereyear (' Created_at ', ') ' Wherecolumn (' first_name ', ' Last_Name ')//Judge two fields equal Wherecolumn (' updated_at ', ' > ', ' Created_at ') wherecolumn ([[' first_name ', ' = ', ' Las T_name '], [' Updated_at ', ' > ', ' Created_at ']]) where (' name ', ' = ', ' John ')->orwhere (function ($query) {$que
Ry->where (' votes ', ' > ', '->where ') (' title ', ' <> ', ' Admin ');
         }) whereexists (function ($query) {$query->select (Db::raw (1)) ->from (' orders ')->whereraw (' orders.user_id = Users.id ');
                })//Where exists (select 1 from orders where orders.user_id = users.id) # JSON $users = db::table (' users ')

->where (' options->language ', ' en ')->get (); $users = db::table (' users ')->where (' preferences->dining->meal ', ' salad ')->ge T ();

(' name ', ' desc ')
latest ()                //= (' Created_at ', ' desc ') inrandomorder (
)
groupBy (' account_id ')->having (' account_id ', ' > ', max)
Skip (+)->take (5)        //= offset (Ten)->limit (5)

$role has a value to execute closures
$role = $request->input (' role ');

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



$role A value executes the first closure, otherwise a second closure
$sortBy = null;

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

Write

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

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

Change

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

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

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

Db::table (' users ')->increment (' votes ', 1, [' name ' = ' John ')];

Delete

db::table (' users ')->delete ();
Db::table (' users ')->where (' votes ', ' > ', +)->delete (); Db::table (' users ')->truncate ();