Yii2 : Cannot Show Data in Widget GridView

Question

I try to show data from database in view using gridview, but i got problem

error message

Unknown Method – yii\base\UnknownMethodException Calling unknown method: yii\db\ActiveQuery::getCount()

my controller

public function actionIndex()
{
    $sql = "SELECT presensi.presensi_tanggal 'tanggal', sum(if( hadir.keteranganhadir_id='1',1,0)) 'hadir', sum(if( hadir.keteranganhadir_id='2',1,0)) 'tidak_hadir', count(*) 'total' FROM hadir, keteranganhadir, presensi where hadir.keteranganhadir_id = keteranganhadir.keteranganhadir_id and hadir.presensi_id = presensi.presensi_id group by presensi.presensi_tanggal";

    $model = Hadir::findBySql($sql)->all();

    return $this->render('index', [
        'hadir' => $model,
    ]);
}

my view

<?= GridView::widget([
        'dataProvider' => $hadir,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],

            'tanggal',
            'hadir',
            'tidak_hadir',
            'total',

            ['class' => 'yii\grid\ActionColumn'],
        ],
    ]); ?>

How can i fix the problem?

Answer 1

Gridview looks for dataprovider, not array of activerecord models you have sent:

http://www.yiiframework.com/doc-2.0/yii-data-sqldataprovider.html

in your controller/actionIndex

$count = Yii::$app->db->createCommand('
    SELECT COUNT(*) FROM user WHERE status=:status
', [':status' => 1])->queryScalar();

$dataProvider = new SqlDataProvider([
    'sql' => 'SELECT * FROM user WHERE status=:status',
    'params' => [':status' => 1],
    'totalCount' => $count,
    'sort' => [
        'attributes' => [
            'age',
            'name' => [
                'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC],
                'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC],
                'default' => SORT_DESC,
                'label' => 'Name',
            ],
        ],
    ],
    'pagination' => [
        'pageSize' => 20,
    ],
]);
return $this->render('index', [
    'hadir' => $dataProvider,
]);

Answer 2

You can try this:
In Controller.php file:

public function actionIndex()
    {
        $searchModel = new InvoiceSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);
    }

Your view file seems to be true. Just add above code in your controller file.