人気のフレームワークLaravelのQuery Builderをまとめてみました

すべての列を取得

`
$users = DB::table('users')->get();
foreach ($users as $user)
{
  var_dump($user->name);
}
`

列を指定して取得

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

列を指定/行を指定

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

カラムリストを取得

`
$roles = DB::table('roles')->lists('title');
`

カラムリストを取得2

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

SELECT節を指定する

`
$users = DB::table('users')->select('name', 'email')->get();
$users = DB::table('users')->distinct()->get();
$users = DB::table('users')->select('name as user_name')->get();
`

SELECT節を指定する

`
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
`

WHERE文を使用する

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

OR文

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

WHERE BETWEEN

`
$users = DB::table('users')
         ->whereBetween('votes', array(1, 100))->get();
`

WHERE NOT BETWEEN

`
$users = DB::table('users')
                    ->whereNotBetween('votes', array(1, 100))->get();
`

WHERE IN

`
$users = DB::table('users')
            ->whereIn('id', array(1, 2, 3))->get();

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

WHERE NULL

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

ORDER BY

`
$users = DB::table('users')
                    ->orderBy('name', 'desc')
                    ->groupBy('count')
                    ->having('count', '>', 100)
                    ->get();
`

集計

`
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')->min('price');
$price = DB::table('orders')->avg('price');
$total = DB::table('users')->sum('votes');
`

自動 incrementとdecrement

自動でインクリメントされる便利な機能です

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