写一个“特殊”的查询构造器 - (五、聚合函数、分组、排序、分页)

摘要：聚合函数在中，有一些用来统计汇总的函数，被称作聚合函数，如等。方法其它方法如之类的编写就不一一展示了，代码请看聚合函数。如何获取总数当然是使用上面讲到的聚合函数来处理。

where 相关的子句构造完成后，我们继续构造其它子句。这一篇我们进行聚合函数、分组、排序等子句的构造。

在 SQL 中，有一些用来统计、汇总的函数，被称作聚合函数，如 SUM、COUNT、AVG 等。

使用 select() 方法时，我们可以用 select("COUNT(id)") 这种写法来使用聚合函数，但是这种方式有缺点：

语义上并不直观

在防止关键字冲突时需要手动添加引号(参见第三篇条件查询)

拿到聚合数据需要一串繁琐的方法调用

为了更方便的获得聚合数据，我们需要为其多带带编写方法。

获得某一列的方法可以由 PDO::FETCH_COLUMN 来完成。

基类添加 getList() 方法：

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 子句一起使用，作为分组的筛选条件，可以为空，除了关键字外，语法和 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(), func_get_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();

标准 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();

在数据请求时会遇到请求资源数据量大的问题，对于这个问题，普遍的解决方案就是分页。

有了 limit() 方法，可以进行分页功能的实现了。

为了灵活的访问分页，我们要返回以下的数据：

数据的总数

每页的数据个数

当前页

下一页

上一页

第一页

最后一页

当前页的数据集合

对于获取数据的总数，这里有一些问题。

如何获取总数？当然是使用上面讲到的聚合函数 count() 来处理。但是，使用了 count() 方法后相当于进行了一次 SQL 的构造和执行 (执行后会将构造字符串设置为初始状态)，那么还如何进行当页数据集合的获取呢？

两次执行

当然是有解决方案的，就是构造两次。

我们的查询构造器结构每次新建一个实例就会得到一个 PDO 的连接，所以为了构造两次而新建实例的话，代价太大，那么如何在一个实例中实现两次执行？

回顾上一篇，含有子查询的 SQL 构造时经过了两次构造，对构造字符串进行了保护、恢复。那么换到分页中，还是构造两次 SQL，对构造字符串进行保护、恢复，区别就是：因为要执行两次，所以要对绑定的数据也进行保护、恢复。

基类中添加绑定数据保存、恢复的方法：

// 保存绑定数据
protected function _storeBindParam()
{
    return $this->_bind_params;
}
// 恢复绑定数据
protected function _reStoreBindParam($bind_params)
{
    $this->_bind_params = $bind_params;
}

基类中添加 paginate() 方法：

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!