[ Laravel 5.2 ドキュメント ] データベース -- クエリビルダー

1. はじめに

データベース クエリ ビルダーは、データベース クエリを作成および実行するための便利でスムーズなインターフェイスを提供します。クエリ ビルダーを使用すると、アプリケーションでほとんどのデータベース操作を実行でき、サポートされているすべてのデータベース システムで動作します。

注: Laravel クエリビルダーは PDO パラメーター バインディングを使用して SQL インジェクション攻撃を回避し、バインディングに渡される文字列をフィルターする必要がなくなります。

2. 結果セットを取得します

テーブルからすべての行を取得します

クエリを実行する前に、DB ファサードのテーブル メソッドを使用しますこのメソッドは、指定されたテーブルのクエリ ビルダーを返します。これにより、クエリに対してさらに多くの制約を連鎖させ、最終的にクエリ結果を返すことができます。この例では、get メソッドを使用してテーブル内のすべてのレコードを取得します。

<?phpnamespace App\Http\Controllers;use DB;use App\Http\Controllers\Controller;class UserController extends Controller{    /**     * 显示用户列表     *     * @return Response     */    public function index()    {        $users = DB::table('users')->get();        return view('user.index', ['users' => $users]);    }}

ネイティブ クエリと同様に、get メソッドは結果セットの配列を返します。各結果は次の StdClass インスタンスです。 PHP オブジェクト。オブジェクトのプロパティと同じように列の値にアクセスできます。

foreach ($users as $user) {    echo $user->name;}

テーブルから行/列を取得します

単にデータの行を取得したい場合テーブルの場合、最初のメソッドを使用できます。このメソッドは単一の StdClass オブジェクトを返します。

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

完全な行が必要ない場合は、value メソッドを使用して、テーブルから単一の値を取得できます。 result、指定された列の値を直接返します:

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

テーブルからチャンク化された結果セットを取得します

数千または数百のデータベース レコードを処理する必要がある場合は、次のことを検討できます。チャンク メソッドを使用します。このメソッドは、タイム ブロックで結果セットの小さな部分を取得し、その後、処理する各小さなデータをクロージャに埋め込みます。このメソッドは、大量のデータベース レコードを処理するアーティザン コマンドを作成する場合に非常に便利です。 。たとえば、users テーブルのデータ全体を、一度に 100 レコードを処理する小さなチャンクに処理できます:

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

クロージャ関数から false を返すことで、チャンクの実行を中止できます:

DB::table('users')->chunk(100, function($users) {    // 处理结果集...    return false;});

データ列値のリストを取得します

単一の列値を含む配列を取得したい場合は、lists メソッドを使用できます。この場合、すべてのタイトルの配列を取得します。 :

$titles = DB::table('roles')->lists('title');foreach ($titles as $title) {    echo $title;}

返された配列の列値にさらにカスタム キーを指定することもできます (カスタム キーはテーブル内の他のフィールドの列名である必要があります。そうでない場合はエラーが報告されます)。

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

集計関数

キュー ビルダーには、count、max、min、avg、sum などの多数の集計メソッドも用意されており、クエリの作成後に呼び出すことができます。

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

もちろん、他のクエリ句と集計関数を組み合わせてクエリを構築することもできます。

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

3. クエリ (選択)

クエリ句を指定します

もちろん、常にデータ テーブルのすべての列を取得する必要はありません。select メソッドを使用して、クエリにカスタムの select 句を指定できます。 🎜>

Different メソッドを使用すると、クエリが重複した結果セットを返さないように強制できます:

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

既にクエリ ビルダー インスタンスがあり、既存の select 句にクエリ列を追加したい場合

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

ネイティブ式

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

場合によっては、これらの式は文字列の形式でクエリに挿入されます。 SQL インジェクションを避けるように特に注意してください。ネイティブ式を作成するには、DB::raw メソッドを使用します。

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

4. 結合

内部結合 (等価結合)

クエリ ビルダーは、基本的な SQL の「内部結合」を作成するためにも使用できます。結合メソッドに渡される最初のパラメーターは、結合する必要があるテーブル名です。残りの他のパラメーターは、結合に指定された列制約です。もちろん、ご覧のとおり、単一のクエリで複数のテーブルを結合できます。

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

「内部結合」の代わりに「左結合」を実行したい場合は、leftJoin メソッドを使用できます。このメソッドは join メソッドと同じ方法で使用されます。

高度な join ステートメント

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

メソッドとしてクロージャを join メソッドに渡して、より高度な join 句を指定することもできます。 2 番目のパラメータとして、クロージャは結合句の制約を指定できる JoinClause オブジェクトを返します。

結合で "where" スタイル句を使用したい場合は、where と orWhere を使用できます。クエリ内のメソッド。これらのメソッドは、列と列ではなく、列と値を比較します。

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

5. Union

クエリ ビルダーには、「union」へのショートカットも用意されています。たとえば、2 つのクエリを作成し、次に Union メソッドを使用して 2 番目のクエリと結合したい場合:

UnionAll メソッドも有効で、使用方法は同じです。組合として。

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

6、Where子句

简单where子句

使用查询构建器上的 where方法可以添加 where子句到查询中，调用 where最基本的方法需要三个参数，第一个参数是列名，第二个参数是一个数据库系统支持的任意操作符，第三个参数是该列要比较的值。

例如，下面是一个验证“votes”列的值是否等于100的查询：

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

为了方便，如果你只是简单比较列值和给定数值是否相等，可以将数值直接作为 where方法的第二个参数：

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

当然，你可以使用其它操作符来编写 where子句：

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

or

你可以通过方法链将多个 where约束链接到一起，也可以添加 or子句到查询， orWhere方法和 where方法接收参数一样：

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

更多Where子句

whereBetween

whereBetween方法验证列值是否在给定值之间：

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

whereNotBetween

whereNotBetween方法验证列值不在给定值之间：

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

whereIn/whereNotIn

whereIn方法验证给定列的值是否在给定数组中：

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

whereNotIn方法验证给定列的值不在给定数组中：

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

whereNull/whereNotNull

whereNull方法验证给定列的值为NULL：

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

whereNotNull方法验证给定列的值不是NULL：

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

高级Where子句

参数 分组

有时候你需要创建更加高级的 where子句比如“where exists”或者嵌套的参数分组。Laravel查询构建器也可以处理这些。作为开始，让我们看一个在括号中进行分组约束的例子：

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

正如你所看到的，传递闭包到 orWhere方法构造查询构建器来开始一个约束分组，，该闭包将会获取一个用于设置括号中包含的约束的查询构建器实例。上述语句等价于下面的SQL：

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

exists语句

whereExists方法允许你编写 where existSQL子句， whereExists方法接收一个闭包参数，该闭包获取一个查询构建器实例从而允许你定义放置在“exists”子句中的查询：

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

上述查询等价于下面的SQL语句：

select * from userswhere exists (    select 1 from orders where orders.user_id = users.id)

7、排序、分组、限定

orderBy

orderBy方法允许你通过给定列对结果集进行排序， orderBy的第一个参数应该是你希望排序的列，第二个参数控制着排序的方向——asc或desc：

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

groupBy / having / havingRaw

groupBy和 having方法用于对结果集进行分组， having方法和 where方法的用法类似：

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

havingRaw方法可以用于设置原生字符串作为 having子句的值，例如，我们要找到所有售价大于 $2500的部分：

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

skip / take

想要限定查询返回的结果集的数目，或者在查询中跳过给定数目的结果，可以使用 skip和 take方法：

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

8、插入（Insert）

查询构建器还提供了 insert方法来插入记录到数据表。 insert方法接收数组形式的列名和值进行插入操作：

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

如果数据表有自增ID，使用 insertGetId方法来插入记录将会返回ID值：

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

注意：当使用 PostgresSQL时 insertGetId方法默认自增列被命名为 id，如果你想要从其他“序列”获取ID，可以将序列名作为第二个参数传递到 insertGetId方法。

9、更新（Update）

当然，除了插入记录到数据库，查询构建器还可以通过使用 update方法更新已有记录。 update方法和 insert方法一样，接收列和值的键值对数组包含要更新的列，你可以通过 where子句来对 update查询进行约束：

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

增加/减少

查询构建器还提供了方便增减给定列名数值的方法。相较于编写 update语句，这是一条捷径，提供了更好的体验和测试接口。

这两个方法都至少接收一个参数：需要修改的列。第二个参数是可选的，用于控制列值增加/减少的数目。

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

10、删除（Delete）

当然，查询构建器还可以通过 delete方法从表中删除记录：

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

在调用 delete方法之前可以通过添加 where子句对 delete语句进行约束：

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

如果你希望清除整张表，也就是删除所有列并将自增ID置为0，可以使用 truncate方法：

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

11、悲观锁

查询构建器还包含一些方法帮助你在select语句中实现“悲观锁”。可以在查询中使用 sharedLock方法从而在运行语句时带一把”共享锁“。共享锁可以避免被选择的行被修改直到事务提交：

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

此外你还可以使用 lockForUpdate方法。“for update”锁避免选择行被其它共享锁修改或删除：

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