SQL查询构建器：simple-query
jopen
9年前
SQL查询构建器 - 简单，但强大。
See on examples:
Simple SELECT query
$model = \Simple\Model(['table'=>'superA', 'alias'=>'A']); $query = (new \Simple\Query($model))->where($model->field('username'), 'superUser'); echo $query->sqlSelect(); // SELECT A.* FROM superA AS A WHERE A.username = (?) # use 'bindParameters' propriety to bind parameters in your database statement $query->bindParameters # ['superUser'] # more real example $supossed_mysql_stmt = $con->prepare($query->sqlSelect()); # you can bind_param this way foreach ($query->bindParameters as $value) { $supossed_mysql_stmt->bind_param( $query->type($value),#return 'i', 'd' or 's' $value ); } ...Why we not build onw string sql?
Using SQL Builder you can:
- create powerful query without specific order. - use bind values - create easy subquery conditions using query object - apply patterns to interprete request and build personal queries - not dependent of ORMs
About Model
Model allow to separate table structures to avoid conflicts when you want to create more complex queries.
Constructor
$model = new \Simple\Model([ 'table' => 'nameOfTable', 'alias' => 'shortName', 'pk' => 'id' ## default value ]);
pk()
Primary key of model
#example $model = new \Simple\Model([ 'table'=>'superA', 'alias'=>'A' ]); echo $model->pk(); # A.id
fk(\Simple\Model $modelB)
How key from model $modelB are represented inside of Model like a foreing key. Useful in joins
#example $modelA = new \Simple\Model([ 'table'=>'superA', 'alias'=>'A' ]); $modelA = new \Simple\Model([ 'table'=>'megaB', 'alias'=>'B' ]); echo $modelA->fk($modelB); # A.idMegaB
field($fieldName)
Useful method return anti-collision field name
$modelA = new \Simple\Model([ 'table'=>'superA', 'alias'=>'A' ]); echo $model->field('name'); # A.name table()
Return table name
alias()
return alias table
Now We Can Build Queries!!
namespace My\Model; class User extends \Simple\Model { protected $table = 'user'; protected $alias = 'us'; } $userModel = new \My\Model\User(); $query = new \Simple\Query($userModel); $query->where($userModel->field('username'), $usernameParameter); $stmt = $mysql_con->prepare($query->sqlSelect()); ....