why search return same rows in gridview yii2 kartik

Question

I am facing a problem with filtering. I have created a grid view that contain a list of data from related tables. what I'm facing is :

How can I sort data ( I can only sort the data received from the parent table)?
why the search always return same value whatever I'm writing in filters even with Pjax reload but no data change ?

this my view :

<div class="row">
    <div class="col-lg-12">

        <?php

        $ItemSupMod = $ItemSupMod;
        $gridColumns = [
            ['class' => 'yii\grid\SerialColumn'],
//                    'ItemName',
            [
                'attribute' => 'CategoryName',
                'label' => 'Item Name',
                'filter' => true,
                'enableSorting' => true,
//                                'filterType' => GridView::FILTER_TYPEAHEAD,
                'value' => function ($model, $key, $index, $widget) {
                    return "<span>" . ($model->iTEM != null && sizeof($model->iTEM) > 0 && $model->iTEM->pRODUCT != null && sizeof($model->iTEM->pRODUCT) > 0 && $model->iTEM->pRODUCT->sUBCATEGORY != null && sizeof($model->iTEM->pRODUCT->sUBCATEGORY) > 0 && $model->iTEM->pRODUCT->sUBCATEGORY->cATEGORYOFITEM != null && sizeof($model->iTEM->pRODUCT->sUBCATEGORY->cATEGORYOFITEM) > 0 && $model->iTEM->pRODUCT->sUBCATEGORY->cATEGORYOFITEM->categoryOfItemsTrans != null && sizeof($model->iTEM->pRODUCT->sUBCATEGORY->cATEGORYOFITEM->categoryOfItemsTrans) > 0 ? $model->iTEM->pRODUCT->sUBCATEGORY->cATEGORYOFITEM->categoryOfItemsTrans[0]->CATEGORY_OF_ITEM_TRANS : "" ) . "</span> ";
                },
//                                'vAlign' => 'middle',
                'format' => 'raw',
                'width' => '150px',
//                                'noWrap' => true,
                'enableSorting' => true,
            ],
            [
                'attribute' => 'SubCategoryName',
                'label' => 'Item Name',
                'filter' => true,
                'enableSorting' => true,
//                                'filterType' => GridView::FILTER_TYPEAHEAD,
                'value' => function ($model, $key, $index, $widget) {
                    return "<span>" . ($model->iTEM != null && sizeof($model->iTEM) > 0 && $model->iTEM->pRODUCT != null && sizeof($model->iTEM->pRODUCT) > 0 && $model->iTEM->pRODUCT->sUBCATEGORY != null && sizeof($model->iTEM->pRODUCT->sUBCATEGORY) && $model->iTEM->pRODUCT->sUBCATEGORY->subCategoriesTrans != null && sizeof($model->iTEM->pRODUCT->sUBCATEGORY->subCategoriesTrans) > 0 ? $model->iTEM->pRODUCT->sUBCATEGORY->subCategoriesTrans[0]->SUB_CATEGORY_NAME : "" ) . "</span> ";
                },
//                                'vAlign' => 'middle',
                'format' => 'raw',
                'width' => '150px',
//                                'noWrap' => true,
                'enableSorting' => true,
            ],
            [
                'attribute' => 'ProductName',
                'label' => 'Item Name',
                'filter' => true,
                'enableSorting' => true,
//                                'filterType' => GridView::FILTER_TYPEAHEAD,
                'value' => function ($model) {
                    return "<span>" . ($model->iTEM != null && sizeof($model->iTEM) > 0 && $model->iTEM->pRODUCT != null && sizeof($model->iTEM->pRODUCT) > 0 && $model->iTEM->pRODUCT->productsTrans != null && sizeof($model->iTEM->pRODUCT->productsTrans) > 0 ? $model->iTEM->pRODUCT->productsTrans[0]->PRODUCT_NAME : "" ) . "</span> ";
                },
//                                'vAlign' => 'middle',
                'format' => 'raw',
                'width' => '150px',
//                                'noWrap' => true,
                'enableSorting' => true,
            ],
            [
                'attribute' => 'ItemName', 
//                                'sortParam' => 'post-sort',
//            'defaultOrder' => ['ItemName' => SORT_ASC],
                'label' => 'Item Name',
                'filter' => true,
                'enableSorting' => true,
//                                'filterType' => GridView::FILTER_TYPEAHEAD,
                'value' => function ($model) {
            return "<span>" . ($model->iTEM != null && sizeof($model->iTEM) > 0 && $model->iTEM->itemsTrans != null && sizeof($model->iTEM->itemsTrans) > 0 ? $model->iTEM->itemsTrans[0]->ITEM_NAME : "" ) . "</span> ";
        },
//                                'vAlign' => 'middle',
                'format' => 'raw',
                'width' => '150px',
//                                'noWrap' => true,
                'enableSorting' => true,
            ],
            'PRICE',
            [
                'class' => 'kartik\grid\ExpandRowColumn',
                'width' => '50px',
                'value' => function ($model, $key, $index, $column) {
                    return GridView::ROW_COLLAPSED;
                },
                'detail' => function ($model, $key, $index, $column) {
                    $actionSub = SupplierController::actionSub($model->ITEM_SUPPLIER_ID,$index);
                    return Yii::$app->controller->renderPartial('supitems', $actionSub);
                },
                'headerOptions' => ['class' => 'kartik-sheet-style'],
                'expandOneOnly' => true
            ],
        ];
        echo GridView::widget([
            'id' => 'kv-grid-demo',
            'dataProvider' => $dataItemSupplier,
            'filterModel' => $searchModel,
            'resizableColumns' =>true,
            'formatter' => ['class' => 'yii\i18n\Formatter', 'nullDisplay' => ''],
            'columns' => $gridColumns,
            'containerOptions' => ['style' => 'overflow: auto'], // only set when $responsive = false
            'headerRowOptions' => ['class' => 'kartik-sheet-style'],
            'filterRowOptions' => ['class' => 'kartik-sheet-style'],
            'pjax' => true, // pjax is set to always true for this demo
            'bordered' => true,
            'striped' => true,
            'condensed' => true,
            'responsive' => true,
            'hover' => true,
            'persistResize' => false,

        ]);
        ?>
    </div>
</div>

this is my model that contain search :

class ItemsSupplieirs extends \yii\db\ActiveRecord
{

    public $PRODUCT_ID;
    public $CATEGORY_ID;
    public $SUB_CATEGORY;
    public $ItemName;
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'items_supplieirs';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['ItemName','PRICE'], 'safe'],
            [['CURRENCY_ID','PRICE'], 'required'],

            [['ITEM_ID', 'SUPPLIER_ID', 'CURRENCY_ID','PRICE','ITEM_SUPPLIER_ID'], 'integer'],
            [['PRICE', 'COMMISSION'], 'string', 'max' => 45],
            [['COMMISSION_FLAG'], 'string', 'max' => 1],
            [['DISCOUNT'], 'string', 'max' => 2],
            [['ITEM_ID'], 'exist', 'skipOnError' => true, 'targetClass' => Items::className(), 'targetAttribute' => ['ITEM_ID' => 'ITEM_ID']],
            [['SUPPLIER_ID'], 'exist', 'skipOnError' => true, 'targetClass' => Suppliers::className(), 'targetAttribute' => ['SUPPLIER_ID' => 'SUPPLIER_ID']],
            [['CURRENCY_ID'], 'exist', 'skipOnError' => true, 'targetClass' => Currencies::className(), 'targetAttribute' => ['CURRENCY_ID' => 'CURRENCY_ID']],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'ITEM_ID' => Yii::t('app', 'Item  ID'),
            'SUPPLIER_ID' => Yii::t('app', 'Supplier  ID'),
            'ITEM_SUPPLIER_ID' => Yii::t('app', 'Item  Supplier  ID'),
            'PRICE' => Yii::t('app', 'Price'),
            'CURRENCY_ID' => Yii::t('app', 'Currency  ID'),
            'COMMISSION' => Yii::t('app', 'Commission'),
            'COMMISSION_FLAG' => Yii::t('app', 'Commission  Flag'),
            'DISCOUNT' => Yii::t('app', 'Discount'),
            'PRODUCT_ID' => Yii::t('app', 'Products'),   
            'ItemName' =>Yii::t('app', 'Item Name'),   
        ];
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getCriteriaValues()
    {
        return $this->hasMany(CriteriaValues::className(), ['ITEM_SUPPLIER_ID' => 'ITEM_SUPPLIER_ID']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getItemOptions()
    {
        return $this->hasMany(ItemOptions::className(), ['ITEM_SUPPLIER_ID' => 'ITEM_SUPPLIER_ID']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getItemRatingComments()
    {
        return $this->hasMany(ItemRatingComment::className(), ['ITEM_SUPPLIER_ID' => 'ITEM_SUPPLIER_ID']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getItemSupplierTranslations()
    {
        return $this->hasMany(ItemSupplierTranslation::className(), ['ITEM_SUPPLIER_ID' => 'ITEM_SUPPLIER_ID']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getItemsImgs()
    {
        return $this->hasMany(ItemsImgs::className(), ['ITEM_SUPPLIER_ID' => 'ITEM_SUPPLIER_ID']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getITEM()
    {
        return $this->hasOne(Items::className(), ['ITEM_ID' => 'ITEM_ID']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getSUPPLIER()
    {
        return $this->hasOne(Suppliers::className(), ['SUPPLIER_ID' => 'SUPPLIER_ID']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getCURRENCY()
    {
        return $this->hasOne(Currencies::className(), ['CURRENCY_ID' => 'CURRENCY_ID']);
    }

     /**
     * @inheritdoc
     */
    public function scenarios()
    {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params)
    {
        $query = ItemsSupplieirs::find()
         ->joinWith('items','items_trans');

        // add conditions that should always apply here
        $query->where('PRICE LIKE '. $this->PRICE);
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'sort' => ['attributes' => ['ItemName']]
        ]);
        $dataProvider->sort->attributes['ItemName'] = [
            'asc' => ['ItemName' => SORT_ASC],
            'desc' => ['ItemName' => SORT_DESC],
            'label' => $this->getAttributeLabel('ItemName'),
        ];
        $this->load($params);
//
//        if (!$this->validate()) {
//            // uncomment the following line if you do not want to return any records when validation fails
//            // $query->where('0=1');
//            return $dataProvider;
//        }

        // grid filtering conditions

//        $query->andFilterWhere([
//            'ITEM_ID' => $this->ITEM_ID,
//            'SUPPLIER_ID' => $this->SUPPLIER_ID,
//            'ITEM_SUPPLIER_ID' => $this->ITEM_SUPPLIER_ID,
//            'CURRENCY_ID' => $this->CURRENCY_ID,
//            'items_trans.LANGUAGE_ID' => '1',
//        ]);

//            ->andFilterWhere(['like', 'COMMISSION', $this->COMMISSION])
//            ->andFilterWhere(['like', 'COMMISSION_FLAG', $this->COMMISSION_FLAG])
//            ->andFilterWhere(['like', 'DISCOUNT', $this->DISCOUNT])
//             ->andFilterWhere(['like', 'items_trans.ITEM_NAME', $this->PRICE]);
Yii::error("this->ItemName" .$this->ItemName);
//        $query
//        ->andFilterWhere(['like', 'DISCOUNT', $this->DISCOUNT]);
//            'items_trans.ITEM_NAME'=>$this->ItemName,

//        $models = $dataProvider->getModels();
//        if($models!=null && sizeof($models)>0){
//           Yii::error("sizeof(models) : " .sizeof($models)); 
//        }
        return $dataProvider;
    }
}

this what I send from controller :

$SearchItemsSupplieirs = new \app\models\ItemsSupplieirs;
$dataProvider = $SearchItemsSupplieirs->search(Yii::$app->request->getQueryParams('ItemsSupplieirs'));
return $this->render('listitems', [
                            'dataProvider' => $dataProvider,
                            'searchModel' => $SearchItemsSupplieirs,
                             ]);

@SimonBosley Here we are...please i need your help...im stuck in this part . — Ahmad Sabra, Mar 23 '17 at 14:23
I'm just moderating, thanks for editing your question, it should be clearer for other users now. — Simon Bosley, Mar 23 '17 at 14:52
$SearchItemsSupplieirs = new \app\models\ItemsSupplieirs; $SearchItemsSupplieirs->load(Yii::$app->request->get('ItemsSupplieirs')); $dataProvider = $SearchItemsSupplieirs->search(Yii::$app->request->getQueryParams('ItemsSupplieirs')); 'dataProvider' => $dataProvider, 'searchModel' => $SearchItemsSupplieirs, — Ahmad Sabra, Mar 23 '17 at 15:23

score 0 · Answer 1 · answered Mar 23 '17 at 16:10

Ahmad:

The first thing I noticed is that in your search method you're not actually querying of any of the incoming search parameters. That's why you are getting the same results no matter what you do in the grid.

You are loading the search arguments via the $this->load($params) but you're not adding to your query statement before creating the ActiveDataProvider.

Here is an example of how the search method should be constructed:

public function search($params)
{               
    $query = Client::find()
                ->joinWith(['venues','events'])                     
                ->orderBy('name');

    $this->load($params);                                                               

    if(strlen($this->status) > 0){
        if($this->status <> -1)
            $query->andWhere(['status' => $this->status]);
    }

    if(!empty($this->name)){
        $query->andFilterWhere(['like','client.name',$this->name])
                ->orFilterWhere(['like','venue.name',$this->name]);
    }

    if(!empty($this->status)){
        if($this->status != -1){
            $query->andWhere(['status' => $this->status]);
        }
    }       

    if(!empty($this->address1)){
        $query->andFilterWhere(['like','address1',$this->address1])
                ->orFilterWhere(['like','address2',$this->address1])
                ->orFilterWhere(['like','city',$this->address1])
                ->orFilterWhere(['like','state',$this->address1])
                ->orFilterWhere(['like','zip',$this->address1]);    
    }   

    if(!empty($this->venues)){
        $query->andWhere(['like','venue.name',$this->venues]);
    }

    if(!empty($this->staffing_manager)){
        $query->andWhere(['venue.staffing_manager_id' => $this->staffing_manager]);
    }   

    if(!empty($this->sales_rep)){
        $query->andWhere(['venue.sales_rep_id' => $this->sales_rep]);               
    }                                                   

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'pagination' => false,
    ]);

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

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

    return $dataProvider;
}

why search return same rows in gridview yii2 kartik

1 Answers1