How to access data from a query to a CGridView

Question

I'm trying to use on my view a search made by CreateCommand to fill my CGridView, but I still need to pass as a dataProvider a CActiveDataProvider object.

Is there any way to transform my CreateCommand object into CActiveDataProvider or to do the same query directly as a CActiveDataProvider?

This is the view

<?php $this->widget('zii.widgets.grid.CGridView', array(
                    'id' => 'credenciado-grid',
                    'dataProvider' => $model->search(),
                    'filter' => $model,
                    'afterAjaxUpdate' => "loadAfterAjax",
                    'rowHtmlOptionsExpression' => 'array("style"=>"cursor: pointer","data-row-id" => $data->cre_id, "class" => "row_grid")',
                    'columns' => array(
                            'cre_razao_social',
                        array(
                            'header'=>'Status',
                            'name'=>'cre_ativo',
                            'value'=>'Status::getStatus($data->cre_ativo)',
                            'filter'=>array(true => 'ATIVO',false => 'INATIVO'),
                            'filterHtmlOptions'=>array('class'=>'col-sm-2'),
                        ),
                    ),
                    )); ?>

This is the controller

 $model = new Credenciado('getValoresCredenciadosByConvenioRank');
        $model = new CArrayDataProvider($model, array());
//        pe($model);
        $modelEst = new Estabelecimento('search_adm');
//        pe($model);

        $modelEst->unsetAttributes();
        if (isset($_GET['Estabelecimento']))
            $modelEst->setAttributes($_GET['Estabelecimento']);


//        $model->unsetAttributes();
//        if (isset($_GET['Credenciado']))
//            $model->setAttributes($_GET['Credenciado']);

        $this->render('listar',array(
            'model' => $model,
            'modelEst' => $modelEst,
            'mes1' => $mes1,
            'mes2' => $mes2,
            'meses' => $meses,
            'produto' => $produto,
            'data' => $format,
            'datas' => $date_unique,
            'series' => $series,
            'ano1' => $ano1,
            'ano2' => $ano2
        ));
    }

This is the Model

$q = Yii::app()->db->createCommand()
            ->select("
            cre.cre_razao_social,
            count(op.ope_id) as linhas,
            par.par_data_vencimento,
            pro.pro_codigo,
            SUM(
                CASE
                    WHEN date_trunc('month',par_data_vencimento) < date_trunc('month',now()) AND pro_codigo != 2 THEN
                        par.par_valor
                    WHEN pro_codigo = 2 THEN
                        op.ope_valor_parcela
                    ELSE
                        op.ope_valor_parcela
                END
            ) as total_carteira")
            ->from("operacao op")
            ->join('parcela par', 'par.par_operacao_ope_id = op.ope_id')
            ->join('pos po', 'po.pos_id = op.ope_pos_pos_id')
            ->join('rubrica_credenciado rub', 'rub.rub_id = po.pos_rubrica_credenciado_rub_id')
            ->join('credenciado cre','rub.rub_credenciado_cre_id = cre_id')
            ->join('produto pro', 'pro.pro_id = rub.rub_produto_pro_id')
            ->join('matricula ma', 'ma.mat_matricula = op.ope_matricula AND ma.mat_convenio_con_id = '.Yii::app()->user->convenioid.' ')
            ->where('op.ope_con_id = :con_id', array(':con_id' => $con_id));
        $q->andWhere(
            "(par.par_data_vencimento between :datamin and :datamax and ope_status in('E','S'))
            OR
            (par.par_data_vencimento between :datamin and :datamax AND par.par_num_parcela = op.ope_qtde_parcelas AND op.ope_status = 'Q')
            ",
            array(':datamin'=>$datamin, ':datamax'=>$datamax));

        if(Yii::app()->user->sistema != 1) {
            $q->andWhere('rub_consignavel = true');
        }

        $q->order('par.par_data_vencimento', 'ASC');
        //$q->andWhere(array('in', 'ope_status', array('E','S')));

        $q->group('cre.cre_razao_social, pro.pro_codigo, par.par_data_vencimento');


        $q->queryAll();

This is the error

exception 'CException' with message 'CArrayDataProvider and its behaviors
do not have a method or closure named "search".' in
/var/www/html/yii/framework/base/CComponent.php:266
Stack trace:

Answer 1

you are making a mistake in your view. In your controller you are already passing object of CArrayDataProvider (i.e $model) to your view, so you should not call search() function on $model variable. Instead you should create CArrayDataProvider object from CreateCommand in your model and pass the object of Credenciado as $model in your controller and call your query method in the view like $model-> getValoresCredenciadosByConvenioRank() to get the data provider. Then your view would look like this

<?php $this->widget('zii.widgets.grid.CGridView', array(
                'id' => 'credenciado-grid',
                // call your query function to get data provider (this is the name of function that you have implemented in your model)
                'dataProvider' => $model->getValoresCredenciadosByConvenioRank(),
                'filter' => $model,
                'afterAjaxUpdate' => "loadAfterAjax",
                'rowHtmlOptionsExpression' => 'array("style"=>"cursor: pointer","data-row-id" => $data->cre_id, "class" => "row_grid")',
                'columns' => array(
                        'cre_razao_social',
                    array(
                        'header'=>'Status',
                        'name'=>'cre_ativo',
                        'value'=>'Status::getStatus($data["cre_ativo"])',
                        'filter'=>array(true => 'ATIVO',false => 'INATIVO'),
                        'filterHtmlOptions'=>array('class'=>'col-sm-2'),
                    ),
                ),
     ));
?>

Your controller would become this

    $model = new Credenciado('getValoresCredenciadosByConvenioRank');
    $model->unsetAttributes();
    if (isset($_GET['Credenciado']))
        $model->setAttributes($_GET['Credenciado']);

    // pe($model);
    $modelEst = new Estabelecimento('search_adm');
    // pe($model);

    $modelEst->unsetAttributes();
    if (isset($_GET['Estabelecimento']))
        $modelEst->setAttributes($_GET['Estabelecimento']);

    $this->render('listar',array(
        'model' => $model,
        'modelEst' => $modelEst,
        'mes1' => $mes1,
        'mes2' => $mes2,
        'meses' => $meses,
        'produto' => $produto,
        'data' => $format,
        'datas' => $date_unique,
        'series' => $series,
        'ano1' => $ano1,
        'ano2' => $ano2
    ));
}

And your model would return CArrayDataProvider instead of query result like this,

$queryResult = $q->queryAll();
// return data provider for view instead of query result
return new CArrayDataProvider($queryResult, array());

Hopefully this solution will solve your problem. Let me know if it works or if the problem still persists.