Error in yii\data\BaseDataProvider::getTotalCount():

[strtob]

What steps will reproduce the problem?

use dataprovider with this setup:

 $query = InquiryFinanceHasDocument::find()

        ;

        $query->select(['tbl_inquiry_finance_has_document.*', 'count(tbl_inquiry_finance_has_document.id)']);

        $query->select([
            '*',
            'filesCount' => \app\models\FileHasInquiryFinance::find()
                    ->select(['COUNT(*)'])
                    ->andWhere('tbl_file_has_inquiry_finance.tbl_inquiry_finance_has_document_id = tbl_inquiry_finance_has_document.id'),
        ]);

  $query->andFilterWhere(['>', 'filesCount', 0]);

What is the expected result?

no error

What do you get instead?

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'filesCount' in 'where clause'
The SQL being executed was: SELECT COUNT(*) FROM `tbl_inquiry_finance_has_document` WHERE (`tbl_inquiry_finance_has_document`.`deleted_by`=0) AND (`isObligatory`=1) AND (`isComplete`=0) AND (`filesCount` > 0)

Error Info: Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'filesCount' in 'where clause'
)

in vendor/kartik-v/yii2-grid/src/GridViewTrait.php at line 1511 – [yii\data\BaseDataProvider::getTotalCount](http://www.yiiframework.com/doc-2.0/yii-data-basedataprovider.html#getTotalCount()-detail)()
1505150615071508150915101511151215131514151515161517

        $defaultOptions = [
            'maxCount' => 10000,
            'minCount' => 500,
            'confirmMsg' => Yii::t(
                'kvgrid',
                'There are {totalCount} records. Are you sure you want to display them all?',
                ['totalCount' => number_format($this->dataProvider->getTotalCount())]
            ),
            'all' => [
                'icon' => $notBs3 ? 'fas fa-expand' : 'glyphicon glyphicon-resize-full',
                'label' => Yii::t('kvgrid', 'All'),
                'class' => $defBtnCss,
                'title' => Yii::t('kvgrid', 'Show all data'),
['totalCount' => number_format($this->dataProvider->getTotalCount())]

It seems that the subquery is not used by the getTotalCount() function. Any ideas?

Additional info

Q	A
Yii version	2.0.46-dev
PHP version	7.4.16
Operating system	debian 11

[strtob]

due to the fact that dataprovider use count(*) the subquery is removed in "ActiveDataProvider.php"

 /**
     * {@inheritdoc}
     */
    protected function prepareTotalCount() {
        if (!$this->query instanceof QueryInterface) {
            throw new InvalidConfigException('The "query" property must be an instance of a class that implements the QueryInterface e.g. yii\db\Query or its subclasses.');
        }
        $query = clone $this->query;
        return (int) $query->limit(-1)->offset(-1)->orderBy([])->count('*', $this->db);
    }

How fix this?

[ntesic]

Maybe to try with andHave() instead andWhere() for filesCount?

[alex-code]

Can you show more of the query? You've got two selects, the last one will overwrite the first.

If your query is distinct or has a groupBy, having, union clause the count will wrap the query.

[ntesic]

It is not Yii2 error. You can't use WHERE on column alias, just HAVE. You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.