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
– Fabrizio Caldarelli, Commented Apr 22, 2016 at 18:46

jmattheis · Accepted Answer · 2017-08-29 15:09:36Z

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, 2017 at 15:09

jmattheis

11.2k12 gold badges52 silver badges62 bronze badges

answered Apr 22, 2016 at 14:25

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Yii2 grid view filter sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related