Laravel chobi

Laravelのクエリビルダを深掘り

はじめに

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,
       },
     ],
   }

リレーション

参考：外部キーについて（MySQL編）

※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

コメントを残す

RECOMMENDこちらの記事も人気です。