CODEWITHSUNDEEP
phpyii2
Vikram Pote
Vikram Pote

Reputation: 5579

how to use not null condition in YII2

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,
       ]);

Upvotes: 52

Views: 104749

Answers (8)

Ashok Kumar
Ashok Kumar

Reputation: 336

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');

Upvotes: 5

mariovials
mariovials

Reputation: 786

->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],
)

Upvotes: 38

atrichkov
atrichkov

Reputation: 477

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

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

Upvotes: -3

sj59
sj59

Reputation: 2162

How to select non-empty columns in MySQL?

use LENGTH :

SELECT col1
FROM table
WHERE LENGTH(col1) > 0

Yii2 : 

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

Upvotes: 4

Stephen Tang
Stephen Tang

Reputation: 66

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

Do no use :

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

Upvotes: 4

Mirocow
Mirocow

Reputation: 335

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

Upvotes: 7

arogachev
arogachev

Reputation: 33548

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.

Upvotes: 13

Oldskool
Oldskool

Reputation: 34877

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.

Upvotes: 90

Related Questions