DB操作
初めに
事前準備として既に「DB接続設定」が完了しているものとし、DBは「MySQL」として話を進めていきます。
※ 初期テーブルの生成も作成済みとします
○ [Eloquent ORM]について
理由としては、[Eloquent ORM]でのDB操作は学習コストが高く、またフレームワークの独自性も非常に強いためです。
第三者が見た時にわかり難い。システムリプレイス等で元のSQLがわかり難い。
など、フレームワークにはありがちな機能ですが、個人的には不要と感じます。。
生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」テーブルが空なので正常な結果となります。
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の登録処理が正常に実施された事を意味します。
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', []);
}
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) { }
クエリビルダ
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', []);
}
public function index(Request $request) {
$users = DB::table('users')->where('id', 1)->first();
var_dump($users);exit;
return view('hoge/index', []);
}
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》を取得できます。
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', []);
}
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', []);
}
※ 例外は必ず「\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', []);
}
自動的に「登録日時」「更新日時」を登録したい①
・created_at 登録日時
・updated_at 更新日時
この２つの項目は、実は今回割愛した「Eloquent ORM」では設定次第では自動で登録されます。
やはり常に登録するカラムは、機械的に勝手にコッソリと登録されて欲しいですよね。。
しかし、これまで紹介してきた「生SQL（DBファサード）」「クエリビルダ」にはそんな設定は用意されていません。
そこで頑張ってカスタマイズする事にします!!
生SQLに対して更新項目を増やすのは流石に難しいので、「クエリビルダ」に対してカスタマイズします。
カスタマイズの概要としては、登録・更新メソッドである「update」「insert」「insertGetId」をオーバーライドする方法とします。
対象ファイルは既存ファイルである以下となります。
「～\app\Providers\AppServiceProvider.php」
少し整理しましたが、初期状態は以下の様な内容です。
<?php
namespace App\Providers;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider {
public function register(): void {
//
}
public function boot(): void {
//
}
}
そこで独自のメソッド名を作成する必要があり、今回は以下の様なメソッド名で紹介します。
「myUpdate」「myInsert」「myInsertGetId」
それでは[AppServiceProvider]クラスを以下の様に修正して見ましょう。
<?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);
});
}
}
つづく無名関数に登録/更新する配列が格納されます。
use関数でテーブル一覧配列をアサインする事で処理内での自動で処理するテーブルの切り分けをしています。
なので本来のメソッド名「insedrt」「insertGetId」「update」となります。
app('current_table')で後からどこでも取得可能にしている。
※ if ($callback)に関しては特に無視しても問題ありません
日時に限らず、「日」「時刻」を分けたカラムを作成しても良いと思います。
またセッション値も取得可能ですので、登録/更新ユーザーカラムを作成しても良いかもしれません。
自動的に「登録日時」「更新日時」を登録したい②
・そもそもオーバーライドが分かりにくい。
作成する場所は「～\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) {
// 失敗処理
}
}
}
次に任意のコントローラから制御してみましょう。
<?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平文の格納場所
そこで苦労するのが「保管場所」と「呼出し方」となります。
・呼出し方 できるだけシンプルに分かり易く呼出したい
Laravelには確かそんなディレクトリが。。「～\resources」です。
ここに「～\resources\sql」ディレクトリを作成して保管する事にします。 ファイル名は任意ですが、分かり易く以下とします。
{テーブル名}_{SQL内容を端的に表す単語}.sql
名前を「users_name.php」とし、ファイルの中身は以下とします。
select * from users where name = ?;
何かDB関連で都合の良いクラスはないかな。。
あ、さっき（自動的に「登録日時」「更新日時」を登録したい②）作った【Dao】クラスがある!!
ここにメソッドを追加します。
// SQL文を取得する
public function getRawSqlString($sql_name): string {
return file_get_contents(resource_path('sql/' . $sql_name . '.sql'));
}
public function index(Request $request) {
$dao = new Dao;
$users = DB::select($dao->getRawSqlString('users_name'), ['hoge_user']);
}
SELECT取得結果を連想配列にしたい
これが地味に使いにくいので配列で受け取れるようにします。
○ 生SQL（DBファサード）の場合
例）
$users = array_map('get_object_vars', $result);
DBへ登録した場合は、登録したIDを確認したい場合があると思います。
その場合は、登録後以下[SELECT]を発行する事で確認できます。
$userId = DB::selectOne("SELECT LAST_INSERT_ID() as id")->id;