"WHERE column IS NOT NULL" with Kohana v3 Query Builder

Question

Is it possible with Kohana v3 Query Builder to use the IS NOT NULL operator?

The where($column, $op, $value) method requires all three parameters and even if I specify

->where('col', 'IS NOT NULL', '')

it builds and invalid query eg.

SELECT * FROM table WHERE col IS NOT NULL '';

v3 is just fine. and you were so close! All you had to do, was move the NULL to the value argument: `->where('col', 'IS NOT', NULL)` — SpadXIII, Sep 27 '10 at 08:46
Now that v3 has better docs and I've had a chance to get used to it-- I'm enjoying it thoroughly. — MeatFlavourDev, Apr 03 '11 at 10:25

score 24 · Accepted Answer · answered Jan 22 '11 at 23:58

24

The operator is not escaped:

->where('col', 'IS NOT', NULL)

No need to use DB::expr, Kohana already supports what you want.

answered Jan 22 '11 at 23:58

shadowhand

3,202
19
23

score 9 · Answer 2 · answered Sep 26 '10 at 04:16

9

This works with the ORM module and is a little less typing.

->where('col', '!=', NULL);

answered Sep 26 '10 at 04:16

Gerry Shaw

9,178
5
41
45

score 3 · Answer 3 · answered Sep 26 '10 at 00:54

3

Not sure (it's 3 AM right now) but ->where('col', '', DB::expr('IS NOT NULL')) might works.

answered Sep 26 '10 at 00:54

Crozin

43,890
13
88
135

GENIUS. You sir, are a saint. – MeatFlavourDev Sep 26 '10 at 01:19
I think it's better to put the 'IS NOT' as the second argument and the value just being NULL. Just like Gerry answered. Using a DB::expr is nice, but adds unnecessary extra overhead (in this case) – SpadXIII Sep 27 '10 at 08:45
2

Yeah the best solution is ->where('col', 'IS NOT', NULL) – MeatFlavourDev Nov 10 '10 at 19:28

score 0 · Answer 4 · answered Oct 03 '14 at 06:14

The WHERE clause takes 3 arguments, the 1st and 3rd which are always attempted to be converted to the backticks format (i.e. `table`.`field`). As long as you supply the DB::Expr on at least the 3rd argument, you can get away with leaving nothing in 1st and 2nd args and the following should work as well:

->where('', '', DB::Expr('!isNull(col)'));

This is confirmed to work on Kohana 3.2 and above.

score -1 · Answer 5 · answered Sep 27 '10 at 05:30

-1

This should work:

->where('col', '=', NULL);

answered Sep 27 '10 at 05:30

biakaveron

5,493
1
16
20

That's checking of the value is null, OP wanted is not null. – Gerry Shaw Feb 06 '11 at 17:40

"WHERE column IS NOT NULL" with Kohana v3 Query Builder

5 Answers5