where 相关的子句构造完成后,我们继续构造其它子句。这一篇我们进行聚合函数、分组、排序等子句的构造。
聚合函数
在 sql 中,有一些用来统计、汇总的函数,被称作聚合函数,如 SUM、COUNT、AVG 等。
使用 select() 方法时,我们可以用 select('COUNT(id)') 这种写法来使用聚合函数,但是这种方式有缺点:
为了更方便的获得聚合数据,我们需要为其单独编写方法。
getList() 方法
获得某一列的方法可以由 PDO::FETCH_COLUMN 来完成。
public function getList($field) { $this->_cols_str = ' '.self::_quote($field).' '; $this->_buildQuery(); $this->_execute(); // 获取一列数据 return $this->_pdoSt->fetchAll(PDO::FETCH_COLUMN,0); }
count() 方法
public function count($field = '*') { // 判断是否时 * // 非 * 时给字段添加引号 if(trim($field) != '*') { $field = self::_quote($field); } // 构造列查询字符串 $this->_cols_str = ' COUNT('.$field.') AS count_num '; // 取结果 return $this->row()['count_num']; }
构造 sql SELECT COUNT(id) FROM test_table;
$results = $driver->table('test_table') ->count('id');
由于聚合函数方法和 get()、row() 方法一样是取结果的,链式调用时切记要放到最后执行。
其它方法
sum()、avg() 等方法没有 COUNT('*') 这样的场景,比 count() 方法的编写更简单。
sum() 方法:
public function sum($field) { $this->_cols_str = ' SUM('.self::_quote($field).') AS sum_num '; return $this->row()['sum_num']; }
其它方法如 avg()、max() 之类的编写就不一一展示了,代码请看 WorkerF - 聚合函数 。
分组:group by 和 having
group by 子句指定了要分组的字段,可以是一个或多个 (用逗号隔开)。
having 子句和 group by 子句一起使用,作为分组的筛选条件,可以为空,除了关键字外,语法和 where 子句基本相同。
基类新增 groupBy() 方法:
public function groupBy($field) { // 是否初次调用 ? if($this->_groupby_str == '') { $this->_groupby_str = ' GROUP BY '.self::_wrapRow($field); } else { // 非初次调用(多个分组字段),使用逗号分隔 $this->_groupby_str .= ','.self::_wrapRow($field); } return $this; }
基类新增 having()、orHaving() 方法:
public function having() { $operator = 'AND'; // 初次调用 ? if($this->_having_str == '') { $this->_having_str = ' HAVING '; } else { $this->_having_str .= ' '.$operator.' '; } // 和 where 子句一样进行条件构造 $this->_condition_constructor(func_num_args(),func_get_args(),$this->_having_str); return $this; } public function orHaving() { $operator = 'OR'; if($this->_having_str == '') { $this->_having_str = ' HAVING '; } else { $this->_having_str .= ' '.$operator.' '; } $this->_condition_constructor(func_num_args(),$this->_having_str); return $this; }
这里我们也留一个处理原生字符串的 havingRaw() 方法 (手动填写数据,不进行数据绑定):
public function havingRaw($string) { $this->_having_str = ' HAVING '.$string.' '; return $this; }
构造 sql SELECT id,SUM(price) FROM test_table GROUP BY price HAVING SUM(price) > 1000;
:
$results = $driver->table('test_table') ->select('id','SUM(price)') ->groupBy('price') ->having('SUM(price)','>',1000) ->get(); // 使用 havingRaw() $results = $driver->table('test_table') ->select('id','SUM(price)') ->groupBy('price') ->havingRaw('SUM(price) > 1000') ->get();
排序
排序就很简单了,固定的语法和正序、倒序两个模式,多个字段排序时使用逗号隔开:
public function orderBy($field,$mode = 'ASC') { $mode = strtoupper($mode); if ( ! in_array($mode,['ASC','DESC'])) { throw new \InvalidArgumentException("Error order by mode"); } // 初次调用? if($this->_orderby_str == '') { $this->_orderby_str = ' ORDER BY '.self::_wrapRow($field).' '.$mode; } else { // 多个排序字段时,逗号隔开 $this->_orderby_str .= ','.self::_wrapRow($field).' '.$mode; } return $this; }
构造 sql SELECT * FROM tes_table ORDER BY price DESC,id ASC;
:
$results = $driver->table('test_table') ->orderBy('price','DESC') ->orderBy('id','ASC') ->get();
limit 和 分页
limit 子句
标准 sql 中的 limit 子句是 limit、offset 关键字一起使用的,MysqL 中有 LIMIT 0,10
的简写形式,但是 Postgresql 和 sqlite 并不适用。所以我们选用 limit、offset 语法:
public function limit($offset,$step) { $this->_limit_str = ' LIMIT '.$step.' OFFSET '.$offset.' '; return $this; }
构造 sql SELECT * FROM test_table LIMIT 10 OFFSET 1;
:
$results = $driver->table('test_table') ->limit(1,10) ->get();
分页
在数据请求时会遇到请求资源数据量大的问题,对于这个问题,普遍的解决方案就是分页。
为了灵活的访问分页,我们要返回以下的数据:
对于获取数据的总数,这里有一些问题。
如何获取总数?当然是使用上面讲到的聚合函数 count() 来处理。但是,使用了 count() 方法后相当于进行了一次 sql 的构造和执行 (执行后会将构造字符串设置为初始状态),那么还如何进行当页数据集合的获取呢?
两次执行
当然是有解决方案的,就是构造两次。
我们的查询构造器结构每次新建一个实例就会得到一个 PDO 的连接,所以为了构造两次而新建实例的话,代价太大,那么如何在一个实例中实现两次执行?
回顾上一篇,含有子查询的 sql 构造时经过了两次构造,对构造字符串进行了保护、恢复。那么换到分页中,还是构造两次 sql,对构造字符串进行保护、恢复,区别就是:因为要执行两次,所以要对绑定的数据也进行保护、恢复。
// 保存绑定数据 protected function _storeBindParam() { return $this->_bind_params; } // 恢复绑定数据 protected function _reStoreBindParam($bind_params) { $this->_bind_params = $bind_params; }
public function paginate($step,$page = NULL) { // 保存构造字符串和绑定数据 $store = $this->_storeBuildAttr(); $bind_params = $this->_storeBindParam(); // 获取数据总数 $count = $this->count(); // 恢复构造字符串和绑定数据 $this->_reStoreBuildAttr($store); $this->_reStoreBindParam($bind_params); // 创建分页数据 $page = $page ? $page : 1; // 第一页 $this->limit($step * ($page - 1),$step); $rst['total'] = $count; $rst['per_page'] = $step; $rst['current_page'] = $page; $rst['next_page'] = ($page + 1) > ($count / $step) ? NULL : ($page + 1); $rst['prev_page'] = ($page - 1) < 1 ? NULL : ($page - 1); $rst['first_page'] = 1; $rst['last_page'] = $count / $step; $rst['data'] = $this->get(); // 返回结果 return $rst; }
测试一下,获取从第五页的数据,每页 10 条数据:
$results = $driver->table('test_table') ->paginate(10,5);
Just do it!