👩‍🔧 😚 👨🏻‍🚒 PHP SQLクエリジェネレーター 🤛 🕧 🧙🏿

約1年半前に、Web開発を始めました。関数型プログラミングから始めました。約半年前、私はOOPに切り替え、MVCデザインアーキテクチャの使用を開始しました。データベースとのすべての通信と作業が1つのクラスを介して実行されるため、最近、データベースとの作業を最適化するタスクが発生しました。これは、手動でSQLクエリを記述する必要があったため、不便でした。タスクは2段階に分けられました。

データベースに接続するクラスを作成します
データを操作するためのモデルクラスを作成する

最初のタスクは非常に迅速に解決されました。それを実装するために、Singletonデザインパターンを使用しました。

2番目のタスクを実装するには、もう少し時間が必要でした。基本は、サイト上のシンプルなニュース管理モジュールでした。このモジュールの役割には、レコードの選択、作成、削除、および更新の標準機能セットが含まれていました。モデルクラスは、データベースへの接続を実際に作成するDataBaseクラスを継承します。また、このクラスは、 DML操作の sqlコードを生成します。 DataBaseクラスは抽象であり、すべての子クラスにDMLメソッドを実装する必要があります。

以下は、Newsモジュールの管理を担当する抽象メソッドのセットです。

//  abstract public function getRecords($what='*',$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false); //  abstract function addRecord($data=array(),$debug=false); // (-) abstract function deleteRecords($table, $where=NULL,$debug=false); // (-) abstract function setRecords($table,$what,$where,$debug=false); //  abstract function query($sql);

次に、各抽象メソッドについて詳しく説明します。これらのメソッドの実装については、Newsモデルのクラスを説明するときに以下に示します。

入力パラメーターのいくつかは繰り返されるため、本当に必要でない限り、それらを再説明しません。

GetRecordsメソッド

このメソッドを使用すると、SQLの条件に適合するレコードのセットを取得できます。

$ what-テーブルから選択するフィールドの配列を渡します
$ where-キーの連想配列を配列の形式で渡します（ 'Field' => array（ 'sign'、 'value'））。これにより、WHERE述部をより柔軟に使用できます。
$ limit-配列の形式でキーの連想配列を渡します（「初期レコード」、「レコード数」）。この構造により、ページネーションまたは限られた数のレコードの出力を実装できます
$ order-連想配列配列（「フィールド」=>「ソートタイプ」）。任意の数の列でソートできます。
$ join-連想配列配列（「接続タイプ」、配列（「Table1」、「Table2」）、配列（「Alias1」、「Alias2」）、配列（「field1」、「field2」））。通信タイプ：左、内側、右側、外側。表1および表2：通信が確立されるテーブル。 Allias1とAllias2はテーブルのエイリアスです。 Field1とField2はそれぞれです。 PKおよびFKテーブル
$ degub-このパラメーターは、PDOでprepareステートメントを使用する場合に必要なパラメーターだけでなく、既に作成されたSQLクエリをクラスプロパティに保存するために必要です

AddRecordメソッド

このメソッドを使用すると、テーブルにレコードを追加できます。

$ data-'field' => 'value'の形式のパラメーターの連想配列。テーブルに挿入されます

DeleteRecordsメソッド

このメソッドを使用すると、テーブルからレコードを削除できます。

$ table-データが削除されるテーブルの名前

SetRecordsメソッド

このメソッドを使用すると、指定した条件に従ってテーブル内のレコードを更新できます。

$ what-この場合、このパラメーターは、SET演算子で使用される 'field' => 'value'の形式で配列を渡します

クエリ方法

このメソッドを使用すると、非標準のクエリを実行できます。これを行うには、メソッドにパラメーターとしてsqlクエリを渡すだけです。

$ sql-実行するSQLクエリ

クエリのDMLタイプは、SELECT、INSERT、UPDATE、DELETEの4つのタイプで構成されていることを多くの人が知っていると思います。これらのクエリを生成するには、条件付きでいくつかの部分に分割する必要があります。

1）SELECTクエリは、WHAT、JOIN、WHERE、ORDER、LIMIT、GROUP、HAVINGに分かれています。

2）INSERTリクエスト：何

3）UPDATEクエリ：WHAT、WHERE

4）クエリの削除：WHERE。

リクエストのこれらの部分を個別に生成し、それらを結合するだけでよいことがわかりました。これらのパーツをDataBaseクラスで生成するために、この例のようにNewsモジュールだけでなく、他のモジュールにも共通するメソッドが作成されました。

これらの方法をより詳細に検討してください。

CheckWhatメソッド

このメソッドへの唯一の入力は、SELECTを使用して選択するか、UPDATEを使用して更新する必要がある値の連想配列です。

ここで説明する $ whatパラメータ。

 protected function checkWhat($what) { if (is_array($what)) { foreach ($what as $k=>$v) { if (!is_numeric($k)) //      .  ,   prepare statement,   ,        SELECT  { $result['column'][]=$k."=?"; $result['params'][]=$v; } else { $result['column'][]=$v; } } $result['column']=implode(",",$result['column']); } return $result; }

ここではすべてがはっきりしていると思います。配列をチェックし、配列のすべての要素を走査し、クエリ文字列の一部を構築します。

CheckJoinメソッド

ここで、$ joinパラメータについて説明します。また、3つ以上のテーブルを結合する必要がある場合、$結合パラメーターを配列（$ join1、$ join2、.....、$ joinN）として表すことができます

  //  Join  . protected function checkJoin($join) { if (is_array($join) && count($join)>0) { if (!is_array($join[0])) //   ,     join { $res[]=$this->addJoin($join[0],$join[1],$join[2],$join[3]); } else { //    ,    join $res=$this->addJoinArray($join); } return implode(" ",$res); } else { return false; } } // join protected function addJoin($type=' INNER ',$tables,$pseudoName,$rows) { if ($type!=='' && is_array($tables) && is_array($rows)) { $t0=$tables[0]; $t1=$tables[1]; if (is_array($pseudoName) && count($pseudoName)>0) //   ,    { $t0=$pseudoName[0]; $t1=$pseudoName[1]; } return $type." JOIN `".$tables[1]."` `".$pseudoName[1]."` ON `".$t0."`.`".$rows[0]."`=`".$t1."`.`".$rows[1]."`"; } else { return false; } } //   join` protected function addJoinArray($join) { if (is_array($join)) { foreach ($join as $j) { $res[]=$this->addJoin($j[0],$j[1],$j[2],$j[3]); } } return $res; }

CheckWhereメソッド

このメソッドは、リクエストのWHEREコンポーネントのパラメーターをチェックします。ここで、$ whereパラメータについて説明します。

 protected function checkWhere($where) { if (!is_null($where) && is_array($where)) { foreach ($where as $k=>$v) { $part=$k.$v[0]; //     ''  '' if (!is_array($v[1])) //  ,   prepare statement { $part.="?"; $params[]=$v[1]; //     } else { //  ,     IN (array) $part.="(".implode(",",$v[1]).")"; } $res[]=$part; } $result['column']="WHERE ".implode(" AND ",$res); $result['params']=$params; } return $result; }

CheckLimitメソッド

LIMITクエリの述語を生成します。ここではすべてが非常に簡単です。

 protected function checkLimit($limit) { $res=false; if (is_array($limit) && count($limit)>0) { $res=" LIMIT ".$limit['start']; if (isset($limit['count']) && $limit['count']!=='') //        { $res.=", ".$limit['count']; } } return $res; }

CheckOrderメソッド

ORDER述語の生成。

 protected function checkOrder($order) { if (is_array($order) && count($order)>0) { foreach ($order as $row=>$dir) { $res[]=$row." ".$dir; } return "ORDER BY ".implode(",",$res); } else { return false; } }

これが、リクエストの主要部分を生成するために必要なすべてのメソッドです。しかし、以来 WHEREおよびWHATなどのクエリの部分でprepareステートメントを使用するため、これらの部分のパラメーターを結合してPDOに渡す必要があります。このタスクのために、別のメソッドを書きました

CheckParamsメソッド

入力パラメーターは2つの配列です。 WHATおよびWHEREパラメーターの配列。

 protected function checkParams($what,$where) { if (!isset($what) || !is_array($what)) { $params=$where; } else if (!isset($where) && !is_array($where)) { $params=$what; } else { $params=array_merge($what,$where); } return $params; }

SQLクエリを構築する次のステップは、SQLコードの最終生成です。このために、prepareSelectSQL、prepareInsertSQL、prepareDeleteSQL、prepareUpdateSQLの4つのメソッドを作成しました

これらの方法をより詳細に検討してください。

PrepareSelectSQLメソッド

このメソッドのパラメーターは、getRecordsメソッドのパラメーターと一致します。これは、$ what、$ where、$ limit、$ order、$ join、$ debugです。これらのオプションについて説明します。

 protected function prepareSelectSQL($what=array('*'),$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false) { $what=$this->checkWhat($what); $where=$this->checkWhere($where); $limit=$this->checkLimit($limit); $order=$this->checkOrder($order); $j=$this->checkJoin($join); $sql="SELECT ".$what['column']." FROM `".$this->table."` `tb` ".$j." ".$where['column']." ".$order." ".$limit; $params=$this->checkParams($what['params'],$where['params']); if ($debug) //  true,   sql      . { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); //   sql   . }

PrepareInsertSQLメソッド

この方法はより簡単です限定された述語とパラメーターのセットを使用します

 protected function prepareInsertSQL($data,$table,$debug=false) { $params=$values=$column=array(); foreach ($data as $c=>$p) { $column[]=$c; //    $values[]="?"; //   prepare statement $params[]=$p; //    } $sql=" INSERT INTO `".$table."` (".implode(",",$column).") VALUES (".implode(',',$values).")"; if ($debug) { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); }

PrepareDeleteSQLメソッド

レコードを削除する要求。 WHERE述部にはテーブル名と一連のパラメーターを使用します。

  protected function prepareDeleteSQL($table,$where,$debug=false) { $where=$this->checkWhere($where); $sql="DELETE FROM `".$table."` ".$where['column']; $params=$this->checkParams($what,$where['params']); if ($debug) { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); }

PrepareUpdateSQLメソッド

テーブル内のレコードを更新するためのSQL要求を生成します。

  protected function prepareUpdateSQL($table,$what,$where,$debug=false) { $what=$this->checkWhat($what); $where=$this->checkWhere($where); $sql="UPDATE `".$table."` SET ".$what['column']." ".$where['column']; $params=$this->checkParams($what['params'],$where['params']); if ($debug) { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); }

データベースへの接続とDML SQLクエリの生成を担当するDataBaseクラスについては上記で説明しました。以下は、このクラスの完全なコードです。

抽象クラスのデータベース

 <? abstract class DataBase { static private $_db=NULL; public $sql=''; public $params=array(); /* *  __construct  __clone  , *        new. * */ private function __construct() { return false; } private function __clone() { return false; } //  abstract public function getRecords($what='*',$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false); //  abstract function addRecord($data=array(),$debug=false); // (-) abstract function deleteRecords($table, $where=NULL,$debug=false); // (-) abstract function setRecords($table,$what,$where,$debug=false); //  abstract function query($sql); /* *     .    , *     ,  , *       . *     PDOChild * */ public static function getInstance($registry) { if (is_null(self::$_db)) { self::$_db=new PDOchild($registry); } return self::$_db; } /* *  join  . * type -   join * tables -      * pseudoName -    * row -      * */ protected function addJoin($type=' INNER ',$tables,$pseudoName,$rows) { if ($type!=='' && is_array($tables) && is_array($rows)) { $t0=$tables[0]; $t1=$tables[1]; if (is_array($pseudoName) && count($pseudoName)>0) { $t0=$pseudoName[0]; $t1=$pseudoName[1]; } return $type." JOIN `".$tables[1]."` `".$pseudoName[1]."` ON `".$t0."`.`".$rows[0]."`=`".$t1."`.`".$rows[1]."`"; } else { return false; } } /* *   join   * join -   join array(join,join) * */ protected function addJoinArray($join) { if (is_array($join)) { foreach ($join as $j) { $res[]=$this->addJoin($j[0],$j[1],$j[2],$j[3]); } } return $res; } /* *  SELECT sql * what-        * where-      array(=>array(=,)) * limit-     array( , ) * order-  array (=>) * join-  join * debug-  true     sql   sql     params   * */ protected function prepareSelectSQL($what=array('*'),$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false) { $what=$this->checkWhat($what); $where=$this->checkWhere($where); $limit=$this->checkLimit($limit); $order=$this->checkOrder($order); $j=$this->checkJoin($join); $sql="SELECT ".$what['column']." FROM `".$this->table."` `tb` ".$j." ".$where['column']." ".$order." ".$limit; $params=$this->checkParams($what['params'],$where['params']); if ($debug) { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); } /* *  Insert sql * data-   -   * table-     * debug-  true     sql   sql     params   * */ protected function prepareInsertSQL($data,$table,$debug=false) { foreach ($data as $c=>$p) { $column[]=$c; $values[]="?"; $params[]=$p; } $sql=" INSERT INTO `".$table."` (".implode(",",$column).") VALUES (".implode(',',$values).")"; if ($debug) { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); } /* *  Delete sql * where-    * table-      * debug-  true     sql   sql     params   * */ protected function prepareDeleteSQL($table,$where,$debug=false) { $where=$this->checkWhere($where); $sql="DELETE FROM `".$table."` ".$where['column']; $params=$this->checkParams($what,$where['params']); if ($debug) { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); } /* *  Update sql * table-      * what -      * where-    * debug-  true     sql   sql     params   */ protected function prepareUpdateSQL($table,$what,$where,$debug=false) { $what=$this->checkWhat($what); $where=$this->checkWhere($where); $sql="UPDATE `".$table."` SET ".$what['column']." ".$where['column']; $params=$this->checkParams($what['params'],$where['params']); if ($debug) { $this->sql=$sql; $this->params=$params; } return array('sql'=>$sql,'params'=>$params); } /* *    join *   ,      ,    addJoin *  ,  addJoinArray *  join ,     * */ protected function checkJoin($join) { if (is_array($join) && count($join)>0) { if (!is_array($join[0])) { $res[]=$this->addJoin($join[0],$join[1],$join[2],$join[3]); } else { $res=$this->addJoinArray($join); } return implode(" ",$res); } else { return false; } } /* *    what *    . , *    =>?     prepare SQL * */ protected function checkWhat($what) { if (is_array($what)) { foreach ($what as $k=>$v) { if (!is_numeric($k)) { $result['column'][]=$k."=?"; $result['params'][]=$v; } else { $result['column'][]=$v; } } $result['column']=implode(",",$result['column']); } return $result; } /* *    Where *     , *    =>?     prepare SQL *  v[0](sign)= IN   value  ,    IN (array); *     LIKE,   . *       sql * */ protected function checkWhere($where) { if (!is_null($where) && is_array($where)) { foreach ($where as $k=>$v) { $part=$k.$v[0]; if (!is_array($v[1])) { $part.="?"; $params[]=$v[1]; } else { $part.="(".implode(",",$v[1]).")"; } $res[]=$part; } $result['column']="WHERE ".implode(" AND ",$res); $result['params']=$params; } return $result; } /* *    Limit *     , *   LIMIT  SQL *   LIMIT        * */ protected function checkLimit($limit) { if (is_array($limit) && count($limit)>0) { $res=" LIMIT ".$limit['start']; if (isset($limit['count']) && $limit['count']!=='') { $res.=", ".$limit['count']; } } return $res; } /* *    Order *     , *   ORDER  SQL *   ORDER * */ protected function checkOrder($order) { if (is_array($order) && count($order)>0) { foreach ($order as $row=>$dir) { $res[]=$row." ".$dir; } return "ORDER BY ".implode(",",$res); } else { return ''; } } /* *     prepare sql *      WHAT    WHERE. *    ,  prepare sql *    update, select, delete, insert *    what  where * */ protected function checkParams($what,$where) { if (!isset($what) || !is_array($what)) { $params=$where; } else if (!isset($where) && !is_array($where)) { $params=$what; } else { $params=array_merge($what,$where); } return $params; } } ?>

ここで、Newsモデルクラスについて説明します。このクラスは、親DataBaseクラスのすべての抽象メソッドと静的getObjectメソッドを実装します。このメソッドは、このクラスのオブジェクトのインスタンスを返します。このメソッドは、newキーワードを使用してNewsクラスのオブジェクトを作成する必要をなくすために作成されました。これは次のようなものです。

 $news=News::getObject()->getRecords(params);

このクラスの各メソッドは、必要なsqlクエリジェネレーターを呼び出し、クエリを実行するために最終的なクエリとパラメーターをPDOに渡します。以下は、Newsモデルの完全なクラスコードです。

クラスニュース

 <? class News extends DataBase { public $table='news'; //  public $id_row='id'; // primary key public function __construct() { $registry=new Registry(); //     $this->db=parent::getInstance($registry); } //        public static function getObject() { return new News(); } //    . public function getRecords($what=array('*'),$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false) { $data=$this->prepareSelectSQL($what,$where, $limit, $order,$join,$debug); $res=$this->db->prepare($data['sql']); $res->execute($data['params']); $result=$query->fetchAll(PDO::FETCH_OBJ); return $result; } public function addRecord($data=array(),$debug=false) { $data=$this->prepareInsertSQL($data,$this->table,$debug); $query=$this->db->prepare($data['sql']); return $query->execute($data['params'])); } public function deleteRecords($table, $where=NULL,$debug=false) { $data=$this->prepareDeleteSQL($table,$where,$debug); $query=$this->db->prepare($data['sql']); $result=$query->execute($data['params']); return $result; } public function setRecords($table,$what,$where,$debug=false) { $data=$this->prepareUpdateSQL($table,$what,$where,$debug); $query=$this->db->prepare($data['sql']); $result=$query->execute($data['params']); return $result; } public function query($sql) { $query=$this->db->prepare($sql); $query->execute(); $result=$query->fetchAll(PDO::FETCH_OBJ); return $result; } } ?>

原則として、これは完了することができます。もちろん、GROUP BYおよびHAVING述語の生成を追加することもできますが、私はそうしないことにしました。私が明確に述べたクエリを構築する原理は、使用に問題はないと思います。その結果、SQLクエリを構築するメカニズムが得られました。これは、データベース内の特定のテーブル構造に関連付けられておらず、さまざまなタイプのDML SQLクエリに適用できます。必要に応じて、githubにリポジトリを作成できます。

メソッドを改善するための批判と提案を聞いてうれしいです。

PHP SQLクエリジェネレーター