IS NOT NULL trouble in Nette/Database

Sooner or later, you’ll find yourself in a need of filtering out NULL values along with some other criterion. You’ve tried ->where(array('something' => $value, 'other_thing IS NOT NULL')) and got an error. I’m sure you’ve spent considerable time looking for some kind of negation that would work with NULL and I bet you’ve even tried to stick NSqlLiteral somewhere in there. I’m here to rescue you.

Two solutions:

  1. Split your where into two calls.
    This will cause where() to detect correctly that there are no arguments to the condition and allow you to use 'other_thing IS NOT NULL'

    1. $jobs = $database->table(‘job’)
    2.   ->where(‘project_id’, $projectId)
    3.   ->where(‘vendor_id IS NOT NULL’);
  2. Begin with NOT
    You can negate the whole condition by placing NOT at the beginning. 

    1. $jobs = $database->table(‘job’)
    2.   ->where(array(
    3.     ‘project_id’ => $projectId,
    4.     ‘NOT vendor_id’ => null
    5.   ));

I suppose this applies to the original database library NotORM as well. Haven’t tested it though.


Leave a Reply