今回はPHPフレームワークの一つのLaravelの中から、データベースのクエリビルダを自動構築する設定を検討してみたいと思います。具体的には取得条件のテーブルなどの定義情報を渡して、クエリビルダを構築するメソッドを検討します。今回利用するDBはMySQLです。SQLの基本が理解できていることが前提となります。
またこのメソッドに関連する他の記事については以下を参照して下さい。
Laravelのクエリビルダを構築するメソッドの検討 - Select文を作成する②複数の結合条件の対応など - 商売力開発ブログ
クエリビルダを活用する
Laravelではクエリビルダが用意されており、このメソッド使用することでクエリを作成してデータベースを操作することができることは、以前紹介しました。
www.prj-alpha.biz
クエリビルダを利用することで簡単にデータベースからデータを取得できますが、使用頻度が多い場合は毎回記述するとなると結構面倒です。そこでクエリビルダを構築するメソッドを検討したいと思います。今回は簡単なデータの取得の部分、要はSelect文を構築するクエリビルダについて考えていきたいと思います。
クエリビルダとSelect文の構成要素
Select文の構成は簡単なものだと以下のようになります。
SELECT select_list FROM table_list [ WHERE search_conditions ] [ GROUP BY group_by_list ] [ HAVING search_conditions ] [ ORDER BY order_list [ ASC | DESC ] ]
今回はこの中で、SELECT、FROMについてクエリビルダを構築するメソッドを検討します。以下のようなSelect文を構成するためのクエリ定義情報を受け取ると、その定義に合わせたクエリビルダを構築するイメージです。
$queryDef=[ 'table' => [], 'join' => [], 'select' => [], ];
テーブルの設定と結合条件
まず始めにテーブルの設定に関して考えてみます。クエリビルダで記述する場合、DBファサードに基となるテーブルを設定し、結合を行う場合はjoinやleftJoinメソッドを利用し結合対象のテーブルを指定し、結合条件を記載します。
$query = DB::table('table1'); //内部結合 $query->join('joinTable2', 'table1.col1', '=', 'joinTable2.col1'); //左外部結合 $query->leftJoin('leftTable3', 'table1.col1', '=', 'leftTable3.col1');
ここで考えたいのがテーブル名についてです。結合条件にもテーブル名を持っていますが、これを別名で置き換えるようにします。別名を付けることで、同じテーブルとの結合が複数必要な場合にも対応することができます。例えば、クエリ定義情報のtable設定が以下のようになってるとします。
$queryDef['table']=['table1','joinTable2','leftTable3'];
結合条件にはテーブルのNoを指定して書き換えるようにします。わかりやすくするため、テーブルのプレフィックスも設定します。
//テーブル名のプレフィックス $prefix = 'tbl'; $query = DB::table($queryDef['table'][0].' as '.$prefix.'0'); //内部結合 $query->join($queryDef['table'][1].' as '.$prefix.'1', $prefix.'0'.'.col1', '=', $prefix.'1'.'.col1'); //左外部結合 $query->leftJoin($queryDef['table'][2].' as '.$prefix.'2', $prefix.'0'.'.col1', '=', $prefix.'2'.'.col1');
この状態でどのようなSQLが発行されるか、確認してみます。以下のtoSqlメソッドで確認することができます。
logger($query->toSql());
この結果は以下のようになりました。
select * from `table1` as `tbl0` inner join `joinTable2` as `tbl1` on `tbl0`.`col1` = `tbl1`.`col1` left join `leftTable3` as `tbl2` on `tbl0`.`col1` = `tbl2`.`col1`
テーブルをクエリ定義情報から設定できるイメージができたので、続いては結合条件の部分です。結合条件には対象のテーブルと、結合するカラムの情報が必要になります。クエリ定義情報のjoin設定を以下のようにします。
$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'] ] ], ];
この定義情報から結合するようにしてみます。
//テーブル名のプレフィックス $prefix = 'tbl'; $query = DB::table($queryDef['table'][0].' as '.$prefix.'0'); for ($i=0; $i < count($queryDef['join']); $i++) { //結合するテーブルと別名の設定 $joinTable = $queryDef['table'][$queryDef['join'][$i]['tblNo']].' as '.$prefix.$queryDef['join'][$i]['tblNo']; //左結合カラム $leftCol = $prefix.$queryDef['join'][$i]['cond'][0]['leftTblNo'].'.'.$queryDef['join'][$i]['cond'][0]['leftCol']; //等号などの演算子 $operator = $queryDef['join'][$i]['cond'][0]['ope']; //右結合カラム $rightCol = $prefix.$queryDef['join'][$i]['cond'][0]['rightTblNo'].'.'.$queryDef['join'][$i]['cond'][0]['rightCol']; //結合するタイプごとに設定するメソッドを変更する if($queryDef['join'][$i]['type']=='join'){ //join $query->join($joinTable,$leftCol,$operator,$rightCol); }elseif($queryDef['join'][$i]['type']=='leftJoin'){ //leftJoin $query->leftJoin($joinTable,$leftCol,$operator,$rightCol); } }
この状態でtoSqlメソッドで確認すると同じ結果が得られました。
取得するカラムの設定
取得対象のテーブルの準備ができましたので、続いては取得するカラムの設定です。取得対象となるカラムの情報にはテーブルとカラムの情報が必要です。テーブルはNoを指定するようにします。クエリ定義情報のselect設定を以下のようにします。
$queryDef['select']=[ [ 'tblNo' => 0, 'column' => 'col1', 'alias' => NULL, ], [ 'tblNo' => 1, 'column' => 'col2', 'alias' => 'ColName', ], ];
この定義情報からカラムを追加するようにしてみます。
//テーブル名のプレフィックス $prefix = 'tbl'; $query = DB::table($queryDef['table'][0].' as '.$prefix.'0'); for ($i=0; $i < count($queryDef['join']); $i++) { //結合するテーブルと別名の設定 $joinTable = $queryDef['table'][$queryDef['join'][$i]['tblNo']].' as '.$prefix.$queryDef['join'][$i]['tblNo']; //左結合カラム $leftCol = $prefix.$queryDef['join'][$i]['cond'][0]['leftTblNo'].'.'.$queryDef['join'][$i]['cond'][0]['leftCol']; //等号などの演算子 $operator = $queryDef['join'][$i]['cond'][0]['ope']; //右結合カラム $rightCol = $prefix.$queryDef['join'][$i]['cond'][0]['rightTblNo'].'.'.$queryDef['join'][$i]['cond'][0]['rightCol']; //結合するタイプごとに設定するメソッドを変更する if($queryDef['join'][$i]['type']=='join'){ //join $query->join($joinTable,$leftCol,$operator,$rightCol); }elseif($queryDef['join'][$i]['type']=='leftJoin'){ //leftJoin $query->leftJoin($joinTable,$leftCol,$operator,$rightCol); } } for ($i=0; $i < count($queryDef['select']); $i++) { //テーブルとカラムの設定 $addCol=$prefix.$queryDef['select'][$i]['tblNo'].'.'.$queryDef['select'][$i]['column']; //別名の設定 if(!empty($queryDef['select'][$i]['alias'])){ //別名を設定する $addCol=$addCol.' as '.$queryDef['select'][$i]['alias']; } //カラムの設定 $query->addSelect($addCol); }
この状態でtoSqlメソッドで確認すると以下の結果が得られました。
select `tbl0`.`col1` , `tbl1`.`col2` as `ColName` from `table1` as `tbl0` inner join `joinTable2` as `tbl1` on `tbl0`.`col1` = `tbl1`.`col1` left join `leftTable3` as `tbl2` on `tbl0`.`col1` = `tbl2`.`col1`
問題なく別名も設定されました。
まとめ
今回はLaravelのデータベースのクエリビルダについて、定義情報から構築する方法を検討しました。
ここまではSelectを行うのに最低限の設定となります。この他の設定については、また別の機会で紹介します。
以上
【関連するリンク】 www.prj-alpha.biz www.prj-alpha.biz
