Yii/Kartik Filter Gridview

[lerxx]

Hello community,

i would like to filter my gridview by writing something in the column field but I don’t know how. I have the here the two code snippets: documents and documentsSearch.

Thanks in advance!

documents:
<?php

/* @var $this yii\web\View */

use yii\helpers\Html;
use yii\helpers\ArrayHelper;
use kartik\grid\GridView;
use kartik\export\ExportMenu;
use kartik\daterange\DateRangePicker;
  
$this->title = 'Dokumente';
//$this->params['breadcrumbs'][] = $this->title;
?>

<div class="row">

    <p><div class="col-md-12">
           <?php  echo $this->render('_search',['dataProvider' => $dataProvider, 'searchModel' => $searchModel,]); 
				 
		   ?>
       <p><p></div>


    <div class="col-md-12">
        <div class="documents-index">
           
           <?php
			
            echo GridView::widget([
                'dataProvider' => $dataProvider,
                'filterModel' => $searchModel,
				'resizableColumns'=>true,
				//'rowOptions'   => function ($model, $key, $index, $grid) {
				//	return [
				//	'id' => $model['dre'], 
				//	'onclick' => exec('H:\kkh\dll\...'),
				//	];
				//},
                'condensed' => true,
                'pjax'=>false,
                'pjaxSettings'=>[
                    'neverTimeout'=>true,
                ],
                'hover'=>true,
                'toolbar' => [ 
                    'content' => '',
                    '{export}',
                ],
                'export'=>[
                    'fontAwesome'=>true
                ],
                'toggleDataContainer' => ['class' => 'btn-group-sm'],
                'exportContainer' => ['class' => 'btn-group-sm'],  
                'exportConfig' => [
                    GridView::CSV => ['label' => 'Als CSV speichern'],
                    GridView::EXCEL => ['label' => 'Als Excel speichern'],
                    ],
                'columns' => [
                        [
                    'attribute' => 'fallnr',
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
					
                        ],
                            [
                    'attribute' => 'name' ,
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
                    'headerOptions' => ['style' => 'width:20%'],
                    'value' =>  function ($data) { return $data['name'] . ', ' . $data['vorname']; }
                        ],
                    [
                    'attribute' => 'entlassung',
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
					'value' => function ($data) {
                                if ( strpos($data['entlassung'],'2099') !== false) {
                                    return null;
                                    }
                                else {
                                    return $data['entlassung'];
                                    }
                                },  
                    'format' => ['datetime', 'php:d.m.Y'],
                        ],
                                    [
                    'attribute' => 'fach',
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
					//'filterType' => GridView::FILTER_SELECT2,
					//'filterInputOptions' => [
					//'placeholder' => 'Alle',
					//'id' => 'fach',
					//]
                        ],
                                    [
                    'attribute' => 'station',
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
                        ],
                                    [
                    'attribute' => 'typ',
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
                        ],
                                    [
                    'attribute' => 'status',
                    'format' => 'html',
                    'vAlign'=>'middle',
                    'hAlign'=>'center',
                    'filter'=> [ "-99" => 'Storniert',"0"=>'Nicht freigegeben',"1"=>'Freigegeben',"1000" =>'In bearbeitung',
                                "300" =>'Archiviert'],
                    'value' => function ($data) {
                                if ($data['status'] == "-99") {
                                    return '<strong style="color:#6a737b"><i class="glyphicon glyphicon-trash" aria-hidden="true"></i></strong>';
                                    }
                                elseif ($data['status'] == "0") {
                                    return '<strong style="color:#f58025"><i class="glyphicon glyphicon-pencil" aria-hidden="true"></i></strong>';
                                    }
                                elseif ($data['status'] == "1") {
                                    return '<strong style="color:#7bc143"><i class="glyphicon glyphicon-ok" aria-hidden="true"></i></strong>';
                                    }
                                elseif ($data['status'] == "1000") {
                                    return '<strong style="color:#000000"><i class="glyphicon glyphicon-lock" aria-hidden="true"></i></strong>';
                                    }
                                elseif (($data['status'] == "300") or ($data['status'] == '200')) {
                                    return '<strong style="color:#000000"><i class="glyphicon glyphicon-archive" aria-hidden="true"></i></strong>';
                                    }
                                elseif ($data['status'] == "-1") {
                                    return '<strong style="color:#f58025"><i class="glyphicon glyphicon-alert" aria-hidden="true"></i></strong>';
                                    }
                                },  
     
                        ],
                                    [
                    'attribute' => 'gedruckt',
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
					'filter'=> [ "Ja","Nein"],
                    'format' => ['datetime', 'php:d.m.Y H:i:s'],
                        ],
                                    [
                    'attribute' => 'freigegeben', //Freigabedatum
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
                    'format' => ['datetime', 'php:d.m.Y H:i:s'],
                        ],
						 [
                    'attribute' => 'dicpsr',
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
                        ],
                                    [
                    'attribute' => 'laufzeit',
					 'hAlign'=>'left',
                    'vAlign'=>'middle',
					'filter'=> [ "> 1 Tag","> 5 Tage","> 10 Tage","> 1 Monat","> 6 Monate"],
                        ],
                ],
                'panel' => [
                    'before' => ' ', 
                ],
            ]);

            ?>
    </div>  
    
	<div class="col-md-12">
        <div class="documents-index">
           
           <?php
		 //echo $count = $dataProvider->getCount();

            echo GridView::widget([
                'dataProvider' => $dataProvider,
                'filterModel' => $searchModel,
				'resizableColumns'=>true,
				//'rowOptions'   => function ($model, $key, $index, $grid) {
				//	return [
				//	'id' => $model['dre'], 
				//	'onclick' => exec('H:\kkh\dll\...'),
				//	];
				//},
                'condensed' => true,
                'pjax'=>false,
                'pjaxSettings'=>[
                    'neverTimeout'=>true,
                ],
                'hover'=>true,
                'toolbar' => [ 
                    'content' => '',
                    '{export}',
                ],
                'export'=>[
                    'fontAwesome'=>true
                ],
                'toggleDataContainer' => ['class' => 'btn-group-sm'],
                'exportContainer' => ['class' => 'btn-group-sm'],  
                'exportConfig' => [
                    GridView::CSV => ['label' => 'Als CSV speichern'],
                    GridView::EXCEL => ['label' => 'Als Excel speichern'],
                    ],
                'columns' => [
                        [
                    'attribute' => 'station',
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
                        ],
                                    [
                    'attribute' => 'anzahl',
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
                        ],
                                    [
                    'attribute' => 'laufzeitentldauer',
					 'hAlign'=>'left',
                    'vAlign'=>'middle',
					'filter'=> [ "> 1 Tag","> 5 Tage","> 10 Tage","> 1 Monat","> 6 Monate"],
                        ],
                ],
                'panel' => [
                    'before' => ' ', 
                ],
            ]);

            ?>
    </div>  
</div>

and the file documentsSearch:

<?php

namespace app\models;

use Yii;
use yii\base\Model;
use yii\data\ArrayDataProvider;


class DocumentsSearch extends Model
{
       
        public $entlassung_von;
        public $entlassung_bis;
        public $dre;
        public $fallnr;
        public $name;
        public $entlassung;
        public $fach;
        public $station;
        public $typ;
        public $status;
        public $gedruckt;
        public $freigegeben;
		public $dicpsr;
        public $laufzeit;
        public $anlage;
        
        
        public function init()
        {
            $entlassung_von = date('d.m.Y');
            $entlassung_bis = date('d.m.Y');
        }
        
        public function rules()
        { 
            // only fields in rules() are searchable
            return [
                [['dre','fallnr','name','entlassung','fach','station','typ','status','gedruckt','freigegeben','dicpsr', 'laufzeit'], 'safe'],
            ];
        }
        
        public function attributeLabels()
        {
            return [
                'dre' => 'Dokument Nr.',
                'fallnr' => 'Fallnr.',
                'name' => 'Name Patient',
                'vorname' => 'Vorname',
                'entlassung' => 'Entlassd.',
                'fach' => 'Fach',
                'station' => 'Station',
                'typ' => 'Typ',
                'status' => 'Status',
                'gedruckt' => 'Gedruckt',
                'freigegeben' => 'Freigegeben',
				'dicpsr' => 'Dik. Arzt',
                'laufzeit' => 'Laufzeit von der Entlassung bis zur Freigabe',
            ];
        }

        public function search($params)
        {
			//$query = Model::find();
			
            $documents = Yii::$app->db->createCommand("SELECT distinct dre.dre .... ")
                ->queryAll();
            
            if ($this->load($params)) {
				//$from_date = $this->from_date; 
                $name = strtolower(trim($this->name));
                $fallnr = strtolower(trim($this->fallnr));
                $entlassung = strtotime($this->entlassung);
                $fach = strtolower(trim($this->fach));
                $station = strtolower(trim($this->station));
                $typ = strtolower(trim($this->typ));
                $status = strtolower(trim($this->status));
                $gedruckt = strtolower(trim($this->gedruckt));
                $freigegeben = (trim($this->freigegeben));
                $anlage = strtolower(trim($this->anlage));
				$dicspr = strtolower(trim($this->dicpsr));
                $laufzeit = ($this->laufzeit);
                $documents = array_filter($documents, function ($role) use ($fallnr,$name,$entlassung,$fach,$station,$typ,$status,
                                                                            $gedruckt,$freigegeben,$laufzeit) 
                {
                return (
                    empty($name) || strpos((strtolower(is_object($role) ? $role->name : $role['name'])), $name) !== false and
                    empty($fallnr) || strpos((strtolower(is_object($role) ? $role->fallnr : $role['fallnr'])), $fallnr) !== false and
                    empty($entlassung) || strpos((strtolower(is_object($role) ? $role->entlassung : $role['entlassung'])), $entlassung) !== false and
                    empty($fach) || strpos((strtolower(is_object($role) ? $role->fach : $role['fach'])), $fach) !== false and
                    empty($station) || strpos((strtolower(is_object($role) ? $role->station : $role['station'])), $station) !== false and
					empty($dicpsr) || strpos((strtolower(is_object($role) ? $role->dicpsr : $role['dicpsr'])), $dicpsr) !== false and
                    empty($typ) || strpos((strtolower(is_object($role) ? $role->typ : $role['typ'])), $typ) !== false and
                    empty($status) || strpos((strtolower(is_object($role) ? $role->status : $role['status'])), $status) !== false and
                    empty($gedruckt) || strpos((strtolower(is_object($role) ? $role->gedruckt : $role['gedruckt'])), $gedruckt) !== false and
                     empty($freigegeben) ||strpos((strtolower(is_object($role) ? $role->freigegeben : $role['freigegeben'])), $freigegeben) !== false and
                    empty($laufzeit) || strpos((strtolower(is_object($role) ? $role->laufzeit : $role['laufzeit'])), $laufzeit) !== false 
                    );
                });
            }
            
            $dataProvider = new ArrayDataProvider([
				//'query' => $query,
                'key' => 'dre',
                'allModels' => $documents,
                'sort' => [
                    'defaultOrder' => ['fach' => SORT_ASC],
                    'attributes' => ['dre','fallnr', 'name', 'vorname', 'entlassung', 'fach', 'station', 'typ', 'status', 'dicpsr','gedruckt', 'freigegeben', 'laufzeit', 'dicpsr'],
                ],
                'pagination' => [
                    'pageSize' => 30,
                ],
            ]);

            return $dataProvider;
        }
}

[lerxx]

I have tried the following:

• adding to search():
  $query->andFilterWhere([
  ‘fallnr’ => $this->fallnr, … ]);

but it doesn’t work. After inserting this, the page isn’t loading anymore. The fields are also already in “rules” (documentSearch). What am I doing wrong?

Thanks in advance!

[machour (Mehdi Achour)]

Hi

I see that you commented out the $query in your search method and that you are using a sql command instead, could you explain why?

Also, to be able to help you efficiently, could you keep just one column in your grid for now and remove all the other code for now? Post fresh files one you’re done, it will be easier for us to help you, then you’ll add back the other columns once you’ve understood how to make it work correctly.

[lerxx]

Thank you for your reply!

I have edited my code and now I am showing only the column “Fallnr” (Casenumber).

My Search Document:

    <?php

namespace app\models;

use Yii;
use yii\base\Model;
use yii\data\ArrayDataProvider;


class DocumentsSearch extends Model
{
       
        public $entlassung_von;
        public $entlassung_bis;
        public $fallnr;
        
        public function init()
        {
            $entlassung_von = date("d.m.Y", strtotime("-2 months"));
            $entlassung_bis = date("d.m.Y", strtotime("-1 months"));
        }
        
        public function rules()
        { 
            // only fields in rules() are searchable
            return [
                [['fallnr'], 'safe'],
            ];
        }
        
        
        public function attributeLabels()
        {
            return [
                'fallnr' => 'Fallnr.',
            ];
        }

        
        public function search($params)
        {
            $query = Yii::$app->db->createCommand("SELECT distinct dre.dre as dre, dre.pat as Fallnr FROM x1234dre ..
			 ")->queryAll();
            
            if ($this->load($params)) {
                $fallnr = strtolower(trim($this->fallnr));
                $query = array_filter($query, function ($role) use ($fallnr) 
                {
                return (
                    empty($fallnr) || strpos((strtolower(is_object($role) ? $role->fallnr : $role['fallnr'])), $fallnr) !== false
                    );
                });
            }
            
            $dataProvider = new ArrayDataProvider([
				//'query' => $query,
                'key' => 'dre',
                'allModels' => $query,
                'sort' => [
                    'defaultOrder' => ['fallnr' => SORT_ASC],
                    'attributes' => ['fallnr'],
                ],
                'pagination' => [
                    'pageSize' => 30,
                ],
			]);
			
            return $dataProvider;
        }
}

And the new “View”:

<?php

/* @var $this yii\web\View */
use yii\helpers\Html;
use yii\helpers\ArrayHelper;
use kartik\grid\GridView;
use kartik\export\ExportMenu;
use kartik\daterange\DateRangePicker;
   
$this->title = 'Dokumente';
?>

<div class="row">

    <p><div class="col-md-12">
           <?php  echo $this->render('_search',['dataProvider' => $dataProvider, 'searchModel' => $searchModel,]); ?>
    <p><p></div>

    <div class="col-md-12">
        <div class="documents-index">
           <?php
            echo GridView::widget([
                'dataProvider' => $dataProvider,
                'filterModel' => $searchModel,
				'resizableColumns'=>true,
                'condensed' => true,
                'pjax'=>false,
                'pjaxSettings'=>[
                    'neverTimeout'=>true,
                ],
                'hover'=>true,
                'toolbar' => [ 
                    'content' => '',
                    '{export}',
                ],
                'export'=>[
                    'fontAwesome'=>true
                ],
                'toggleDataContainer' => ['class' => 'btn-group-sm'],
                'exportContainer' => ['class' => 'btn-group-sm'],  
                'exportConfig' => [
                    GridView::CSV => ['label' => 'Als CSV speichern'],
                    GridView::EXCEL => ['label' => 'Als Excel speichern'],
                    ],
                'columns' => [
                        [
                    'attribute' => 'fallnr',
                    'hAlign'=>'left',
                    'vAlign'=>'middle',
					
                        ],
                ],
                'panel' => [
                    'before' => ' ', 
                ],
            ]);
            ?>
    </div>  

To your questions:

• I am using an sql command because I am using an database connection and that’s the place where I am getting the case numbers etc.
• And if I am going to use ‘query’ => $query in my “new ArrayDataprovider” or at the beginning of my search-function $query = Model::find() I am getting an error.

Thanks in advance!

[machour (Mehdi Achour)]

What is the error you’re getting when using Model::find()? Let’s get to the bottom of that, because best thing would be indeed to use Model::find() and the classical SearchModel that gets generated by Gii.

The problem with your current approach is that you’re loading ALL database entries, then filter them out using PHP. This won’t have good performances.

[lerxx]

I am getting the error: Calling unknown method: app\models\DocumentsSearch::find()

Can you tell me where the find method should be (I think in my model file) and how this would look like?

[lerxx]

Hi machour, I have a further question after I have tried something out.

Is it correct, that the gii tool should also work over an ODBC connection? If yes, I think it would be easier for me to fix this tool (because it shows me that it doesn’t work “Connection does not support reading schema information for ‘{ingres ii}’ DBMS.” is the eception by trying to create a model with gii).

And you said, that I am going to select the data from the dabase to often and that isn’t really performance. If I am using correct the models and the controller/search-files (generated with CRUD), loads it automatically the data from the database like it would be correct? Does I need to use the select statement furthermore?

And if I am going to join databases, are I am going to do this with the functions of yii? If yes, this would be faster than my “select-statement” method, or? Thanks in advance!

[machour (Mehdi Achour)]

Hi @lerxx

Indeed using Gii would be the best for you. Please open an issue at https://github.com/yiisoft/yii2-gii if you’re facing a bug with Ingres.

As for the error you’re getting, your DocumentsSearch is extending \yii\base\Model.
Usually, it would extend your Documents ActiveRecord:
DocumentsSearch => Documents => ActiveRecord

find() is from ActiveRecord