Yii Framework 2 : SqlDataProvider

Yii2.0 SqlDataProvider is used to get the data provider using plain SQL statement. SqlDataProvider is return the data provider as array for each row of query result. We can use the sorting, pagination in SqlDataProvider using $sort and $pagination parameter.

Pagination: If we want pagination concept, We have to configure the $totalCount attribute.

Sort : If we want to use the sorting feature, We have to configure the $sort attribute. If you want to remove the sorting, set 'sort' as false.

SqlDataProvider Formats

FORMAT 1

<?php
$totalCount = Yii::$app->db
            ->createCommand('SELECT COUNT(*) FROM posts WHERE publish=:publish', [':publish' => 1])
            ->queryScalar();

$dataProvider = new SqlDataProvider([
    'sql' => 'SELECT * FROM posts WHERE publish=:publish',
    'params' => [':publish' => 1],
    'totalCount' => $totalCount,
    //'sort' =>false, to remove the table header sorting
    'sort' => [
        'attributes' => [
            'title' => [
                'asc' => ['title' => SORT_ASC],
                'desc' => ['title' => SORT_DESC],
                'default' => SORT_DESC,
                'label' => 'Post Title',
            ],
            'author' => [
                'asc' => ['author' => SORT_ASC],
                'desc' => ['author' => SORT_DESC],
                'default' => SORT_DESC,
                'label' => 'Name',
            ],
			'created_on'
        ],
    ],
    'pagination' => [
        'pageSize' => 10,
    ],
]);

return $dataProvider;
?>

SqlDataProvider To Gridview

We will get the array for each row. Use the key to display/access the value in gridview of yii2.0.


<?php
<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        [
            'label' =>"Name",
            'attribute' => 'tbl_column_name',
            'value'=>function($data){
                return $data["tbl_column_name"];
            }
        ],
        'title',
        'author',
        'created_on',
        ['class' => 'yii\grid\ActionColumn'],
    ],
]);
?>

SqlDataProvider To Excel

GridView/SqlDataProvider To Excel

  • Ben

    When I put this data in the view I get “Undefined variable $dataprovider” What am I doing wrong please?
    I am referring to the gridview yii 2.0.

    Is there anything else I need to set anywhere else in the app?

    All I want is output the data of a column from mysql.

    Thanks,

    Ben

    • m bala

      Please post your code

      • Ben

        Hi M bala,

        In your demo you show this code:

        $totalCount = Yii::$app->db->createCommand(‘SELECT COUNT(*) FROM posts WHERE publish=:publish’, [‘:publish’ => 1])
        ->queryScalar();

        $dataProvider = new SqlDataProvider([
        ‘sql’ => ‘SELECT * FROM posts WHERE publish=:publish’,
        ‘params’ => [‘:publish’ => 1],
        ‘totalCount’ => $totalCount,
        //’sort’ =>false, to remove the table header sorting
        ‘sort’ => [
        ‘attributes’ => [
        ‘title’ => [
        ‘asc’ => [‘title’ => SORT_ASC],
        ‘desc’ => [‘title’ => SORT_DESC],
        ‘default’ => SORT_DESC,
        ‘label’ => ‘Post Title’,
        ],
        ‘author’ => [
        ‘asc’ => [‘author’ => SORT_ASC],
        ‘desc’ => [‘author’ => SORT_DESC],
        ‘default’ => SORT_DESC,
        ‘label’ => ‘Name’,
        ],
        ‘created_on’
        ],
        ],
        ‘pagination’ => [
        ‘pageSize’ => 10,
        ],
        ]);

        return $dataProvider;

        But should this code goes in the view file or in the controller? I am a bit lost.
        If in the controller, how shall I output the results in the view files please?

        Thank you,

        Ben

        • m bala

          Add this code in model or controller. It is based on your logical.
          Model – return the $dataProvider

          Controller – pass this value to view

          • Ben

            I am not sure I understood what you meant, would you have a simple “echo” example of what can be done in the view with the above code please? How to echo this in the view, this is my problem.

            Thank you,

            Ben

          • Mark Novem Grisola

            i use that example and it works for me very well for 4 tables, i just follow the idea and supply the other codes.. anyway ill give u a hand, hope it will help you. i assume that we have a single parameter to serve as a constraint for filtering.
            1. Make sure that your sql query is working..
            2. class Household {
            ……
            public static function getList($param1)
            { $total = …… ;
            $dataProvider = … ;
            return $dataProvider;
            } inside the function just follow the example abovel..

            3. Controller..
            public function actionView()
            { …..
            $dataProvider = Household::getList($param1);
            //remember $parma1 is the constraints for filtering
            return $this->render(‘view’,[‘dataProvider’=>$dataProvider]);
            …..
            }
            4. Vew
            view.php
            $dataProvider,
            ‘columns’=> [ [‘class’=>’yiigridSearialColumn’ ],
            ‘column1’,
            ‘column2’,
            ……. ,
            [‘class’=>’yiigridActionColumn’, ‘template’=>'{update}’], // it’s up to you
            ],
            ]);?>

            That’s all.. hope it helps you a little.. 😉

          • matias Tucci

            I have a issue with the filtering option, this is my code:
            The querys and everything else works, I think my problem is with the $searchmodel maybe?

            $columns = “c.compNum AS CompNum,h.variety AS Hybrid,m.variety AS Mother,f.variety AS Father,cr.crop AS Crop,n.numcompartment AS Nursery,o.numCrop AS NumCrop,o.orderKg AS `Order(KG)` ,o.numRows AS NumRows”;
            $columnsArray = explode(‘,’, $columns);
            foreach($columnsArray as $colum){
            echo $colum.””;
            }
            $id = 1;
            $totalCount = Yii::$app->db->createCommand(‘SELECT DISTINCT COUNT(*)
            FROM `order` o
            INNER JOIN `compartment` c
            INNER JOIN `hybrid` h
            INNER JOIN `Mother` m
            INNER JOIN `Father` f
            INNER JOIN `Crop` cr
            INNER JOIN `Nursery` n
            WHERE o.Hybrid_idHybrid = :id
            AND c.idcompartment = o.compartment_idCompartment
            AND h.idHybrid = o.Hybrid_idHybrid
            AND h.Mother_idMother = m.idMother
            AND h.Father_idFather = f.idFather
            AND h.Crop_idCrops = cr.idcrops
            AND n.idnursery = o.nursery_idnursery
            AND o.delete = 0’, [‘:id’ => $id])
            ->queryScalar();

            $dataProvider = new SqlDataProvider([
            ‘sql’ => ‘SELECT DISTINCT ‘.$columns.’
            FROM `order` o
            INNER JOIN `compartment` c
            INNER JOIN `hybrid` h
            INNER JOIN `Mother` m
            INNER JOIN `Father` f
            INNER JOIN `Crop` cr
            INNER JOIN `Nursery` n
            WHERE o.Hybrid_idHybrid = :id
            AND c.idcompartment = o.compartment_idCompartment
            AND h.idHybrid = o.Hybrid_idHybrid
            AND h.Mother_idMother = m.idMother
            AND h.Father_idFather = f.idFather
            AND h.Crop_idCrops = cr.idcrops
            AND n.idnursery = o.nursery_idnursery
            AND o.delete = 0
            ‘,
            ‘params’ => [
            ‘:id’ => $id
            ],
            ‘totalcount’ => $totalCount,
            ‘pagination’ => [
            ‘pageSize’ => 20,
            ],
            ]);

            $searchModel = new OrderSearch();
            // I tried with this one to but it didin´t work 🙁
            // $searchModel = $dataProvider->getModels();

            // get the user records in the current page
            $model = $dataProvider->getModels();
            echo GridView::widget([
            ‘dataProvider’ => $dataProvider,
            ‘filterModel’ => $searchModel,
            ‘columns’ => [
            ‘Order(KG)’,
            ‘NumCrop’,
            [
            ‘label’ =>”NumCrop”,
            ‘attribute’ => ‘NumCrop’,
            ‘value’=>function($data){
            return $data[“NumCrop”];
            }
            ],

            [‘class’ => ‘yiigridActionColumn’],
            ],
            ]);

          • Mark Novem Grisola

            Hi matias Tucci, sorry for late reply. Did you already solve the problem? If not yet, we need to know what you are trying to accomplish and what errors occurs if possible give some details like error messages so that we can point out easily the problem.

          • matias Tucci

            sorry, I solved it somehow, thnx

  • Fabio Di Rosa

    I’m quite new to Yii2. I’m using advanced structure

    I need to show a custom sql result in a view without using a model because I would like to display a sql view.

    index.php

    $dataProvider,
    ‘columns’ => [
    [‘class’ => ‘yiigridSerialColumn’],
    ‘COD_RISORSA’,
    [
    ‘label’ =>”Nome”,
    ‘attribute’ => ‘NOME’,
    ‘value’=>function($data){
    return $data[“NOME”];
    }
    ],
    ‘COGNOME’,
    [‘class’ => ‘yiigridActionColumn’],
    ],
    ]); ?>

    VRisorseController.php

    public function actionIndex()
    {
    $totalCount = Yii::$app->db->createCommand(‘SELECT COUNT(*) FROM v_risorse’)->queryScalar();
    $dataProvider = new SqlDataProvider([
    ‘sql’ => ‘SELECT * FROM v_risorse’,
    ‘totalCount’ => $totalCount,
    ‘sort’ =>false,
    ‘pagination’ => [
    ‘pageSize’ => 10,
    ],
    ]);
    return $this->render(‘index’, [
    ‘dataProvider’ => $dataProvider,
    ]);
    }
    At the following Url: http://localhost/advanced/frontend/web/index.php?r=vrisorse%2Findex
    I have the error:
    Not Supported – yiibaseNotSupportedException Message format ‘number’ is only supported for integer values. You have to install PHP intl extension to use this feature. 1. in C:xampphtdocsadvancedvendoryiisoftyii2i18nMessageFormatter.php

    I tried to comment all the columns in gridview, and the error seems to be related to $dataProvider variable

    ‘COD_RISORSA’,’NOME’, ‘COGNOME’ are columns of the select.

    • m bala

      Just remove gridview and check the query is working fine or not

    • Dewo

      [solve] install or active PHP intl extension then restart webserver, done.

  • hoAAah

    I tried this with MySQL db and it works fine..
    But when I tried this with SQL Server (version :2008), $totalCount gives an “array to string conversion” error, and $dataProvider give “undefined offset” error.

  • matias Tucci

    It works fine but I would like to know how to add filtering to this.