ORM: count statistics after ignored group by

Time:2022-1-1

cause

When using the SQL query of a PHP framework, it is found that after grouping conditions are added each time, one of the parameters returned by the page method is the dataset and the other is the total number, but the total number is always inaccurate.

problem

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

For the following SQL query, the page () method will execute two SQL queries, one is to query the data set, the other is to query the total number of qualified data sets, 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 occurs in the SQLqueryandProcessing resultsIn, for the query of the above business, the following will be generated:

# 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 whole table calculated, and the returned result always has only one piece of data;

Sql1 adds a group with type condition to 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… And the return result is 1-n.

solve

This is exactly the bug of the framework, which is being handledGeneral queryandGrouping queryIt is not handled separately.

The framework count() takes the first value of the result set as the total number, which is no problem in ordinary queries; However, in the grouping query, only the total number under the type1 grouping is obtained. When grouping query, we should always take the number of groups.

//Original code of framework
public function count($column = '*')
{
    if (!$this->DB) {
        Throw new exception ('You haven't connected to the database yet ', 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); //  The problem is, just take the first record of the result set
    $this->lastQueryAt = time();
    return isset($info['num']) ? $info['num'] : 0;
}

I found the problem, so I modified it and put forward a PR to the framework.

The modified code is as follows:

//Modified code
public function count($column = '*')
{
    if (!$this->DB) {
        Throw new exception ('You haven't connected to the database yet ', 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); //  When there are groups, take multiple records of the result set
        $total = count($info);
    } else {
        $info = $this->DB->query($sql)->fetch(\PDO::FETCH_ASSOC); //  When there is no grouping, the first record of the result set is still taken as before
        $total = isset($info['num']) ? $info['num'] : 0;
    }

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

end!

Recommended Today

Could not get a resource from the pool when the springboot project starts redis; nested exception is io. lettuce. core.

resolvent: Find your redis installation path: Start redis server Exe After successful startup: Restart project resolution. ———————————————————————->Here’s the point:<——————————————————————- Here, if you close the redis command window, the project console will report an error. If you restart the project, the same error will be reported at the beginning, The reason is: It is inconvenient to […]