[TOC]
* * * * *
# 1 查询对象
>>查询对象(Query)实现基本的查询操作
>与模型不同的是，查询对象是在数据库的整体上进行操作，所以需要指定数据库表
>模型针对的数据库中的某个数据表的操作。
# 2 查询操作
## 2-1 增删改查
### $query->insert()
~~~
public function insert(array $data, $replace = false, $getLastInsID = false, $sequence = null)
{
// 分析查询表达式
$options = $this->parseExpress();
// 生成SQL语句
$sql = $this->builder()->insert($data, $options, $replace);
// 获取参数绑定
$bind = $this->getBind();
if ($options['fetch_sql']) {
// 获取实际执行的SQL语句
return $this->connection->getRealSql($sql, $bind);
}
// 执行操作
$result = $this->execute($sql, $bind);
if ($getLastInsID) {
$sequence = $sequence ?: (isset($options['sequence']) ? $options['sequence'] : null);
return $this->getLastInsID($sequence);
}
return $result;
}
~~~
### $query->insertAll()
~~~
public function insertAll(array $dataSet)
{
// 分析查询表达式
$options = $this->parseExpress();
if (!is_array(reset($dataSet))) {
return false;
}
// 生成SQL语句
$sql = $this->builder()->insertAll($dataSet, $options);
// 获取参数绑定
$bind = $this->getBind();
if ($options['fetch_sql']) {
// 获取实际执行的SQL语句
return $this->connection->getRealSql($sql, $bind);
} else {
// 执行操作
return $this->execute($sql, $bind);
}
}
~~~
### $query->selectInsert()
~~~
public function selectInsert($fields, $table)
{
// 分析查询表达式
$options = $this->parseExpress();
// 生成SQL语句
$table = $this->parseSqlTable($table);
$sql = $this->builder()->selectInsert($fields, $table, $options);
// 获取参数绑定
$bind = $this->getBind();
if ($options['fetch_sql']) {
// 获取实际执行的SQL语句
return $this->connection->getRealSql($sql, $bind);
} else {
// 执行操作
return $this->execute($sql, $bind);
}
}
~~~
### $query->select()
~~~
public function select($data = null)
{
if ($data instanceof Query) {
return $data->select();
} elseif ($data instanceof \Closure) {
call_user_func_array($data, [ & $this]);
$data = null;
}
// 分析查询表达式
$options = $this->parseExpress();
if (false === $data) {
// 用于子查询 不查询只返回SQL
$options['fetch_sql'] = true;
} elseif (!is_null($data)) {
// 主键条件分析
$this->parsePkWhere($data, $options);
}
$resultSet = false;
if (empty($options['fetch_sql']) && !empty($options['cache'])) {
// 判断查询缓存
$cache = $options['cache'];
unset($options['cache']);
$key = is_string($cache['key']) ? $cache['key'] : md5(serialize($options));
$resultSet = Cache::get($key);
}
if (!$resultSet) {
// 生成查询SQL
$sql = $this->builder()->select($options);
// 获取参数绑定
$bind = $this->getBind();
if ($options['fetch_sql']) {
// 获取实际执行的SQL语句
return $this->connection->getRealSql($sql, $bind);
}
// 执行查询操作
$resultSet = $this->query($sql, $bind, $options['master'], $options['fetch_class']);
if ($resultSet instanceof \PDOStatement) {
// 返回PDOStatement对象
return $resultSet;
}
if (isset($cache)) {
// 缓存数据集
if (isset($cache['tag'])) {
Cache::tag($cache['tag'])->set($key, $resultSet, $cache['expire']);
} else {
Cache::set($key, $resultSet, $cache['expire']);
}
}
}
// 返回结果处理
if (count($resultSet) > 0) {
// 数据列表读取后的处理
if (!empty($this->model)) {
// 生成模型对象
$model = $this->model;
foreach ($resultSet as $key => $result) {
/** @var Model $result */
$result = new $model($result);
$result->isUpdate(true);
// 关联查询
if (!empty($options['relation'])) {
$result->relationQuery($options['relation']);
}
$resultSet[$key] = $result;
}
if (!empty($options['with']) && $result instanceof Model) {
// 预载入
$resultSet = $result->eagerlyResultSet($resultSet, $options['with'], is_object($resultSet) ? get_class($resultSet) : '');
}
}
} elseif (!empty($options['fail'])) {
$this->throwNotFound($options);
}
return $resultSet;
}
~~~
### $query->find()
~~~
public function find($data = null)
{
if ($data instanceof Query) {
return $data->find();
} elseif ($data instanceof \Closure) {
call_user_func_array($data, [ & $this]);
$data = null;
}
// 分析查询表达式
$options = $this->parseExpress();
if (!is_null($data)) {
// AR模式分析主键条件
$this->parsePkWhere($data, $options);
}
$options['limit'] = 1;
$result = false;
if (empty($options['fetch_sql']) && !empty($options['cache'])) {
// 判断查询缓存
$cache = $options['cache'];
if (true === $cache['key'] && !is_null($data) && !is_array($data)) {
$key = 'think:' . $options['table'] . '|' . $data;
} else {
$key = is_string($cache['key']) ? $cache['key'] : md5(serialize($options));
}
$result = Cache::get($key);
}
if (!$result) {
// 生成查询SQL
$sql = $this->builder()->select($options);
// 获取参数绑定
$bind = $this->getBind();
if ($options['fetch_sql']) {
// 获取实际执行的SQL语句
return $this->connection->getRealSql($sql, $bind);
}
// 执行查询
$result = $this->query($sql, $bind, $options['master'], $options['fetch_class']);
if ($result instanceof \PDOStatement) {
// 返回PDOStatement对象
return $result;
}
if (isset($cache)) {
// 缓存数据
if (isset($cache['tag'])) {
Cache::tag($cache['tag'])->set($key, $result, $cache['expire']);
} else {
Cache::set($key, $result, $cache['expire']);
}
}
}
// 数据处理
if (!empty($result[0])) {
$data = $result[0];
if (!empty($this->model)) {
// 返回模型对象
$model = $this->model;
$data = new $model($data);
$data->isUpdate(true, isset($options['where']['AND']) ? $options['where']['AND'] : null);
if ($this->allowField) {
$data->allowField($this->allowField);
}
// 关联查询
if (!empty($options['relation'])) {
$data->relationQuery($options['relation']);
}
if (!empty($options['with'])) {
// 预载入
$data->eagerlyResult($data, $options['with'], is_object($result) ? get_class($result) : '');
}
}
} elseif (!empty($options['fail'])) {
$this->throwNotFound($options);
} else {
$data = null;
}
return $data;
}
~~~
### $query->update()
~~~
public function update(array $data)
{
$options = $this->parseExpress();
$pk = $this->getPk($options);
if (isset($options['cache']) && is_string($options['cache'])) {
$key = $options['cache'];
}
if (empty($options['where'])) {
// 如果存在主键数据 则自动作为更新条件
if (is_string($pk) && isset($data[$pk])) {
$where[$pk] = $data[$pk];
if (!isset($key)) {
$key = 'think:' . $options['table'] . '|' . $data[$pk];
}
unset($data[$pk]);
} elseif (is_array($pk)) {
// 增加复合主键支持
foreach ($pk as $field) {
if (isset($data[$field])) {
$where[$field] = $data[$field];
} else {
// 如果缺少复合主键数据则不执行
throw new Exception('miss complex primary data');
}
unset($data[$field]);
}
}
if (!isset($where)) {
// 如果没有任何更新条件则不执行
throw new Exception('miss update condition');
} else {
$options['where']['AND'] = $where;
}
} elseif (is_string($pk) && isset($options['where']['AND'][$pk]) && is_scalar($options['where']['AND'][$pk])) {
$key = 'think:' . $options['table'] . '|' . $options['where']['AND'][$pk];
}
// 生成UPDATE SQL语句
$sql = $this->builder()->update($data, $options);
// 获取参数绑定
$bind = $this->getBind();
if ($options['fetch_sql']) {
// 获取实际执行的SQL语句
return $this->connection->getRealSql($sql, $bind);
} else {
// 检测缓存
if (isset($key) && Cache::get($key)) {
// 删除缓存
Cache::rm($key);
}
// 执行操作
return '' == $sql ? 0 : $this->execute($sql, $bind);
}
}
~~~
### $query->delete()
~~~
public function delete($data = null)
{
// 分析查询表达式
$options = $this->parseExpress();
if (isset($options['cache']) && is_string($options['cache'])) {
$key = $options['cache'];
}
if (!is_null($data) && true !== $data) {
if (!isset($key) && !is_array($data)) {
// 缓存标识
$key = 'think:' . $options['table'] . '|' . $data;
}
// AR模式分析主键条件
$this->parsePkWhere($data, $options);
}
if (true !== $data && empty($options['where'])) {
// 如果条件为空 不进行删除操作 除非设置 1=1
throw new Exception('delete without condition');
}
// 生成删除SQL语句
$sql = $this->builder()->delete($options);
// 获取参数绑定
$bind = $this->getBind();
if ($options['fetch_sql']) {
// 获取实际执行的SQL语句
return $this->connection->getRealSql($sql, $bind);
}
// 检测缓存
if (isset($key) && Cache::get($key)) {
// 删除缓存
Cache::rm($key);
}
// 执行操作
return $this->execute($sql, $bind);
}
~~~
## 2-2 事务操作
### $query->startTrans()
>> 启动事务
~~~
public function startTrans()
{
$this->connection->startTrans();
}
~~~
### $query->commit()
>> 提交事务
~~~
public function commit()
{
$this->connection->commit();
}
~~~
### $query->transaction()
>>执行事务
~~~
public function transaction($callback)
{
return $this->connection->transaction($callback);
}
~~~
### $query->rollback()
>>回滚事务
~~~
public function rollback()
{
$this->connection->rollback();
}
~~~
## 2-3 sql语句操作
### $query->query()
>>调用连接器的静态query方法
~~~
public function query($sql, $bind = [], $master = false, $class = false)
{
return $this->connection->query($sql, $bind, $master, $class);
}
~~~
### $query->execute()
>>调用连接器的静态query方法
~~~
public function execute($sql, $bind = [])
{
return $this->connection->execute($sql, $bind);
}
~~~
## 2-4 聚合操作
### count()
~~~
public function count($field = '*')
{
return $this->value('COUNT(' . $field . ') AS tp_count', 0);
}
~~~
### max()
~~~
public function max($field = '*')
{
return $this->value('MAX(' . $field . ') AS tp_max', 0);
}
~~~
### min()
~~~
public function min($field = '*')
{
return $this->value('MIN(' . $field . ') AS tp_min', 0);
}
~~~
### avg()
~~~
public function avg($field = '*')
{
return $this->value('AVG(' . $field . ') AS tp_avg', 0);
}
~~~
### sum()
~~~
public function sum($field = '*')
{
return $this->value('SUM(' . $field . ') AS tp_sum', 0);
}
~~~
### $query->value()
~~~
public function value($field, $default = null)
{
$result = null;
if (!empty($this->options['cache'])) {
// 判断查询缓存
$cache = $this->options['cache'];
if (empty($this->options['table'])) {
$this->options['table'] = $this->getTable();
}
$key = is_string($cache['key']) ? $cache['key'] : md5($field . serialize($this->options));
$result = Cache::get($key);
}
if (!$result) {
if (isset($this->options['field'])) {
unset($this->options['field']);
}
$pdo = $this->field($field)->fetchPdo(true)->find();
if (is_string($pdo)) {
// 返回SQL语句
return $pdo;
}
$result = $pdo->fetchColumn();
if (isset($cache)) {
// 缓存数据
if (isset($cache['tag'])) {
Cache::tag($cache['tag'])->set($key, $result, $cache['expire']);
} else {
Cache::set($key, $result, $cache['expire']);
}
}
} else {
// 清空查询条件
$this->options = [];
}
return !is_null($result) ? $result : $default;
}
~~~
### $query->column()
~~~
public function column($field, $key = '')
{
$result = false;
if (!empty($this->options['cache'])) {
// 判断查询缓存
$cache = $this->options['cache'];
if (empty($this->options['table'])) {
$this->options['table'] = $this->getTable();
}
$guid = is_string($cache['key']) ? $cache['key'] : md5($field . serialize($this->options));
$result = Cache::get($guid);
}
if (!$result) {
if (isset($this->options['field'])) {
unset($this->options['field']);
}
if ($key && '*' != $field) {
$field = $key . ',' . $field;
}
$pdo = $this->field($field)->fetchPdo(true)->select();
if (is_string($pdo)) {
// 返回SQL语句
return $pdo;
}
if (1 == $pdo->columnCount()) {
$result = $pdo->fetchAll(PDO::FETCH_COLUMN);
} else {
$resultSet = $pdo->fetchAll(PDO::FETCH_ASSOC);
if ($resultSet) {
$fields = array_keys($resultSet[0]);
$count = count($fields);
$key1 = array_shift($fields);
$key2 = $fields ? array_shift($fields) : '';
$key = $key ?: $key1;
foreach ($resultSet as $val) {
if ($count > 2) {
$result[$val[$key]] = $val;
} elseif (2 == $count) {
$result[$val[$key]] = $val[$key2];
} elseif (1 == $count) {
$result[$val[$key]] = $val[$key1];
}
}
} else {
$result = [];
}
}
if (isset($cache) && isset($guid)) {
// 缓存数据
if (isset($cache['tag'])) {
Cache::tag($cache['tag'])->set($guid, $result, $cache['expire']);
} else {
Cache::set($guid, $result, $cache['expire']);
}
}
} else {
// 清空查询条件
$this->options = [];
}
return $result;
}
~~~
## 2-5 链式操作
### table
~~~
public function table($table)
{
$this->options['table'] = $table;
return $this;
}
~~~
### where
~~~
public function where($field, $op = null, $condition = null)
{
$param = func_get_args();
array_shift($param);
$this->parseWhereExp('AND', $field, $op, $condition, $param);
return $this;
}
~~~
### whereOr
~~~
public function whereOr($field, $op = null, $condition = null)
{
$param = func_get_args();
array_shift($param);
$this->parseWhereExp('OR', $field, $op, $condition, $param);
return $this;
}
~~~
### whereXor
~~~
public function whereXor($field, $op = null, $condition = null)
{
$param = func_get_args();
array_shift($param);
$this->parseWhereExp('XOR', $field, $op, $condition, $param);
return $this;
}
~~~
### whereTime()
~~~
public function whereTime($field, $op, $range = null)
{
if (is_null($range)) {
// 使用日期表达式
$date = getdate();
switch (strtolower($op)) {
case 'today':
case 'd':
$range = 'today';
break;
case 'week':
case 'w':
$range = 'this week 00:00:00';
break;
case 'month':
case 'm':
$range = mktime(0, 0, 0, $date['mon'], 1, $date['year']);
break;
case 'year':
case 'y':
$range = mktime(0, 0, 0, 1, 1, $date['year']);
break;
case 'yesterday':
$range = ['yesterday', 'today'];
break;
case 'last week':
$range = ['last week 00:00:00', 'this week 00:00:00'];
break;
case 'last month':
$range = [date('y-m-01', strtotime('-1 month')), mktime(0, 0, 0, $date['mon'], 1, $date['year'])];
break;
case 'last year':
$range = [mktime(0, 0, 0, 1, 1, $date['year'] - 1), mktime(0, 0, 0, 1, 1, $date['year'])];
break;
}
$op = is_array($range) ? 'between' : '>';
}
$this->where($field, strtolower($op) . ' time', $range);
return $this;
}
~~~
### field()
~~~
public function field($field, $except = false, $tableName = '', $prefix = '', $alias = '')
{
if (empty($field)) {
return $this;
}
if (is_string($field)) {
$field = array_map('trim', explode(',', $field));
}
if (true === $field) {
// 获取全部字段
$fields = !empty($this->allowField) && ('' == $tableName || $this->getTable() == $tableName) ? $this->allowField : $this->getTableInfo($tableName ?: (isset($this->options['table']) ? $this->options['table'] : ''), 'fields');
$field = $fields ?: ['*'];
} elseif ($except) {
// 字段排除
$fields = !empty($this->allowField) && ('' == $tableName || $this->getTable() == $tableName) ? $this->allowField : $this->getTableInfo($tableName ?: (isset($this->options['table']) ? $this->options['table'] : ''), 'fields');
$field = $fields ? array_diff($fields, $field) : $field;
}
if ($tableName) {
// 添加统一的前缀
$prefix = $prefix ?: $tableName;
foreach ($field as $key => $val) {
if (is_numeric($key)) {
$val = $prefix . '.' . $val . ($alias ? ' AS ' . $alias . $val : '');
}
$field[$key] = $val;
}
}
if (isset($this->options['field'])) {
$field = array_merge($this->options['field'], $field);
}
$this->options['field'] = array_unique($field);
return $this;
}
~~~
### limit()
~~~
public function limit($offset, $length = null)
{
if (is_null($length) && strpos($offset, ',')) {
list($offset, $length) = explode(',', $offset);
}
$this->options['limit'] = intval($offset) . ($length ? ',' . intval($length) : '');
return $this;
}
~~~
### order()
~~~
public function order($field, $order = null)
{
if (!empty($field)) {
if (is_string($field)) {
if (!empty($this->options['via'])) {
$field = $this->options['via'] . '.' . $field;
}
$field = empty($order) ? $field : [$field => $order];
} elseif (!empty($this->options['via'])) {
foreach ($field as $key => $val) {
if (is_numeric($key)) {
$field[$key] = $this->options['via'] . '.' . $val;
} else {
$field[$this->options['via'] . '.' . $key] = $val;
unset($field[$key]);
}
}
}
$this->options['order'] = $field;
}
return $this;
}
~~~
### group()
~~~
public function group($group)
{
$this->options['group'] = $group;
return $this;
}
~~~
### having()
~~~
public function having($having)
{
$this->options['having'] = $having;
return $this;
}
~~~
### join()
~~~
public function join($join, $condition = null, $type = 'INNER')
{
if (empty($condition)) {
// 如果为组数，则循环调用join
foreach ($join as $key => $value) {
if (is_array($value) && 2 <= count($value)) {
$this->join($value[0], $value[1], isset($value[2]) ? $value[2] : $type);
}
}
} else {
$prefix = $this->prefix;
// 传入的表名为数组
if (is_array($join)) {
if (0 !== $key = key($join)) {
// 设置了键名则键名为表名，键值作为表的别名
$table = $key . ' ' . array_shift($join);
} else {
$table = array_shift($join);
}
if (count($join)) {
// 有设置第二个元素则把第二元素作为表前缀
$table = (string) current($join) . $table;
} elseif (false === strpos($table, '.')) {
// 加上默认的表前缀
$table = $prefix . $table;
}
} else {
$join = trim($join);
if (0 === strpos($join, '__')) {
$table = $this->parseSqlTable($join);
} elseif (false === strpos($join, '(') && false === strpos($join, '.') && !empty($prefix) && 0 !== strpos($join, $prefix)) {
// 传入的表名中不带有'('并且不以默认的表前缀开头时加上默认的表前缀
$table = $prefix . $join;
} else {
$table = $join;
}
}
if (is_array($condition)) {
$condition = implode(' AND ', $condition);
}
$this->options['join'][] = strtoupper($type) . ' JOIN ' . $table . ' ON ' . $condition;
}
return $this;
}
~~~
### union()
~~~
public function union($union, $all = false)
{
$this->options['union']['type'] = $all ? 'UNION ALL' : 'UNION';
if (is_array($union)) {
$this->options['union'] = array_merge($this->options['union'], $union);
} else {
$this->options['union'][] = $union;
}
return $this;
}
~~~
### distinct()
~~~
public function distinct($distinct)
{
$this->options['distinct'] = $distinct;
return $this;
}
~~~
### lock()
~~~
public function lock($lock = false)
{
$this->options['lock'] = $lock;
$this->options['master'] = true;
return $this;
}
~~~
### cache()
~~~
public function cache($key = true, $expire = null, $tag = null)
{
// 增加快捷调用方式 cache(10) 等同于 cache(true, 10)
if (is_numeric($key) && is_null($expire)) {
$expire = $key;
$key = true;
}
if (false !== $key) {
$this->options['cache'] = ['key' => $key, 'expire' => $expire, 'tag' => $tag];
}
return $this;
}
~~~
### force()
~~~
public function force($force)
{
$this->options['force'] = $force;
return $this;
}
~~~
### comment()
~~~
public function comment($comment)
{
$this->options['comment'] = $comment;
return $this;
}
~~~
### alais()
~~~
public function alias($alias)
{
$this->options['alias'] = $alias;
return $this;
}
~~~
### fetchSql()
~~~
public function fetchSql($fetch = true)
{
$this->options['fetch_sql'] = $fetch;
return $this;
}
~~~
### fetchPdo()
~~~
public function fetchPdo($pdo = true)
{
$this->options['fetch_class'] = $pdo;
return $this;
}
~~~
### fetchClass()
~~~
public function fetchClass($class)
{
$this->options['fetch_class'] = $class;
return $this;
}
~~~
### with()
~~~
public function with($with)
{
if (empty($with)) {
return $this;
}
if (is_string($with)) {
$with = explode(',', $with);
}
$i = 0;
$currentModel = $this->model;
/** @var Model $class */
$class = new $currentModel;
foreach ($with as $key => $relation) {
$closure = false;
if ($relation instanceof \Closure) {
// 支持闭包查询过滤关联条件
$closure = $relation;
$relation = $key;
$with[$key] = $key;
} elseif (is_string($relation) && strpos($relation, '.')) {
$with[$key] = $relation;
list($relation, $subRelation) = explode('.', $relation, 2);
}
/** @var Relation $model */
$model = $class->$relation();
$info = $model->getRelationInfo();
if (in_array($info['type'], [Relation::HAS_ONE, Relation::BELONGS_TO])) {
if (0 == $i) {
$name = Loader::parseName(basename(str_replace('\\', '/', $currentModel)));
$table = $this->getTable();
$alias = isset($info['alias'][$name]) ? $info['alias'][$name] : $name;
$this->table($table)->alias($alias);
if (isset($this->options['field'])) {
$field = $this->options['field'];
unset($this->options['field']);
} else {
$field = true;
}
$this->field($field, false, $table, $alias);
}
// 预载入封装
$joinTable = $model->getTable();
$joinName = Loader::parseName(basename(str_replace('\\', '/', $info['model'])));
$joinAlias = isset($info['alias'][$joinName]) ? $info['alias'][$joinName] : $joinName;
$this->via($joinAlias);
if (Relation::HAS_ONE == $info['type']) {
$this->join($joinTable . ' ' . $joinAlias, $alias . '.' . $info['localKey'] . '=' . $joinAlias . '.' . $info['foreignKey'], $info['joinType']);
} else {
$this->join($joinTable . ' ' . $joinAlias, $alias . '.' . $info['foreignKey'] . '=' . $joinAlias . '.' . $info['localKey'], $info['joinType']);
}
if ($closure) {
// 执行闭包查询
call_user_func_array($closure, [ & $this]);
//指定获取关联的字段
//需要在 回调中 调方法 withField 方法，如
// $query->where(['id'=>1])->withField('id,name');
if (!empty($this->options['with_field'])) {
$field = $this->options['with_field'];
unset($this->options['with_field']);
}
}
$this->field($field, false, $joinTable, $joinAlias, $relation . '__');
$i++;
} elseif ($closure) {
$with[$key] = $closure;
}
}
$this->via();
$this->options['with'] = $with;
return $this;
}
~~~
### withField()
~~~
public function withField($field)
{
$this->options['with_field'] = $field;
return $this;
}
~~~
### via()
~~~
public function via($via = '')
{
$this->options['via'] = $via;
return $this;
}
~~~
### relation()
~~~
public function relation($relation)
{
$this->options['relation'] = $relation;
return $this;
}
~~~
# 3 数据表操作
## 1 数据表信息
### $query->name()
~~~
public function name($name)
{
$this->name = $name;
return $this;
}
~~~
### $query->setTable()
~~~
public function setTable($table)
{
$this->table = $table;
return $this;
}
~~~
### $query->getTable()
~~~
public function getTable($name = '')
{
if ($name || empty($this->table)) {
$name = $name ?: $this->name;
$tableName = $this->prefix;
if ($name) {
$tableName .= Loader::parseName($name);
}
} else {
$tableName = $this->table;
}
return $tableName;
}
~~~
### $query->getTableInfo()
~~~
public function getTableInfo($tableName = '', $fetch = '')
{
if (!$tableName) {
$tableName = $this->getTable();
}
if (is_array($tableName)) {
$tableName = key($tableName) ?: current($tableName);
}
if (strpos($tableName, ',')) {
// 多表不获取字段信息
return false;
} else {
$tableName = $this->parseSqlTable($tableName);
}
list($guid) = explode(' ', $tableName);
if (!isset(self::$info[$guid])) {
$info = $this->connection->getFields($tableName);
$fields = array_keys($info);
$bind = $type = [];
foreach ($info as $key => $val) {
// 记录字段类型
$type[$key] = $val['type'];
$bind[$key] = $this->getFieldBindType($val['type']);
if (!empty($val['primary'])) {
$pk[] = $key;
}
}
if (isset($pk)) {
// 设置主键
$pk = count($pk) > 1 ? $pk : $pk[0];
} else {
$pk = null;
}
self::$info[$guid] = ['fields' => $fields, 'type' => $type, 'bind' => $bind, 'pk' => $pk];
}
return $fetch ? self::$info[$guid][$fetch] : self::$info[$guid];
}
~~~
### $query->getpk()
~~~
public function getPk($options = '')
{
if (!empty($this->pk)) {
$pk = $this->pk;
} else {
$pk = $this->getTableInfo(is_array($options) ? $options['table'] : $options, 'pk');
}
return $pk;
}
~~~
## 2 数据表字段信息
### $query->getTableFields()
~~~
public function getTableFields($options)
{
return !empty($this->allowField) ? $this->allowField : $this->getTableInfo($options['table'], 'fields');
}
~~~
### $query->getFieldsType()
~~~
public function getFieldsType($options)
{
return !empty($this->fieldType) ? $this->fieldType : $this->getTableInfo($options['table'], 'type');
}
~~~
## 3 数据表绑定信息
### $query->getFieldBind()
~~~
public function getFieldsBind($options)
{
$types = $this->getFieldsType($options);
$bind = [];
if ($types) {
foreach ($types as $key => $type) {
$bind[$key] = $this->getFieldBindType($type);
}
}
return $bind;
}
~~~
### $query->getFieldBindType()
~~~
protected function getFieldBindType($type)
{
if (preg_match('/(int|double|float|decimal|real|numeric|serial)/is', $type)) {
$bind = PDO::PARAM_INT;
} elseif (preg_match('/bool/is', $type)) {
$bind = PDO::PARAM_BOOL;
} else {
$bind = PDO::PARAM_STR;
}
return $bind;
}
~~~
# 4 sql解析合成语句操作
## 1 数据表解析
### $query->parseSqlTable()
~~~
public function parseSqlTable($sql)
{
if (false !== strpos($sql, '__')) {
$prefix = $this->prefix;
$sql = preg_replace_callback("/__([A-Z0-9_-]+)__/sU", function ($match) use ($prefix) {
return $prefix . strtolower($match[1]);
}, $sql);
}
return $sql;
}
~~~
## 2 where语句解析
### $query->parseWhereExp()
~~~
protected function parseWhereExp($logic, $field, $op, $condition, $param = [])
{
if ($field instanceof \Closure) {
$this->options['where'][$logic][] = is_string($op) ? [$op, $field] : $field;
return;
}
if (is_string($field) && !empty($this->options['via']) && !strpos($field, '.')) {
$field = $this->options['via'] . '.' . $field;
}
if (is_string($field) && preg_match('/[,=\>\<\'\"\(\s]/', $field)) {
$where[] = ['exp', $field];
if (is_array($op)) {
// 参数绑定
$this->bind($op);
}
} elseif (is_null($op) && is_null($condition)) {
if (is_array($field)) {
// 数组批量查询
$where = $field;
} elseif ($field) {
// 字符串查询
if (is_numeric($field)) {
$where[] = ['exp', $field];
} else {
$where[$field] = ['null', ''];
}
}
} elseif (is_array($op)) {
$where[$field] = $param;
} elseif (in_array(strtolower($op), ['null', 'notnull', 'not null'])) {
// null查询
$where[$field] = [$op, ''];
} elseif (is_null($condition)) {
// 字段相等查询
$where[$field] = ['eq', $op];
} else {
$where[$field] = [$op, $condition];
}
if (!empty($where)) {
if (!isset($this->options['where'][$logic])) {
$this->options['where'][$logic] = [];
}
$this->options['where'][$logic] = array_merge($this->options['where'][$logic], $where);
}
}
~~~
### $query->parsePkWhere()
~~~
protected function parsePkWhere($data, &$options)
{
$pk = $this->getPk($options);
// 获取当前数据表
if (!empty($options['alias'])) {
$alias = $options['alias'];
}
if (is_string($pk)) {
$key = isset($alias) ? $alias . '.' . $pk : $pk;
// 根据主键查询
if (is_array($data)) {
$where[$key] = isset($data[$pk]) ? $data[$pk] : ['in', $data];
} else {
$where[$key] = strpos($data, ',') ? ['IN', $data] : $data;
}
} elseif (is_array($pk) && is_array($data) && !empty($data)) {
// 根据复合主键查询
foreach ($pk as $key) {
if (isset($data[$key])) {
$attr = isset($alias) ? $alias . '.' . $key : $key;
$where[$attr] = $data[$key];
} else {
throw new Exception('miss complex primary data');
}
}
}
if (!empty($where)) {
if (isset($options['where']['AND'])) {
$options['where']['AND'] = array_merge($options['where']['AND'], $where);
} else {
$options['where']['AND'] = $where;
}
}
return;
}
~~~
## 3 表达式解析
### $query->parseExpress()
~~~
protected function parseExpress()
{
$options = $this->options;
// 获取数据表
if (empty($options['table'])) {
$options['table'] = $this->getTable();
}
if (!isset($options['where'])) {
$options['where'] = [];
} elseif (isset($options['view'])) {
// 视图查询条件处理
foreach (['AND', 'OR'] as $logic) {
if (isset($options['where'][$logic])) {
foreach ($options['where'][$logic] as $key => $val) {
if (array_key_exists($key, $options['map'])) {
$options['where'][$logic][$options['map'][$key]] = $val;
unset($options['where'][$logic][$key]);
}
}
}
}
if (isset($options['order'])) {
// 视图查询排序处理
if (is_string($options['order'])) {
$options['order'] = explode(',', $options['order']);
}
foreach ($options['order'] as $key => $val) {
if (is_numeric($key)) {
if (strpos($val, ' ')) {
list($field, $sort) = explode(' ', $val);
if (array_key_exists($field, $options['map'])) {
$options['order'][$options['map'][$field]] = $sort;
unset($options['order'][$key]);
}
} elseif (array_key_exists($val, $options['map'])) {
$options['order'][$options['map'][$val]] = 'asc';
unset($options['order'][$key]);
}
} elseif (array_key_exists($key, $options['map'])) {
$options['order'][$options['map'][$key]] = $val;
unset($options['order'][$key]);
}
}
}
}
// 表别名
if (!empty($options['alias'])) {
$options['table'] .= ' ' . $options['alias'];
}
if (!isset($options['field'])) {
$options['field'] = '*';
}
if (!isset($options['strict'])) {
$options['strict'] = $this->getConfig('fields_strict');
}
foreach (['master', 'lock', 'fetch_class', 'fetch_sql', 'distinct'] as $name) {
if (!isset($options[$name])) {
$options[$name] = false;
}
}
foreach (['join', 'union', 'group', 'having', 'limit', 'order', 'force', 'comment'] as $name) {
if (!isset($options[$name])) {
$options[$name] = '';
}
}
if (isset($options['page'])) {
// 根据页数计算limit
list($page, $listRows) = $options['page'];
$page = $page > 0 ? $page : 1;
$listRows = $listRows > 0 ? $listRows : (is_numeric($options['limit']) ? $options['limit'] : 20);
$offset = $listRows * ($page - 1);
$options['limit'] = $offset . ',' . $listRows;
}
$this->options = [];
return $options;
}
~~~
# 5 查询构造器
>> 查询对象可以调用查询构造合成SQL语句
>查询构造器的实验见 [查询构造](http://www.kancloud.cn/zmwtp/tp/212776)
## 查询构造器创建
>>查询构造器，针对不同数据库驱动拼接SQL语句时需要创建不同的查询构造器
### $query->builder()
>>创建针对不同数据库驱动的查询构造器。
~~~
protected function builder()
{
static $builder = [];
$driver = $this->builder;
if (!isset($builder[$driver])) {
$class = false !== strpos($driver, '\\') ? $driver : '\\think\\db\\builder\\' . ucfirst($driver);
$builder[$driver] = new $class($this->connection);
}
// 设置当前查询对象
$builder[$driver]->setQuery($this);
return $builder[$driver];
}
~~~
## 查询构造器使用
>> 在数据库操作中调用查询构造器拼接SQL语句
~~~
;insert中调用$query->builder()->insert()合成insert语句
public function insert(array $data, $replace = false, $getLastInsID = false, $sequence = null)
{
// 分析查询表达式
$options = $this->parseExpress();
// 生成SQL语句
$sql = $this->builder()->insert($data, $options, $replace);
// 获取参数绑定
$bind = $this->getBind();
if ($options['fetch_sql']) {
// 获取实际执行的SQL语句
return $this->connection->getRealSql($sql, $bind);
}
// 执行操作
$result = $this->execute($sql, $bind);
if ($getLastInsID) {
$sequence = $sequence ?: (isset($options['sequence']) ? $options['sequence'] : null);
return $this->getLastInsID($sequence);
}
return $result;
}
~~~
~~~
； update片段中调用$query->builder()->update()合成update语句
public function update(array $data)
{
$options = $this->parseExpress();
$pk = $this->getPk($options);
if (isset($options['cache']) && is_string($options['cache'])) {
$key = $options['cache'];
}
if (empty($options['where'])) {
// 如果存在主键数据 则自动作为更新条件
if (is_string($pk) && isset($data[$pk])) {
$where[$pk] = $data[$pk];
if (!isset($key)) {
$key = 'think:' . $options['table'] . '|' . $data[$pk];
}
unset($data[$pk]);
} elseif (is_array($pk)) {
// 增加复合主键支持
foreach ($pk as $field) {
if (isset($data[$field])) {
$where[$field] = $data[$field];
} else {
// 如果缺少复合主键数据则不执行
throw new Exception('miss complex primary data');
}
unset($data[$field]);
}
}
if (!isset($where)) {
// 如果没有任何更新条件则不执行
throw new Exception('miss update condition');
} else {
$options['where']['AND'] = $where;
}
} elseif (is_string($pk) && isset($options['where']['AND'][$pk]) && is_scalar($options['where']['AND'][$pk])) {
$key = 'think:' . $options['table'] . '|' . $options['where']['AND'][$pk];
}
// 生成UPDATE SQL语句
$sql = $this->builder()->update($data, $options);
// 获取参数绑定
$bind = $this->getBind();
if ($options['fetch_sql']) {
// 获取实际执行的SQL语句
return $this->connection->getRealSql($sql, $bind);
} else {
// 检测缓存
if (isset($key) && Cache::get($key)) {
// 删除缓存
Cache::rm($key);
}
// 执行操作
return '' == $sql ? 0 : $this->execute($sql, $bind);
}
}
~~~
- 框架简介
- 简介
- 框架目录
- 根目录
- 应用目录
- 核心目录
- 扩展目录
- 其他目录
- 框架流程
- 启动流程
- 请求流程
- 响应流程
- 框架结构
- 应用组织
- 网络请求
- 路由组织
- 数据验证
- 数据模型（M）
- 数据库连接(Connection)
- 数据库(Db)
- 查询构造(Builder)
- 数据库查询(Query)
- 模型(Model)
- 模板视图（V）
- 视图(View)
- 模板引擎(Think)
- 模板标签库(TagLib)
- 控制器（C）
- 网络响应
- 配置与缓存
- 配置操作
- 缓存操作
- cookie与session
- Cookie操作
- Session操作
- 自动加载
- 钩子注册
- 文件上传
- 分页控制
- 控制台
- 自动构建
- 日志异常调试
- 异常处理
- 代码调试
- 日志记录
- 框架使用
- 1 环境搭建(Server)
- 2 网络请求(Request)
- 3 请求路由(Route)
- 4 响应输出(Response)
- 5 业务处理(Controller)
- 6 数据存取(Model)
- 7 Web界面(View)