How to add Search and Filter Criteria in Yii

Question

I am developing a database application using Yii Framework. I am reading tables from MySQL database and displaying them to the user. I need the user to be able to filter the fields in the table or search for a certain value.

For example, I have a table named "supermarkets":

CREATE TABLE IF NOT EXISTS `supermarkets` (
  `Name` varchar(71) NOT NULL,
  `Location` varchar(191) DEFAULT NULL,
  `Telephone` varchar(68) DEFAULT NULL,
  `Fax` varchar(29) DEFAULT NULL,
  `Website` varchar(24) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

.../model/supermarkets:

<?php

namespace app\models;

use yii\db\ActiveRecord;

class Supermarkets extends ActiveRecord
{



}

.../views/supermarkets/index.php:

<?php
use yii\helpers\Html;
use yii\widgets\LinkPager;
?>
<h1>Supermarkets</h1>
<ul>
<?php
$array = (array) $supermarkets;

function build_table($array){

    // start table

    $html = '<table class="altrowstable" id="alternatecolor">';

    // header row

    $html .= '<tr>';

    foreach($array[0] as $key=>$value){

            $html .= '<th>' . $key . '</th>';

        }

    $html .= '</tr>';

    // data rows

    foreach( $array as $key=>$value){

        $html .= '<tr>';

        foreach($value as $key2=>$value2){

            $html .= '<td>' . $value2 . '</td>';

        }

        $html .= '</tr>';

    }

    // finish table and return it

    $html .= '</table>';

    return $html;

}



echo build_table($array);

?>

....Controllers/SupermarketsController:

<?php

namespace app\controllers;

use yii\web\Controller;

use yii\data\Pagination;
use app\models\Supermarkets;
class SupermarketsController extends Controller
{
    public function actionIndex()
    {
        $query = supermarkets::find();

        $pagination = new Pagination([
            'defaultPageSize' => 20,
            'totalCount' => $query->count(),
        ]);

        $supermarkets = $query->orderBy('Name')
            ->offset($pagination->offset)
            ->limit($pagination->limit)
            ->all();

        return $this->render('index', [
            'supermarkets' => $supermarkets,
            'pagination' => $pagination,
        ]);
    }
}

I need the user to be able to filter the table or search its fields by one or more attribute. How can I do this?

Answer 1

Instead of trying to reinvent the wheel you can simply use the Yii provided CGridView widget. It has the sorting and filtering functionality. Check the documentation and you will find that there are lot of configurations you can play with. Following code snippet uses minimum configuration.

.../views/supermarkets/index.php:

    <?php
    $this->widget('zii.widgets.grid.CGridView', array(
        'id' => 'supermarkets-grid',
        'dataProvider' => $model->search(),
        'filter' => $model,
        'columns' => array(
            'name',
            'location',
            'telephone',
            'fax',
            'website'
        ),
    ));
    ?>

Implement search() function in Supermarkets model.

public function search()
{

    $criteria=new CDbCriteria;

    $criteria->compare('name',$this->name,true);
    $criteria->compare('location',$this->location,true);
    $criteria->compare('telephone',$this->telephone,true);
    $criteria->compare('fax',$this->fax,true);
    $criteria->compare('website',$this->website,true);

    return new CActiveDataProvider(get_class($this), array(
        'criteria'=>$criteria,
        'sort'=>array(
            'defaultOrder'=>'name ASC',
        ),
        'pagination'=>array(
            'pageSize'=>20
        ),
    ));
}

Controllers/SupermarketsController:.

public function actionIndex() {
    $model =new Supermarkets('search');
    if(isset($_GET['Supermarkets']))
        $model->attributes =$_GET['Supermarkets'];

    return  $this->render('index', array('model'=>$model));
}

Answer 2

The right and better way to organize all queries and filtering in your Model class rather than in controller.

You should use findAll() rather than find() - to find all records that meet your criteria. find() - limited to return only one record.

You could do what you need in different ways:

1. Just make string SQL-criteria to search through necessary attributes:

   findAll('Name = "Supermarket" OR Telephone = "99988899"')

// searches supermarket with name "Supermarket" or with telephone - "99988899" (without qoutes)

2. Good Yii way use CDbCriteria:

$criteria = new CDbCriteria(); // add any atributes that you want to filter $criteria->addCondition('Name = "Supermarket"'); $criteria->addCondition('Telephone = "99988899"','OR');

and use findAll like findAll($criteria).

Finally - just read the Yii manual about Models and working with ActiveRecord. It's documentation is quite good and easy to understand.