mysql构造器_14. 查询构造器 (query builder)

## 创建core/query/QueryBuilder.php

```

namespace core\database\query;

use core\database\connection\Connection;

class QueryBuilder

{

protected $connection;

protected $grammar;

public $binds;

public $columns;

public $distinct;

public $form;

public $union;

public $bindings = [

'select' => [],

'from' => [],

'join' => [],

'where' => [],

'groupBy' => [],

'having' => [],

'order' => [],

'union' => [],

'unionOrder' => [],

];

protected $operators = [

'=','','<=','>=','<>','!=','<=>','like','like binary','not like','ilike','&','|','^',

'<>','rlike','not rlike','regexp','not regexp','~','~*','!~','!~*','similar to','not similar to',

'not ilike','~~*','!~~*'

];

public function __construct(Connection $connection, $grammar)

{

$this->connection = $connection; // 数据库连接

$this->grammar = $grammar; // 编译成sql的类

}

public function table(string $table,$as = null)

{

return $this->from($table,$as);

// return (clone $this)->from($table,$as);

}

public function from($table,$as)

{

$this->from = $as ? "{$table} as {$as}" : $table;

return $this;

}

public function get($columns = ['*'])

{

if(! is_array($columns))

$columns = func_get_args();

$this->columns = $columns;

$sql = $this->toSql();

return $this->runSql($sql);

}

// 运行sql

public function runSql($sql)

{

return $this->connection->select(

$sql,$this->getBinds()

);

}

public function where($column, $operator = null, $value = null, $joiner = 'and')

{

if ( is_array($column)) // 如果是 where(['id' => '2','name' => 'xxh']) 这种

foreach ($column as $col => $value)

$this->where($col,'=',$value);

if(! in_array($operator,$this->operators)){ // 操作符不存在

$value = $operator;

$operator = '=';

}

$type = 'Basic';

$this->wheres[] = compact(

'type', 'column', 'operator', 'value', 'joiner'

); // 存到wheres变量

$this->binds[] = $value;

return $this;

}

public function orWhere($column, $operator = null, $value = null)

{

return $this->where($column, $operator, $value ,'or');

}

public function find($id,$columns = ['*'],$key = 'id')

{

return $this->where($key,$id)->get($columns);

}

public function whereLike($column, $operator = null, $value = null)

{

return $this->where($column, $operator, $value, 'like');

}

public function toSql() // 编译成sql

{

return $this->grammar->compileSql($this);

}

public function getBinds() // 绑定

{

return $this->binds;

}

}

```

## 创建core/query/MysqlGrammar

```

namespace core\database\query;

class MysqlGrammar

{

protected $selectComponents = [

'columns',

'from',

'joins',

'wheres',

'groups',

'havings',

'orders',

'limit',

'offset',

'lock',

];

public function compileSql(QueryBuilder $query)

{

$sql = [];

foreach ($this->selectComponents as $component)

if( isset( $query->{$component}))

$sql[$component] = $this->$component($query, $query->$component);

return implode($sql);

}

protected function columns(QueryBuilder $query,$columns)

{

if(! $columns)

$columns = ['*'];

$select = 'select ';

if ($query->distinct)

$select = 'select distinct ';

return $select . implode(',',$columns);

}

protected function from(QueryBuilder $query,$form)

{

return ' from '.$form;

}

protected function joins()

{

}

protected function wheres(QueryBuilder $queryBuilder,$wheres)

{

if(! $wheres)

return '';

$where_arrs = [];

foreach ($wheres as $index => $where){

if(! $index)

$where['joiner'] = ' where';

$where_arrs[] = sprintf(' %s `%s` %s ?',$where['joiner'], $where['column'], $where['operator']);

}

return implode($where_arrs);

}

protected function groups()

{

}

protected function havings()

{

}

protected function orders()

{

}

protected function limit()

{

}

protected function offset()

{

}

protected function lock()

{

}

}

```

## 编辑core\database\connection\MysqlConnection.php

添加以下代码

```

// 调用不存在的方法 调用一个新的查询构造器

public function __call($method, $parameters)

{

// 返回QueryBuilder类

return $this->newBuilder()->$method(...$parameters);

}

// 创建新的查询器

public function newBuilder()

{

return new QueryBuilder($this, new MysqlGrammar());

}

````


## 运行