CakePHP: Find where field is not null

Question

I need to select all rows where User.site_url is not null. It's simple enough to do this in a regular MySQL query but how is this done in CakePHP?

The manual mentions the following:

array ("not" => array (
        "Post.title" => null
    )
)

I have tried the following but it's still returning everything

$this->User->find('all', array('conditions' => array('not' => array('User.site_url'))));

Answer 1

I think this is what you mean:

$this->User->find('all', array( 
    'conditions' => array('not' => array('User.site_url' => null))
));

Answer 2

Your just missing the null

$this->User->find('all', array('conditions' => array('not' => array('User.site_url'=>null))));

Answer 3

In Cake, a WHERE condition is constructed from 'conditions' element by joining keys and values. That means that you can actually skip providing the keys if you like. E.g.:

array('conditions' => array('User.id'=>1))

is completely equivalent to

array('conditions' => array('User.id = 1'))

Essentially, you can solve your problem by just this:

$this->User->find('all', array('conditions' => array('User.site_url IS NOT NULL')));

Answer 4

For simple query:

$this->User->find('all', array(
     'conditions' => array(
         'User.site_url IS NOT NULL'
));

For cakephp 3.X

 $table = TableRegistry::get('Users');
 $assessmentComments = $table
      ->find()
      ->where(function (QueryExpression $exp, Query $q) {
            return $exp->isNotNull('site_url');
        })
      ->all();

Answer 5

You can also try this,

$this->User->find('all', array('conditions' => array('User.site_url <>' => null));

This works fine for me..

Answer 6

Please try '' rather than null:

$this->User->find('all', array('conditions' => array('User.site_url <>' => ''));

Answer 7

This work fine for me:

$this->User->find('all', array('conditions' => array('User.site_url !=' => null));

Answer 8

this scope is correct! (ctlockey)

$this->User->find('all', array('conditions' => array('not' => array('User.site_url' =>null))));

However I using with different versions of MySql and MariaDb returned inconstant results. I believe that a little bit of direct sql is not that bad so to ensure the integrity of the return.

Therefore, I did the following:

$Obj->find()->where(['field_a IS NULL', 'field_b IS NOT NULL'])->all();

Answer 9

Its working for me

$this->set('inventory_masters',$this->InventoryMaster->find('all',array('order'=>$orderfinal,'conditions' => array('InventoryMaster.id' => $checkboxid,'not' => array('InventoryMaster.error'=>null)))));