how to use not null condition in YII2

Question

Hi i want to use not null condition in my yii2 query how should i use that. i don't want city and state null.

My query is

$query = new Query;             
      $query->select('ID, City,State,StudentName')                                  
                                ->from('student')                               
                                ->where(['IsActive' => 1])                                                                                                          
                                ->orderBy(['rand()' => SORT_DESC])
                                ->limit(10);                                
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'pagination' => false,
       ]);

Which of the fields should not be null? – Oldskool Apr 22 '15 at 11:38 — Oldskool, Apr 22 '15 at 11:38
i don't want city and state null. – Vikram Pote Apr 22 '15 at 11:46 — Vikram Pote, Apr 22 '15 at 11:46

score 87 · Accepted Answer · answered Apr 22 '15 at 11:55

You can use the not operator combined with the fields that should not be null to generate a IS NOT NULL SQL statement. Like this:

$query = new Query;             
$query->select('ID, City,State,StudentName')
      ->from('student')                               
      ->where(['IsActive' => 1])
      ->andWhere(['not', ['City' => null]])
      ->andWhere(['not', ['State' => null]])
      ->orderBy(['rand()' => SORT_DESC])
      ->limit(10);

Also check the examples in the documentation.

That does not generate "IS NOT NULL", that generates "NOT (City IS NULL)". @mariovials answer is the correct one here. — jurchiks, Apr 13 '18 at 14:23

mariovials · Answer 2 · 2020-12-14T21:25:19.663

38

->where(['IS NOT', 'column', null]);

get

WHERE column IS NOT NULL

You can also use, it is faster to type

->where('column IS NOT NULL')

In complex query

->where(['AND',
  'column1 IS NOT NULL', // works
  ['IS NOT', 'column2', null], // works
  ['column3' => $value],
)

edited Dec 14 '20 at 21:25

answered Jul 21 '17 at 20:52

mariovials

782
9
12

arogachev · Answer 3 · 2016-05-13T04:22:34.523

13

One of the options will be:

$query = new Query;             
$query->select('ID, City,State,StudentName')
    ->from('student')
    ->where(['IsActive' => 1])
    ->andWhere(['<>', 'City', null])
    ->andWhere(['<>', 'State', null])
    ->orderBy(['rand()' => SORT_DESC])
    ->limit(10);

Check official docs for where.

edited May 13 '16 at 04:22

answered Apr 22 '15 at 11:52

arogachev

33,150
7
114
117

1

You have missing "," after the '<>' – llioor May 08 '16 at 12:45
@llioor Thanks, fixed. BTW, you can suggest edit too. – arogachev May 08 '16 at 12:51
1

I can't because it is only 1 char to change. TW you forgot "," in the previous row as well. – llioor May 12 '16 at 21:43
5

This query is wrong. In SQL, you cannot use the comparison operators to compare NULL values. The proper way is to use "IS NULL" or "IS NOT NULL" conditions. – André Sousa Dec 12 '19 at 23:07

Mirocow · Answer 4 · 2016-08-27T23:08:53.733

7

    $items = BadOffer::find()->where(['OR',
                                               ['IS', 'moderator_id', (new Expression('Null'))],
                                               ['moderator_id' => $user->id],
    ]);

edited Aug 27 '16 at 23:08

answered Jan 16 '16 at 14:11

Mirocow

340
3
5

Ashok Kumar · Answer 5 · 2021-06-23T08:42:47.653

5

In Yii2, we can use any of the queries below to verify the null condition,

->andWhere(['NOT', ['city' => null]]);

or

->andWhere(['<>', ['city' => null]]);

or

->andWhere('city IS NOT NULL');

edited Jun 23 '21 at 08:42

answered Oct 05 '17 at 13:11

Ashok Kumar

326
2
8

score 4 · Answer 6 · edited Oct 04 '17 at 04:00

4

use ->andWhere(['not', ['State' => null]]) or ->andWhere(['is not', 'State', null]);

Do no use :

andFilterWhere, as the null will make this filter be ignored
->andWhere(['<>', 'State', null]) as it form query AND State <> null

edited Oct 04 '17 at 04:00

answered Oct 04 '17 at 03:28

Stephen Tang

66
3

score 3 · Answer 7 · answered Feb 17 '18 at 13:28

3

How to select non-empty columns in MySQL?

use LENGTH :

SELECT col1
FROM table
WHERE LENGTH(col1) > 0

Yii2 :

->andWhere(['>', 'LENGTH(col1)', 0])

answered Feb 17 '18 at 13:28

sj59

2,072
3
22
23

score -3 · Answer 8 · answered Sep 20 '18 at 11:52

-3

This work for me, but only when pass null as string

->andFilterWhere(['<>', '`city`', 'null']);

answered Sep 20 '18 at 11:52

atrichkov

450
3
6

1

This may work only by accident - it create condition like `city <> 'null'`which may ignore null values (although it may depend on DBMS) but will also ignore fields with value `null` as string. – rob006 Sep 21 '18 at 07:41

how to use not null condition in YII2

8 Answers8