How to filter null values with Kartik grid and select2 filter?

Question

I'm trying to add a custom option to my select2 filter (kartik grid). This option is meant to select all rows where something_id=null. It's a simple crud generated grid, modified to use kartik grid and select2 filter instead of yii2 default grid/filter.

I tried doing this in my table2_id column:

'filter' => [null => 'Nothing'] + Arrayhelper::map(Something::find()->orderBy('id)->asArray()->all(), 'id', 'thing')

But this new "null" option is not showing in the menu. I assume for select2 null just mean no filter and select2 just shows the placeholder for that. I can't find any option in select2 documentation to modify this behavior. The generated html for this option is:

<option value="" selected="" data-select2-id="5">Nothing</option>

But the css has the property visibility:hidden;. So I tried using:

'filter' => [0 => 'Nothing'] + Arrayhelper::map(Something::find()->orderBy('id)->asArray()->all(), 'id', 'thing')

But I can't manage to properly translate 'something_id'=>0 into a 'something_id'=>null condition neither in the controller or search model. Tried many things but everything failed at some point and I don't think my code deserves to be posted here, also I don't keep a copy of every failed piece of code so I'd like to ask what is the correct way of doing this from scratch. Thank you.

How about the custom query for data provider that runs the `null` filter when `id` is `0`? — Bizley, Aug 10 '20 at 11:13
``` if(!is_null($this->something_id)){ if ( $this->something_id == 0 ) { $query->andWhere(['something_id' => null]); }else{ $query->andFilterWhere(['something_id' => $this->something_id]); } } ``` — user1675094, Aug 10 '20 at 11:45
But it fails when I do exactly the same with a second column. If I only select "nothing" from "wathever" column filter, I always got: ```SELECT COUNT(*) FROM "table" WHERE ("something_id" IS NULL) AND ("wathever_id" IS NULL)``` Even if "something_id" is null (and not 0) in the search model when debuging this request. — user1675094, Aug 10 '20 at 11:46
Even if I try this ugly piece of code I have the same result when `$this->something_id` is null: ``` if(!is_null($this->something_id)){ if ( $this->something_id == 0 ) { $query->andWhere(['somethign_id' => null]); }else{ $query->andFilterWhere(['something_id' => $this->something_id]); } }else{ $query->andFilterWhere(['something_id' => $this->something_id]); } ``` — user1675094, Aug 10 '20 at 11:53
I tried many things and for one reason or another I never got it. And yes, at least for this I don't care about writing nice code or fully understanding how yii2 woks. It's just old code I need to update and hopefully forever forget. — user1675094, Aug 10 '20 at 11:57

score 1 · Answer 1 · answered Aug 10 '20 at 15:21

Well, my answer got deleted because I made a joke. Here is the very very serious version of my answer.

// WatheverSearch.php
    if ( $this->something_id === '0' ) {
        $query->andWhere(['something_id' => null]);
    }else{
        $query->andFilterWhere(['something' => $this->something]);
    }

How to filter null values with Kartik grid and select2 filter?

1 Answers1