Yii2 grid view filter sql

Question

Hi every one I have a grid view with SQLDataProvider and I'm doing the ModelSearch. Every tutorial on internet are with ActiveDataProvider and I don't want to use ActiveRecord because of the way I'm using my database.

So my question is : how can I add filter in the search method of MyModelSearch ?

Here my code :

Controller :

$queryTR = Yii::$app->db->createCommand(
            "select idTypeRessource, nomTypeRessource from type_ressource"
        )->queryAll();

        $typeRessource = ArrayHelper::map($queryTR, 'idTypeRessource', 'nomTypeRessource');
        $searchModel  = new RessourceSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->get());
        $model        = $dataProvider->getModels();

        return $this->render('ressource',
            [
                'dataProvider'  => $dataProvider,
                'searchModel'   => $searchModel,
                'model'         => $model,
                'typeRessource' => $typeRessource,
            ]
        );

ModelSearch :

class RessourceSearch extends Ressource
{
    public $nomRessource;
    public $idTypeRessource;
    public $idEtatRessource;
    public $dateRecherche;

    public function rules()
    {
        /*...*/
    }

    public function search($param)
    {
         $query = "select idTypeRessource, nomRessource, nomTypeRessource, nomEtatRessource, 
            date_format(tr.dateDebut, '%d/%m/%Y %H:%i') as trDateDebut, 
            date_format(tr.dateFin, '%d/%m/%Y %H:%i') as trDateFin,
            date_format(er.dateDebut, '%d/%m/%Y %H:%i') as erDateDebut,
            date_format(er.dateFin, '%d/%m/%Y %H:%i') as erDateFin
            from ressource
            join historique_type_ressource tr using (idRessource)
            join type_ressource using(idTypeRessource)
            join historique_etat_ressource er using (idRessource)
            join etat_ressource using (idEtatRessource)";

            $count = \Yii::$app->db->createCommand(
               "select count(*) from (" . $query . ") as req"
            )->queryScalar();

            $dataProvider = new SqlDataProvider([
            'sql'        => $query,
            'key'        => 'nomRessource',
            'totalCount' => $count,
            'pagination' => [
            'pageSize' => 15,
            ],
        ]);

           $dataProvider->sort->attributes['nomRessource'] = [
        'asc'  => ['nomRessource' => SORT_ASC],
        'desc' => ['nomRessource' => SORT_DESC],
    ];

       if (!($this->load($param) && $this->validate())) {
           return $dataProvider;
       }
     return $dataProvider;
    }
}

And I can't add filter rule because I'm using SQLDataProvider and not ActiveDataProvider (with this last one you have to use $query->andFilterWhere(...)

Thanks

You could enclose sql in a sql View and create a model from that sql view. Then apply ActiveDataProvider to that data. — Fabrizio Caldarelli, Apr 22 '16 at 18:46

score 1 · Answer 1 · edited Aug 29 '17 at 15:09

1

The more simple (but not very comfortable) way is emulate the 'andFilterWhere' behavior:

if (isset($param['your_field'])){
    $query .= " AND your_table.your_filed =  '" . $param['your_field ."';"
}

This should be repeated for all the filters fields.

edited Aug 29 '17 at 15:09

jmattheis

8,442
8
43
52

answered Apr 22 '16 at 14:25

scaisEdge

124,973
10
73
87

Good Solution. Thanks! – Luca Antonelli Mar 28 '19 at 08:55

Yii2 grid view filter sql

1 Answers1