CakePHP: Find where field is not null
PhpCakephpPhp Problem Overview
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'))));
Php Solutions
Solution 1 - Php
I think this is what you mean:
$this->User->find('all', array(
'conditions' => array('not' => array('User.site_url' => null))
));
Solution 2 - Php
Your just missing the null
$this->User->find('all', array('conditions' => array('not' => array('User.site_url'=>null))));
Solution 3 - Php
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')));
Solution 4 - Php
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();
Solution 5 - Php
You can also try this,
$this->User->find('all', array('conditions' => array('User.site_url <>' => null));
This works fine for me..
Solution 6 - Php
Please try '' rather than null:
$this->User->find('all', array('conditions' => array('User.site_url <>' => ''));
Solution 7 - Php
This work fine for me:
$this->User->find('all', array('conditions' => array('User.site_url !=' => null));
Solution 8 - Php
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();
Solution 9 - Php
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)))));