How to use IS NOT NULL in Knex JS
Solution 1
According to the docs, .havingRaw is what you need:
knex("users").groupBy("users.location").havingRaw("users.photo IS NOT ?", [null]);
On the other hand, do a knex.raw at once unless there is any remaining advantage using the builder on this specific case.
Solution 2
Have you tried:
knex("users").whereNotNull("photo").groupBy("location")
Solution 3
The docs have the answers. There is whereNull
, whereNotNull
, havingNull
, havingNotNull
and so on.
From the DOCS:
havingNull — .havingNull(column)
Adds a havingNull clause to the query.
knex.select('*').from('users').havingNull('email')
Outputs:
select * from `users` having `email` is null
havingNotNull — .havingNotNull(column)
Adds a havingNotNull clause to the query.
knex.select('*').from('users').havingNotNull('email')
Outputs:
select * from `users` having `email` is not null
Give it a try using the knex query lab: http://michaelavila.com/knex-querylab/
Comments
-
Akshay Khetrapal almost 2 years
I am trying to create the following query using knex:
SELECT * FROM users group by users.location having users.photo is not null
as follows:
knex("users").groupBy("users.location").having("users.photo", "IS NOT", "Null")
I am getting the following error on this:
The operator IS NOT is not permitted
I've gone through their documentation and couldn't find anything useful.