How to best check for NULL?

[carbonsam]

I have a basic database schema with 3 tables: Book, Author and BookAuthor. I'm trying to get a count of Books that do not have a related Author.

The SQL query I'm trying to implement to solve this problem is this:

SELECT COUNT(*) from book
LEFT JOIN book_author on book_author.bookId = book.id
WHERE book_author.id is null;

When attempting to build this query I'm not finding any examples of checking for NULL anywhere. What is the best way to do this? Including my failing .where tries and the outcome below:

Book
  .leftJoin(BookAuthor.all) { $0.id.eq($1.bookId) }
  // WHERE clauses I've tried
  .where { #sql("\($1.id) IS NULL") } // Compiles, but value is wrong
  .where { $1.id.eq(nil) } // throws "Generic parameter 'some QueryExpression<Self.QueryValue>' could not be inferred"
  .where { $1.id.eq(UUID(uuidString: "")) } // Compiles, but value is wrong
  // SELECT clauses I've tried
  .select { $1.id.count() }
  .select { $1.id.count(filter: #sql("\($1.id) IS NULL")) }, // Compiles, but value is wrong

Appreciate any help with this!

[stephencelis]

There's an is operator for nil checking. Does this work for you?

Book
  .select { $0.count() }
  .leftJoin(BookAuthor.all) { $0.id.eq($1.bookId) }
  .where { $1.id.is(nil) }

[stephencelis]

As a followup, I would expect this attempt of yours to compile and work:

.where { #sql("\($1.id) IS NULL") } // Compiles, but value is wrong

Can you explain more as to what went wrong and maybe share a failing test case?

[carbonsam]

Thank for your help! I was going crazy not finding anything in the docs or example apps, but I am seeing the is operator docs page now.

After some more testing, it appears the ordering of the statements is important which I hadn't run into until now. The .where { #sql("\($1.id) IS NULL") } line does work and produce the correct result, but only when added after the select, not before like I originally had it. Otherwise the query was returning the count of BookAuthors with NULL ids (which would always be zero) instead of Books with no authors.

Really appreciate your quick response and all the work y'all put into this awesome set of libraries!