ORM: count statistics after the ignored group by

cause

When using a sql query tool of a PHP framework, I found that every time a grouping condition is added, one of the return parameters of the page method is the data set and the other is the total number, but the total number is always inaccurate.

problem

So, I took a look at the implementation method of the framework model.

For the following SQL query, the page() method will execute two SQLs, one is to check the data set, the other is to check the total number of data sets that meet the conditions, and the corresponding return [list, total].

list($list, $total) = $articleModel
->select([
    'XXX',
    'XXX',
])
->groupBy('type')
->page($page, $num);

For the inaccuracy of the total number, the problem appears in the sql query and processing results. For the above business query, it will generate:

# sql1
select count(*) from article group by type;

Compare sql1 with sql2 below, and think about the difference?

# sql2
select count(*) from article;

sql2 is the total number of records in the entire table calculated, and the returned result will always have only one piece of data;

sql1 adds one more type condition grouping than sql2. It calculates the total number of records under each group after grouping, that is, the total number of type1, the total number of type2..., the return result is 1-N.

solve

The bug of the framework in this, and it is not handled separately when processing 1614bf4c931b99 general query group query.

In the frame count(), the first value of the result set is taken as the total number. When in a normal query, this is no problem; but in a grouped query, only the total number under the type1 group is obtained. When grouping queries, we need to take the number of groups that should always be grouped.

// 框架中原代码
public function count($column = '*')
{
    if (!$this->DB) {
        throw  new Exception('您还没有连接数据库', Exception::CODE_DATABASE_ERROR);
    }
    $bak = $this->_sql;
    $sql = $this->buildSql($column);
    $this->sql = $sql;

    if ($this->justSql) {
        return 0;
    }
    $info = $this->DB->query($sql)->fetch(\PDO::FETCH_ASSOC); // 问题在于此，就只拿结果集的第一个记录
    $this->lastQueryAt = time();
    return isset($info['num']) ? $info['num'] : 0;
}

I found the problem, so I modified it and gave the framework a pr.

The modified code is as follows:

// 改造后代码
public function count($column = '*')
{
    if (!$this->DB) {
        throw  new Exception('您还没有连接数据库', Exception::CODE_DATABASE_ERROR);
    }
    $bak = $this->_sql;
    $sql = $this->buildSql($column);
    $this->sql = $sql;

    if ($this->justSql) {
        return 0;
    }

    $total = 0;
    if (count($bak['group']) > 0) {
        $info = $this->DB->query($sql)->fetchAll(\PDO::FETCH_ASSOC); // 有分组时，拿结果集的多个记录
        $total = count($info);
    } else {
        $info = $this->DB->query($sql)->fetch(\PDO::FETCH_ASSOC); // 无分组时，依然如原来一样拿结果集的第一个记录
        $total = isset($info['num']) ? $info['num'] : 0;
    }

    $this->lastQueryAt = time();
    return $total;
}

end！