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


Solution:1

I think this is what you mean:

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


Solution:2

Your just missing the null

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


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


Solution:4

You can also try this,

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

This works fine for me..


Solution:5

This work fine for me:

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


Solution:6

Please try '' rather than null:

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


Solution:7

For simple query:

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

But if you want to do it in join the queries then you need to right join it:

$this->User->find('all', array(       'conditions' => array(           'User.site_url IS NOT NULL'       ),        'joins' => array(              array(                      'table' => $this->getTableName('default', 'table_name'),                      'alias' => 'ModelName',                      'type' => 'RIGHT',                      'conditions' => array(                          'ModelName.id = User.join_field_id',                          'ModelName.field IS NOT NULL',   //<= this condition can be used on main (top) condition                       ),                   ),    ));  


Solution:8

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


Solution:9

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

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »