今回はPHPフレームワークの一つのLaravelの中から、データベースのクエリビルダを自動構築する設定を検討してみたいと思います。具体的には取得条件のテーブルなどの定義情報を渡して、クエリビルダを構築するメソッドを検討します。今回利用するDBはMySQLです。SQLの基本が理解できていることが前提となります。
これまでの内容は以下を参照してください。
Select文の構成
前回までの説明で結合条件と取得するカラムについて対応しました。以下のようなクエリ定義情報を渡すことでクエリビルダを構築するようにしました。
$queryDef=[ 'table' => [], 'join' => [], 'select' => [], ]; $queryDef['table']=['table1','joinTable2','leftTable3']; $queryDef['join']=[ [ 'tblNo' => 1, 'type' => 'join', 'cond'=>[ ['leftTblNo' => 0,'leftCol'=>'col1','ope'=>'=','rightTblNo' => 1,'rightCol'=>'col1'] ] ], [ 'tblNo' => 2, 'type' => 'leftJoin', 'cond'=>[ ['leftTblNo' => 0,'leftCol'=>'col1','ope'=>'=','rightTblNo' => 2,'rightCol'=>'col1'] ] ], ]; $queryDef['select']=[ [ 'tblNo' => 0, 'column' => 'col1', 'alias' => NULL, ], [ 'tblNo' => 1, 'column' => 'col2', 'alias' => 'ColName', ], ];
ただこのままだと、使いにくい部分があるので、変更を加えていきます。
取得する部分の設定
取得するカラムの設定については、テーブルのNoと列名を指定することで取得してます。このままだと計算式などができないので、DB::rawメソッドで対応できるようにします。例えば以下のようなrawフラグをクエリ定義情報に追加してみます。'column'にDB::rawメソッドで使用する文字列を設定しています。注意が必要なのは、テーブル名のプレフィックスとして設定する文字列（この場合、「tbl」）を含めた形式で記述する必要があります。プレフィックスを変更する場合、こちらの設定も変更する必要が出てきます。
$queryDef['select']=[ [ 'tblNo' => 0, 'column' => 'col1', 'alias' => NULL, 'flgRaw' => FALSE, ], [ 'tblNo' => 1, 'column' => 'col2', 'alias' => 'ColName', 'flgRaw' => FALSE, ], [ 'tblNo' => NULL, 'column' => 'tbl1.col3+tbl2.col3', 'alias' => 'ColRaw', 'flgRaw' => TRUE, ], ];
この追加したフラグを元に、addSelectの部分を変更します。
for ($i=0; $i < count($queryDef['select']); $i++) { //flgRawの有無 if($queryDef['select'][$i]['flgRaw']){ //flgRawの場合、カラムからのみ設定する $addCol=$queryDef['select'][$i]['column']; }else{ //テーブルとカラムの設定 $addCol=$prefix.$queryDef['select'][$i]['tblNo'].'.'.$queryDef['select'][$i]['column']; } //別名の設定 if(!empty($queryDef['select'][$i]['alias'])){ //別名を設定する $addCol=$addCol.' as '.$queryDef['select'][$i]['alias']; } //flgRawの有無 if($queryDef['select'][$i]['flgRaw']){ //DB::rawとして設定する $addCol=DB::raw($addCol); } //カラムの設定 $query->addSelect($addCol); }
この状態でtoSqlメソッドで確認すると以下の結果が得られました。
select `tbl0`.`col1` , `tbl1`.`col2` as `ColName` , tbl1.col3 + tbl2.col3 as ColRaw from `table1` as `tbl0` inner join `joinTable2` as `tbl1` on `tbl0`.`col1` = `tbl1`.`col1` left join `leftTable3` as `tbl2` on `tbl0`.`col1` = `tbl2`.`col1`
問題なくDB::rawメソッドの部分も設定されました。
複数条件による結合
joinやleftJoinの結合条件について、その結合条件の数が一つの場合はメソッドの引数に指定しますが、複数ある場合は、第2引数にクロージャを指定して設定することになります。
//結合条件が一つの場合 $query->join('table2', 'table1.col1', '=', 'table2.col1'); //結合条件が複数の場合 $query->join('table2', function($join){ $join->on('table1.col1','=','table1.col1'); $join->on('table1.col2','=','table1.col2'); });
複数の結合条件を構築するには、クエリ定義情報から動的にクロージャを定義し、それを渡すことによって対応することができそうです。ですが動的にメソッドを定義することは、ちょっと調査しても簡単に対応できそうにないので、他の方法で対応します。
具体的にどのように対応していくかと言うと、結合条件が一つの場合の書き方とDB::rawメソッドを利用して、複数の条件を渡します。結合条件の左辺の部分をDB::rawメソッドで設定し複数の条件と最終条件の左辺まで記述して対応するイメージとなります。
//結合条件が一つの場合と同じ形式にして、DB::rawで対応する。 $query->join('table2', DB::raw('条件1' and '条件2' and … and '最終条件の左辺'), '=', 'table2.col1');
クエリ定義情報の結合条件を複数持てるようにし追加してみます。
$queryDef['join']=[ [ 'tblNo' => 1, 'type' => 'join', 'cond'=>[ ['leftTblNo' => 0,'leftCol'=>'col1','ope'=>'=','rightTblNo' => 1,'rightCol'=>'col1'], ['leftTblNo' => 0,'leftCol'=>'col2','ope'=>'=','rightTblNo' => 1,'rightCol'=>'col2'], ] ], [ 'tblNo' => 2, 'type' => 'leftJoin', 'cond'=>[ ['leftTblNo' => 0,'leftCol'=>'col1','ope'=>'=','rightTblNo' => 2,'rightCol'=>'col1'], ] ], ];
このクエリ定義情報から、結合条件が一つの場合と同じ形式にして、DB::rawメソッドで対応するように設定すると以下のようになります。
for ($i = 0; $i < count($queryDef['join']); $i++) { //左辺に設定する文字列 $leftString=''; //結合するテーブルと別名の設定 $joinTable = $queryDef['table'][$queryDef['join'][$i]['tblNo']].' as '.$prefix.$queryDef['join'][$i]['tblNo']; for ($j = 0; $j < count($queryDef['join'][$i]['cond']); $j++) { //左結合カラム $leftCol = $prefix.$queryDef['join'][$i]['cond'][$j]['leftTblNo'].'.'.$queryDef['join'][$i]['cond'][$j]['leftCol']; //等号などの演算子 $operator = $queryDef['join'][$i]['cond'][$j]['ope']; //右結合カラム $rightCol = $prefix.$queryDef['join'][$i]['cond'][$j]['rightTblNo'].'.'.$queryDef['join'][$i]['cond'][$j]['rightCol']; //左辺の文字列への設定 $leftString = $leftString.$leftCol; if($j != count($queryDef['join'][$i]['cond'])-1){ //最後の結合条件でない場合、左辺の文字列に、演算子と右結合カラムを追加し、and条件で設定する $leftString = $leftString.' '.$operator.' '.$rightCol.' and '; }else{ //最後の結合条件、結合の種類ごとに設定する if($queryDef['join'][$i]['type'] == 'join'){ //join $query->join($joinTable,DB::raw($leftString),$operator,$rightCol); }elseif($queryDef['join'][$i]['type'] == 'leftJoin'){ //leftJoin $query->leftJoin($joinTable,DB::raw($leftString),$operator,$rightCol); } } } }
この状態でtoSqlメソッドで確認すると以下の結果が得られました。
select `tbl0`.`col1` , `tbl1`.`col2` as `ColName` , tbl1.col3 + tbl2.col3 as ColRaw from `table1` as `tbl0` inner join `joinTable2` as `tbl1` on tbl0.col1 = tbl1.col1 and tbl0.col2 = `tbl1`.`col2` left join `leftTable3` as `tbl2` on tbl0.col1 = `tbl2`.`col1`
問題なく結合条件が設定されています。これにより複数の結合条件にも動的に対応できるようになっています。この対応によりクエリビルダを自動構築するメソッドの対応範囲が大きくなり、色々な場面で利用できるようになったと思います。
まとめ
今回はLaravelのデータベースのクエリビルダについて、定義情報から構築する方法を検討しました。
今回の検討により、Select項目や結合条件については、ある程度対応できる状態になりました。この他の設定については、また別の機会で紹介します。
以上
【関連するリンク】 www.prj-alpha.biz www.prj-alpha.biz
