はじめに
Laravelのクエリビルダを深掘りします
✅参考
セットアップ
プロジェクト作成
$ composer create-project "laravel/laravel=6.8" queryapp
DB作成
$ mysql -u root mysql> create database testdb; mysql> use testdb;
depts(部署)テーブル作成
create table depts( dept_id int primary key, dept_name varchar(32) ); insert into depts(dept_id,dept_name) values(1,'営業部'); insert into depts(dept_id,dept_name) values(2,'経理部'); insert into depts(dept_id,dept_name) values(3,'技術部'); insert into depts(dept_id,dept_name) values(4,'法務部');
確認
show columns from depts; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | dept_id | int(11) | NO | PRI | NULL | | | dept_name | varchar(32) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 2 rows in set (0.02 sec)
employees(従業員)テーブル作成
create table employees( id int primary key auto_increment, dept_id int, name varchar(32) ); insert into employees(dept_id,user_name) values(1,'田中'); insert into employees(dept_id,user_name) values(2,'玉木'); insert into employees(dept_id,user_name) values(3,'鈴木'); insert into employees(dept_id,user_name) values(3,'山本'); insert into employees(dept_id,user_name) values(2,'斉藤'); insert into employees(dept_id,user_name) values(1,'佐藤'); insert into employees(dept_id,user_name) values(1,'小澤'); insert into employees(dept_id,user_name) values(2,'関野'); insert into employees(dept_id,user_name) values(0,'中村');
テーブルの確認
mysql> select * from depts; +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | 1 | 営業部 | | 2 | 経理部 | | 3 | 技術部 | | 4 | 法務部 | +---------+-----------+ 4 rows in set (0.00 sec) mysql> select * from employees; +----+---------+------+ | id | dept_id | name | +----+---------+------+ | 1 | 1 | 田中 | | 2 | 2 | 玉木 | | 3 | 3 | 鈴木 | | 4 | 3 | 山本 | | 5 | 2 | 斉藤 | | 6 | 1 | 佐藤 | | 7 | 1 | 小澤 | | 8 | 2 | 関野 | | 9 | 0 | 中村 | +----+---------+------+ 9 rows in set (0.00 sec)
SQLで基本的なJOIN
等価結合(NULLは表示されていない)
mysql> select * from depts,employees where depts.dept_id = employees.dept_id; +---------+-----------+----+---------+------+ | dept_id | dept_name | id | dept_id | name | +---------+-----------+----+---------+------+ | 1 | 営業部 | 1 | 1 | 田中 | | 1 | 営業部 | 6 | 1 | 佐藤 | | 1 | 営業部 | 7 | 1 | 小澤 | | 2 | 経理部 | 2 | 2 | 玉木 | | 2 | 経理部 | 5 | 2 | 斉藤 | | 2 | 経理部 | 8 | 2 | 関野 | | 3 | 技術部 | 3 | 3 | 鈴木 | | 3 | 技術部 | 4 | 3 | 山本 | +---------+-----------+----+---------+------+ 8 rows in set (0.00 sec)
👆ジョインばかり考えていましたが、こういう書き方もあるんですね~~
LEFT JOIN(部署をすべて表示する※無所属の中村さんは表示されず)
mysql> SELECT * FROM depts LEFT JOIN employees ON depts.dept_id = employees.dept_id;
+---------+-----------+------+---------+------+
| dept_id | dept_name | id | dept_id | name |
+---------+-----------+------+---------+------+
| 1 | 営業部 | 1 | 1 | 田中 |
| 2 | 経理部 | 2 | 2 | 玉木 |
| 3 | 技術部 | 3 | 3 | 鈴木 |
| 3 | 技術部 | 4 | 3 | 山本 |
| 2 | 経理部 | 5 | 2 | 斉藤 |
| 1 | 営業部 | 6 | 1 | 佐藤 |
| 1 | 営業部 | 7 | 1 | 小澤 |
| 2 | 経理部 | 8 | 2 | 関野 |
| 4 | 法務部 | NULL | NULL | NULL |
+---------+-----------+------+---------+------+
9 rows in set (0.00 sec)
RIGHT JOIN(名前をすべて表示する)
mysql> SELECT * FROM depts RIGHT JOIN employees ON depts.dept_id = employees.dept_id;
+---------+-----------+----+---------+------+
| dept_id | dept_name | id | dept_id | name |
+---------+-----------+----+---------+------+
| 1 | 営業部 | 1 | 1 | 田中 |
| 1 | 営業部 | 6 | 1 | 佐藤 |
| 1 | 営業部 | 7 | 1 | 小澤 |
| 2 | 経理部 | 2 | 2 | 玉木 |
| 2 | 経理部 | 5 | 2 | 斉藤 |
| 2 | 経理部 | 8 | 2 | 関野 |
| 3 | 技術部 | 3 | 3 | 鈴木 |
| 3 | 技術部 | 4 | 3 | 山本 |
| NULL | NULL | 9 | 0 | 中村 |
+---------+-----------+----+---------+------+
9 rows in set (0.00 sec)
👆OUTER JOINはLEFTなら左側に書いたテーブルはすべて表示。右側のテーブルはNULLがあっても表示される。逆然り。
✅UNION(両テーブルのNULL含めて表示させたい場合)
mysql> SELECT * FROM depts LEFT JOIN employees ON depts.dept_id = employees.dept_id -> union -> SELECT * FROM depts RIGHT JOIN employees ON depts.dept_id = employees.dept_id; +---------+-----------+------+---------+------+ | dept_id | dept_name | id | dept_id | name | +---------+-----------+------+---------+------+ | 1 | 営業部 | 1 | 1 | 田中 | | 2 | 経理部 | 2 | 2 | 玉木 | | 3 | 技術部 | 3 | 3 | 鈴木 | | 3 | 技術部 | 4 | 3 | 山本 | | 2 | 経理部 | 5 | 2 | 斉藤 | | 1 | 営業部 | 6 | 1 | 佐藤 | | 1 | 営業部 | 7 | 1 | 小澤 | | 2 | 経理部 | 8 | 2 | 関野 | | 4 | 法務部 | NULL | NULL | NULL | | NULL | NULL | 9 | 0 | 中村 | +---------+-----------+------+---------+------+ 10 rows in set (0.00 sec)
🙄UNIONはまともに使ったの初めてかも。。。なるほど!!
✅INNER JOIN(NULLは表示されない)
mysql> select * from depts inner join employees on depts.dept_id = employees.dept_id; +---------+-----------+----+---------+------+ | dept_id | dept_name | id | dept_id | name | +---------+-----------+----+---------+------+ | 1 | 営業部 | 1 | 1 | 田中 | | 1 | 営業部 | 6 | 1 | 佐藤 | | 1 | 営業部 | 7 | 1 | 小澤 | | 2 | 経理部 | 2 | 2 | 玉木 | | 2 | 経理部 | 5 | 2 | 斉藤 | | 2 | 経理部 | 8 | 2 | 関野 | | 3 | 技術部 | 3 | 3 | 鈴木 | | 3 | 技術部 | 4 | 3 | 山本 | +---------+-----------+----+---------+------+ 8 rows in set (0.00 sec)
👆等価結合と同じ結果が得られる(厳密には違うらしい)
Laravelでリレーション
.envの編集
DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3307 DB_DATABASE=testdb DB_USERNAME=root DB_PASSWORD=
config/database.phpの編集 参考
'charset' => 'utf8', 'collation' => 'utf8_unicode_ci',
マイグレーションファイルの作成
$ php artisan make:migration create_depts_table --create=depts Created Migration: 2020_08_29_082257_create_depts_table $ php artisan make:migration create_employees_table --create=employees Created Migration: 2020_08_29_082336_create_employees_table
depts
Schema::create('depts', function (Blueprint $table) { $table->bigIncrements('id'); $table->string('dept_name'); $table->timestamps(); });
employees
Schema::create('employees', function (Blueprint $table) { $table->bigIncrements('id'); $table->integer('dept_id'); $table->string('user_name'); $table->timestamps(); });
マイグレート後、データの投入
insert into depts(id,dept_name) values(1,‘営業部’); insert into depts(id,dept_name) values(2,‘経理部’); insert into depts(id,dept_name) values(3,‘技術部’); insert into depts(id,dept_name) values(4,‘法務部’);
insert into employees(dept_id,user_name) values(1,’田中’); insert into employees(dept_id,user_name) values(2,’玉木’); insert into employees(dept_id,user_name) values(3,’鈴木
‘); insert into employees(dept_id,user_name)
values(3,’山本’); insert into employees(dept_id,user_name) values(2,’斉藤’); insert into employees(dept_id,user_name) values(1,’佐藤’); insert into employees(dept_id,user_name) values(1,’小澤’); insert into employees(dept_id,user_name) values(2,’関野’); insert into employees(dept_id,user_name) values(0,’中村’);
モデルの作成
※今回はdeptsがemployeesの親テーブルになる。
$ php artisan make:model Dept Model created successfully. $ php artisan make:model Employee Model created successfully.
Dept.php
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Dept extends Model { //timestamps利用しない public $timestamps = false; //primaryKeyの変更 //protected $primaryKey = "dept_id"; //hasMany設定 public function employees() { return $this->hasMany('App\Employee'); } }
Employee.php
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Employee extends Model { //timestamps利用しない public $timestamps = false; //belongsTo設定 public function dept() { return $this->belongsTo('App\Dept'); } }
TInkerで確認
>>> App\Employee::find(1)->dept
=> App\Dept {#3863
id: 1,
dept_name: "営業部",
created_at: null,
updated_at: null,
}
>>> App\Dept::find(1)->employees
=> Illuminate\Database\Eloquent\Collection {#3825
all: [
App\Employee {#4080
id: 1,
dept_id: 1,
user_name: "田中",
created_at: null,
updated_at: null,
},
App\Employee {#4081
id: 6,
dept_id: 1,
user_name: "佐藤",
created_at: null,
updated_at: null,
},
App\Employee {#4082
id: 7,
dept_id: 1,
user_name: "小澤",
created_at: null,
updated_at: null,
},
],
}
🙄地味にhasManyの時はテーブル名が複数形になるの知らなくてハマりました。。。
上記をSQL文で書くと。。。
mysql> select * from depts where id = (select dept_id from employees where id = 1); +----+-----------+------------+------------+ | id | dept_name | created_at | updated_at | +----+-----------+------------+------------+ | 1 | 営業部 | NULL | NULL | +----+-----------+------------+------------+ 1 row in set (0.00 sec) mysql> select * from employees where dept_id = (select id from depts where id = 1); +----+---------+-----------+------------+------------+ | id | dept_id | user_name | created_at | updated_at | +----+---------+-----------+------------+------------+ | 1 | 1 | 田中 | NULL | NULL | | 6 | 1 | 佐藤 | NULL | NULL | | 7 | 1 | 小澤 | NULL | NULL | +----+---------+-----------+------------+------------+ 3 rows in set (0.00 sec)
👆リレーションを結んでいると確かに楽に記述できますね!!
web.php
Route::get('/hasmany', function () { $dept = App\Dept::find(1); echo "部署名: " . $dept->dept_name . "メンバー<br>"; $employees = $dept->employees; foreach ($employees as $employee) { echo $employee->user_name . "<br>"; } echo $employees->count() . "名<br>"; return; });
http://127.0.0.1:8000/hasmanyにアクセス
LaravelでJOIN句
INNER JOIN
// ORM >>> App\Employee::select()->join('depts','employees.dept_id','=','depts.id')->first(); => App\Employee {#4087 id: 1, dept_id: 1, user_name: "田中", created_at: null, updated_at: null, dept_name: "営業部", } // QueryBuilder >>> DB::table('employees')->join('depts','employees.dept_id','=','depts.id')->first(); => {#3862 +"id": 1, +"dept_id": 1, +"user_name": "田中", +"created_at": null, +"updated_at": null, +"dept_name": "営業部", }
LEFT JOIN
// ORM App\Employee::select()->leftjoin('depts','employees.dept_id','=','depts.id')->get(); // QueryBuilder DB::table('employees')->leftjoin('depts','employees.dept_id','=','depts.id')->get();
👆employeesを優先して結合。deptsがnullでも表示する。※rightjoinはその逆。
JOIN句に制約を指定したい場合はONを使う
// ORM App\Employee::select()->join('depts', function ($join) { $join->on('employees.dept_id', '=', 'depts.id') ->where('employees.id', '>', 5); })->get(); // QueryBuilder DB::table('employees')->join('depts', function ($join) { $join->on('employees.dept_id', '=', 'depts.id') ->where('employees.id', '>', 5); })->get();
※function{で区切らないとTinkerは複数行の検証ができないので注意。
※leftjoinもrightjoinもjoinの部分を置換すれば使える。
クエリスコープの利用
モデル内にメソッド(ローカルスコープ)を用意しておくと、モデル呼び出しの際にメソッドを絞り込み条件として使うことができる。
Dept.phpに以下のメソッドを追記。
public function scopeSales($query) { return $query->where('dept_name', "営業部"); }
TInkerで確認
>>> App\Dept::sales()->get() => Illuminate\Database\Eloquent\Collection {#4018 all: [ App\Dept {#4075 id: 1, dept_name: "営業部", created_at: null, updated_at: null, }, ], }
リレーション
※foreign keyについて
外部キーとは他のテーブルのデータに参照(依存)するようにカラムにつける制約のことです。
外部キーを設定すると、関連するテーブル間の整合性をデータベースに保証させることができます。
備忘録
テーブル作成
参考:Laravelのモデルとマイグレーションを同時に作成する方法
同時作成
$ php artisan make:model Person --migration Model created successfully. Created Migration: 2020_08_21_232513_create_people_table
🙄自動でPersonからpeopleテーブルできるのはすごいですね!!
個別作成
php artisan make:model Person php artisan make:migration create_people_table --create=people
コメントを残す