DB操作

DB操作は、大きく「生SQL（DBファサード）」「クエリビルダ」「Eloquent ORM」の3パターンで行います。
事前準備として既に「DB接続設定」が完了しているものとし、DBは「MySQL」として話を進めていきます。
　※ 初期テーブルの生成も作成済みとします

○ [Eloquent ORM]について

本説明は割愛します。
理由としては、[Eloquent ORM]でのDB操作は学習コストが高く、またフレームワークの独自性も非常に強いためです。
第三者が見た時にわかり難い。システムリプレイス等で元のSQLがわかり難い。
など、フレームワークにはありがちな機能ですが、個人的には不要と感じます。。

以下「DB」クラスを宣言します。
use Illuminate\Support\Facades\DB;

○ SELECT

以下を任意のメソッドへ記入します。

任意のコントローラ

public function index(Request $request) {
    $users = DB::select('select * from users where name = ?', ['hoge_user']);
    var_dump($users);exit;            // ダンプする
    return view('hoge/index', []);
}

画面へアクセスすると、以下の様な表示となります。
array(0) { }
これは、現状「users」テーブルが空なので正常な結果となります。

○ INSERT

先ほどのメソッドに追記します。

任意のコントローラ

public function index(Request $request) {
    DB::insert('insert into users (name, email, password) values (?, ?, ?)', ['hoge_user', '[email protected]', '9999']);
    $users = DB::select('select * from users where name = ?', ['hoge_user']);
    var_dump($users);exit;            // ダンプする
    return view('hoge/index', []);
}

画面へアクセスすると、今度はオブジェクトが表示されたハズです。
これはDBの登録処理が正常に実施された事を意味します。

ポイント

DBへ登録した場合は、登録したIDを確認したい場合があると思います。
その場合は、登録後以下[SELECT]を発行する事で確認できます。
$userId = DB::selectOne("SELECT LAST_INSERT_ID() as id")->id;

○ UPDATE

先ほどのメソッドを変更します。

任意のコントローラ

public function index(Request $request) {
    DB::update("update users set password = '8888' where id = ?", [1]);
    $users = DB::select('select * from users where name = ?', ['hoge_user']);
    var_dump($users);exit;            // ダンプする
    return view('hoge/index', []);
}

画面へアクセスすると、オブジェクト内の項目「password」の値が「8888」となっているハズです。

○ DELETE

先ほどのメソッドを変更します。

任意のコントローラ

public function index(Request $request) {
    DB::delete('delete from users where id = ?', [1]);
    $users = DB::select('select * from users where name = ?', ['hoge_user']);
    var_dump($users);exit;            // ダンプする
    return view('hoge/index', []);
}

画面へアクセスすると、削除された事により以下の様な表示となります。
array(0) { }

○ SELECT

○ 全件取得

以下を任意のメソッドへ記入します。

任意のコントローラ

public function index(Request $request) {
    $users = DB::table('users')->get();
    var_dump($users);exit;
    return view('hoge/index', []);
}

画面へアクセスすると、以下の様な表示となります。
object(Illuminate\Support\Collection)#270 (2) { ["items":protected]=> array(0) { } ["escapeWhenCastingToString":protected]=> bool(false) }
※ ちょっとわかりにくい。。

○ 条件付取得

以下を任意のメソッドへ記入します。

任意のコントローラ

public function index(Request $request) {
    users = DB::table('users')->where('name', 'hoge_user')->orderBy('created_at', 'desc')->get();
    var_dump($users);exit;
    return view('hoge/index', []);
}

○ 1件取得

以下を任意のメソッドへ記入します。

任意のコントローラ

public function index(Request $request) {
    $users = DB::table('users')->where('id', 1)->first();
    var_dump($users);exit;
    return view('hoge/index', []);
}

○ INSERT

以下を任意のメソッドへ記入します。

任意のコントローラ

public function index(Request $request) {
    DB::table('users')->insert([
        'name' => 'hoge_user',
        'email' => '[email protected]',
        'password' => '9999',
    ]);
    $users = DB::table('users')->get();
    var_dump($users);exit;            // ダンプ
    return view('hoge/index', []);
}

ポイント

DBへ登録した場合は、登録したIDを確認したい場合があると思います。
その場合はメソッド「insert」を「insertGetId」へ変更する事で返却値で《ID》を取得できます。

○ UPDATE

以下を任意のメソッドへ記入します。

任意のコントローラ

public function index(Request $request) {
    DB::table('users')->where('id', 2)->update(['password' => '8888']);
    $users = DB::table('users')->get();
    var_dump($users);exit;            // ダンプ
    return view('hoge/index', []);
}

○ DELETE

以下を任意のメソッドへ記入します。

任意のコントローラ

public function index(Request $request) {
    DB::table('users')->where('id', 2)->delete();
    $users = DB::table('users')->get();
    var_dump($users);exit;            // ダンプ
    return view('hoge/index', []);
}

○ 複数処理をまとめて成功・失敗させたい場合に使います

任意のコントローラ

public function index(Request $request) {
    try {
        DB::transaction(function () {
            DB::table('users')->insert([
                'name' => 'hoge_user',
                'email' => '[email protected]'
                'password' => '5555',
            ]);
        });
    } catch (\Illuminate\Database\QueryException $e) {
        var_dump($e->getMessage());exit;        // ダンプ
    }
    return view('hoge/index', []); }

[DB::transaction]内で例外が送出されなければ自動的にコミットします。
※ 例外は必ず「\Illuminate\Database\QueryException」でキャッチします
※ SQL制御は「DBファサード」「クエリビルダ」どちらでも同じです

○ 個別にコミットしたい場合に使います

任意のコントローラ

public function index(Request $request) {
    DB::beginTransaction();
    try {
        DB::insert('insert into users (name, email) values (?, ?)', [
            'hoge_user',
            '[email protected]'
             'password' => '5555',
        ]);
        DB::commit();
    } catch (\Illuminate\Database\QueryException $e) {
        DB::rollback();
        var_dump($e->getMessage());exit; // ダンプ
    }
    return view('hoge/index', []);
}

Laravelが初期に作成する、例えば「users」テーブルには以下カラムが存在します。
  ・created_at     登録日時
  ・updated_at    更新日時
この２つの項目は、実は今回割愛した「Eloquent ORM」では設定次第では自動で登録されます。
やはり常に登録するカラムは、機械的に勝手にコッソリと登録されて欲しいですよね。。
しかし、これまで紹介してきた「生SQL（DBファサード）」「クエリビルダ」にはそんな設定は用意されていません。
そこで頑張ってカスタマイズする事にします!!

生SQLに対して更新項目を増やすのは流石に難しいので、「クエリビルダ」に対してカスタマイズします。
カスタマイズの概要としては、登録・更新メソッドである「update」「insert」「insertGetId」をオーバーライドする方法とします。
対象ファイルは既存ファイルである以下となります。
  「～\app\Providers\AppServiceProvider.php」
少し整理しましたが、初期状態は以下の様な内容です。

AppServiceProvider.php

<?php
    namespace App\Providers;
    use Illuminate\Support\ServiceProvider;
    class AppServiceProvider extends ServiceProvider {
        public function register(): void {
            //
        }
        public function boot(): void {
            //
        }
    }

本ファイルでそれぞれのメソッドをオーバーライドしますが、注意点としてLaravelのメソッドである「update」「insert」「insertGetId」は上手くオーバーライドできません。
そこで独自のメソッド名を作成する必要があり、今回は以下の様なメソッド名で紹介します。
「myUpdate」「myInsert」「myInsertGetId」

それでは[AppServiceProvider]クラスを以下の様に修正して見ましょう。

AppServiceProvider.php

<?php
    namespace App\Providers;
    use Illuminate\Support\ServiceProvider;
    class AppServiceProvider extends ServiceProvider {
        public function register(): void {
            //
        }
        public function boot(): void {
            // 日時自動登録テーブル一覧
            $autoTables = ['users'];

            // 独自更新メソッド（myInsert）をオーバーライド
            Builder::macro('myInsert', function (array $values) use($autoTables) {
                $table = app('current_table');
                if (in_array($table, $autoTables)) {
                    $values['created_at'] = now();
                }
                return $this->insert($values);
            });

            // 独自更新メソッド（myInsertGetId）をオーバーライド
            Builder::macro('myInsertGetId', function (array $values) use($autoTables) {
                $table = app('current_table');
                if (in_array($table, $autoTables)) {
                    $values['created_at'] = now();
                }
                return $this->insertGetId($values);
            });

            // 独自更新メソッド（myUpdate）をオーバーライド
            Builder::macro('myUpdate', function (array $values) use($autoTables) {
                $table = app('current_table');
                if (in_array($table, $autoTables)) {
                    $values['updated_at'] = now();
                }
                return $this->update($values);
            });

            // テーブル名取得
            DB::macro('table', function ($table, $callback = null) {
                // テーブル名を取得・記録
                app()->instance('current_table', $table);
                if ($callback) {
                return $this->connection()->table($table)->pipe($callback);
                }
                return $this->connection()->table($table);
            });
        }
    }

以下コードの説明となります。

○ Builder::macro()関数

第１引数に設定した名前をオーバーライドします。
つづく無名関数に登録/更新する配列が格納されます。
use関数でテーブル一覧配列をアサインする事で処理内での自動で処理するテーブルの切り分けをしています。

○ app('current_table')関数

現在処理しようとしているテーブル名を取得します

○ return

単純に本来Laravelが持つメソッドに対して処理を渡しています。
なので本来のメソッド名「insedrt」「insertGetId」「update」となります。

○ DB::macro()関数

現在処理しようとしているテーブル名をLaravelのサービスコンテナに一時保存。
app('current_table')で後からどこでも取得可能にしている。
※ if ($callback)に関しては特に無視しても問題ありません

ポイント

日時に限らず、「日」「時刻」を分けたカラムを作成しても良いと思います。
またセッション値も取得可能ですので、登録/更新ユーザーカラムを作成しても良いかもしれません。

先ほど①の方法でも良いですが、以下の不満点もあります。

・独自メソッド「myInsert」が何かイケてない。
・そもそもオーバーライドが分かりにくい。

それではいっその事オリジナルのクラスを作っちゃいましょう。
作成する場所は「～\app\Models\Dao.php」でどうでしょう？
直感的にも納得のいく場所とファイル名だと思います。

app\Models\Dao.php

<?php
namespace App\Models;
use Exception;
use DB;
class Dao {
    // 自動登録対象テーブル一覧
    const AUTO_TABLE = ['users', 'foos'];

    public function __construct() {}

    // 対象のテーブルに対し「$ent」の内容で新規登録する
    public function insert($table_name, $ent = []): void {
        if(in_array($table_name, self::AUTO_TABLE)) {
            $ent['created_at'] = now();
        }
        try {
            DB::table($table_name)->insert($ent);
        } catch(Exception $e) {
            // 失敗処理
        }
    }

    // 対象のテーブルに対し「$ent」の内容で新規登録し、登録したIDを返却する
    public function insertGetId($table_name, $ent = []): int {
        if(in_array($table_name, self::AUTO_TABLE)) {
            $ent['created_at'] = now();
        }
        try {
            return DB::table($table_name)->insertGetId($ent);
        } catch(Exception $e) {
            // 失敗処理
        }
    }

    // 対象のテーブルに対し「$ent」の内容で更新する
    public function update($table_name, $ent = []): void {
        if(in_array($table_name, self::AUTO_TABLE)) {
            $ent['updated_at'] = now();
        }
        try {
            DB::table($table_name)->update($ent);
        } catch(Exception $e) {
            // 失敗処理
        }
    }
}

これで『Dao』クラスは完成です。
次に任意のコントローラから制御してみましょう。

任意のコントローラ

<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use DB;
use App\Models\Dao;

class HogeController extends Controller {
    // 登録処理
    public function dataSave(Request $request) {
        $ent = [];
        $ent['name'] = 'hoge_user';
        $ent['email'] = '[email protected]';
        $ent['password'] = '9999';

        $dao = new Dao;

        DB::beginTransaction();
        $dao->insert('users', $ent);
        DB::commit();
    }
}

ポイント

こちらの制御の方が自由度が高いと思います。
例えば今回のサンプルではコントローラ側では『try ～ catch』していません。
それはコントローラ側では極力ゴチャゴチャした処理は記述したくないからです。
『Dao』側では一応[catch]はできる様にしていますが、ここに何を記述すればコントローラ側に失敗を伝えられるでしょうか？

例えばこんなのはどうでしょうか。
『Dao』の各メソッドの第１引数に、参照連動でオブジェクト『$reply』を設定します。
『$reply』クラスの中身は、例えば「$result」というプロパティを持たせます。

もうお分かりですね？
そう、「catch」されたら『$result = false;』としまえば良いのです。
コントローラでは引数が増えた事で以下の様な呼び出し方になっています。
$dao->insert($reply, 'users', $ent);
参照連動なので、DB制御の《成功|失敗》は『$reply->result』で確認できます。

複雑なSQLはどうしてもプレースホルダ付の平文で管理する事になると思います。
そこで苦労するのが「保管場所」と「呼出し方」となります。

・保管場所一箇所にまとめたい
・呼出し方できるだけシンプルに分かり易く呼出したい

○ 保管場所

SQLの平文を一言でいえば「リソース」です。
Laravelには確かそんなディレクトリが。。「～\resources」です。
ここに「～\resources\sql」ディレクトリを作成して保管する事にします。

ファイル名は任意ですが、分かり易く以下とします。
{テーブル名}_{SQL内容を端的に表す単語}.sql
名前を「users_name.php」とし、ファイルの中身は以下とします。
select * from users where name = ?;

○ 呼出し方

単純に呼出すには煩雑になりそうです。
何かDB関連で都合の良いクラスはないかな。。
あ、さっき（自動的に「登録日時」「更新日時」を登録したい②）作った【Dao】クラスがある!!
ここにメソッドを追加します。

app\Models\Dao.php

// SQL文を取得する
public function getRawSqlString($sql_name): string {
return file_get_contents(resource_path('sql/' . $sql_name . '.sql'));
}

それではコントローラから呼び出してSQL発行してみましょう。

任意のコントローラ

public function index(Request $request) {
$dao = new Dao;
$users = DB::select($dao->getRawSqlString('users_name'), ['hoge_user']);
}

これで無事《生SQL》達を整理できました。

Laravelは抽出結果をオブジェクトで返却します。
これが地味に使いにくいので配列で受け取れるようにします。

○ 生SQL（DBファサード）の場合

少し面倒で、一旦オブジェクトで受け取ってから配列にします。
例）

$result = DB::select($sql, [$name]);
$users = array_map('get_object_vars', $result);

○ クエリビルダの場合

こちらは簡単でメソッドチェーンを追加するだけです。例）

$users = DB::table('users')->where('name', 'hoge')->get()->toArray();