Laravelでクエリビルダを使う

Laravel 5.7.4で試した.

LaravelにはQuery Builderが備わっています.

SQLインジェクション対策も施されていて, それなりに複雑なクエリも書けるので, O/RマッパーのEloquentだと複雑になりそうな時には便利っぽいです.

試すための事前準備

DBにはPostgreSQLを使いました.

テーブルは以下のようにします.

DEPT(部署)

キー	列名	意味	型
Primary	ID	ID	数値
Unique	DNO	部署コード	文字列
	DNAME	部署名	文字列
	BUDGET	予算	数値
	LAST_UPDATE	最終更新日時	日付

EMP(従業員)

キー	列名	意味	型
Primary	ID	ID	数値
Unique	ENO	従業員コード	文字列
	ENAME	従業員名	文字列
Foreign	DEPT_ID	部署ID	文字列
	SALARY	給与	数値
	LAST_UPDATE	最終更新日時	日付

C.J Dateのデータベース実践講義を元に日付を足しています. また, キー項目をIDに変更しています.

DDL文は以下の通り.

CREATE TABLE dept(
     id INTEGER PRIMARY KEY
    ,dno VARCHAR(20) UNIQUE
    ,dname VARCHAR(30)
    ,budget NUMERIC(13, 3)
);

CREATE TABLE emp (
     id INTEGER PRIMARY KEY
    ,eno VARCHAR(10) UNIQUE
    ,ename VARCHAR(50)
    ,dept_id integer
    ,salary NUMERIC(13)
    ,last_update TIMESTAMP
);

テスト用にデータも入れておく.

INSERT INTO dept VALUES(1, '001', 'sales', 1000000);
INSERT INTO dept VALUES(2, '002', 'develop', 8000000);
INSERT INTO dept VALUES(3, '003', 'legal', 300000);

INSERT INTO emp VALUES(1, '001', 'yamada', 1, 200000, TO_TIMESTAMP('2000/04/01 10:15:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO emp VALUES(2, '002', 'saito', 1, 280000, TO_TIMESTAMP('1996/04/01 10:15:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO emp VALUES(3, '003', 'tanaka', 2, 280000, TO_TIMESTAMP('1996/04/01 10:15:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO emp VALUES(4, '004', 'sonoda', 2, 380000, TO_TIMESTAMP('1990/04/01 10:15:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO emp VALUES(5, '005', 'honda', 3, 300000, TO_TIMESTAMP('2001/04/01 10:15:00', 'YYYY/MM/DD HH24:MI:SS'));

プロジェクトとコントローラを作る

適当な名前でプロジェクトを作ります.

$ composer --create-project --prefer-dist laravel/laravel code

コントローラも適当に作ります.

$ php artisan make:controller Employees

コントローラからクエリビルダを使う

普通, クエリビルダを使う状況はコントローラからでしょう.

コントローラから使う場合のテンプレートはこんな感じです.

<?php                                                                                             1
namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;   #<= 追加

class Employees extends Controller
{
    public function index()
    {
        # empテーブルの値を全て取得してemps変数へ入れる
        # empsはPHPのstdClassオブジェクトのコレクション
        $emps = DB::table('emp')->get();
    
        # ビューへ渡す
        return view('employees.index', ['employees' => $emps]);
    }
}

クエリビルダは結果をPHPのstdClassのインスタンスとして返します.

getは結果をIlluminate\Support\Collectionのインスタンス(配列みたいな感じで扱える)で返すメソッドなので, そのままビューに渡してBladeなんかで扱えます.

<table>
    <tr>
        <th>社員名</th>
    </tr>
@foreach ($employees as $emp)
    <tr>
        <td>{{ $emp->ename }}</td>
    </tr>
@endforeach
</table>

tinkerを使う

クエリビルダを試すためだけに毎回コントローラいじるのは面倒なので, consoleを使います.

RailsのconsoleみたいなREPLはLaravelではtinkerのようです.

ちなみにtinkerは下手な職人の意らしく, artisanの弟子なんでしょうか.洒落がきいてます.

vagrant@homestead:~/code$ php artisan tinker
Psy Shell v0.9.8 (PHP 7.2.5-1+ubuntu18.04.1+deb.sury.org+1 — cli) by Justin Hileman
>>>

試しに上述のコントローラで書いたクエリを実行してみます.

>>> $emps = DB::table('emp')->get();
=> Illuminate\Support\Collection {#2893
     all: [
       {#2891
         +"id": 1,
         +"eno": "001",
         +"ename": "yamada",
         +"dept_id": 1,
         +"salary": "200000",
         +"last_update": "2000-04-01 10:15:00",
       },
       {#2899
         +"id": 2,

いい感じです.

ちなみにtinkerを終了させるには「q」とだけタイプします.

>>> q
Exit:  Goodbye

あれこれ試す

適当に使いそうなやつだけかいつまんで触ってみます.

データを取得する

全部手に入れる

getを使うと指定条件下のデータが全部手に入ります.

>>> $data = DB::table('dept')->get()
=> Illuminate\Support\Collection {#2895
     all: [
       {#2897
         +"id": 1,
         +"dno": "001",
         +"dname": "sales",
         +"budget": "1000000.000",
       },
       {#2888
         +"id": 2,
         +"dno": "002",
         +"dname": "develop",
         +"budget": "8000000.000",
       },
       {#2901
         +"id": 3,
         +"dno": "003",
         +"dname": "legal",
         +"budget": "300000.000",
       },
     ],
   }

1件だけ手に入れる

firstメソッドで指定条件下の最初の1件だけ手に入れます.

>>> $data = DB::table('dept')->first()
=> {#2904
     +"id": 1,
     +"dno": "001",
     +"dname": "sales",
     +"budget": "1000000.000",
   }

特定カラムだけ手に入れる

pluckメソッドで指定カラムだけ手に入れます.

>>> $data = DB::table('dept')->pluck('dno')
=> Illuminate\Support\Collection {#2912
     all: [
       "001",
       "002",
       "003",
     ],
   }

複数カラム指定できないのかな?ちょっと惜しい.

普通のWhere

>>> $data = DB::table('dept')->where('dno', '=', '001')->get()
=> Illuminate\Support\Collection {#2903
     all: [
       {#2909
         +"id": 1,
         +"dno": "001",
         +"dname": "sales",
         +"budget": "1000000.000",
       },
     ],
   }

WhereでOR

>>> $data = DB::table('dept')->where('dno', '=', '001')->orWhere('dno', '=', '002')->get()
=> Illuminate\Support\Collection {#2914
     all: [
       {#2912
         +"id": 1,
         +"dno": "001",
         +"dname": "sales",
         +"budget": "1000000.000",
       },
       {#2901
         +"id": 2,
         +"dno": "002",
         +"dname": "develop",
         +"budget": "8000000.000",
       },
     ],
   }

Whereは他にも強力なのがたくさんあって凄い.

INNER JOIN

普通にjoinできて便利.

>>> $data = DB::table('dept')->join('emp', 'dept.id', '=', 'emp.dept_id')->get()
=> Illuminate\Support\Collection {#2922
     all: [
       {#2900
         +"id": 1,
         +"dno": "001",
         +"dname": "sales",
         +"budget": "1000000.000",
         +"eno": "001",
         +"ename": "yamada",
         +"dept_id": 1,
         +"salary": "200000",
         +"last_update": "2000-04-01 10:15:00",
       },
       [略]

もっと知りたい

以下は更に詳しいので使う時にはリファレンス的に.

Group Byやunionなどの複雑なクエリも使えるのでいい感じ.

write ahead log

ロールフォワード用