Finding null values with .whereIn

Question

I have a built a query with Knex as follows:

knex('discounts')
  .where((builder) => {
    builder
      .where('product_code', productCode)
      .where((builder1) => {
        builder1
          .andWhere('account_code', customer)
          .orWhere('account_code', null);
      });
  })
.select('*');

Everything works fine, but I felt like the .where statement is too long so tried to use the .whereIn function, and that's when I realized it doesn't work:

knex('discounts')
  .where((builder) => {
    builder
      .where('product_code', productCode)
      .whereIn('account_code', [customer, null]);
  })
.select('*');

I am aware that we cannot use null with an IN in raw SQL and it should be done like so:

WHERE 
(columnName IN ('value1', 'value2', 'value3') OR columnName IS NULL)

My question: is my initial query the only way to achieve this or is there any alternative way to use .whereIn with a null when using Knex?

Answer 1

You're right about the first query, the extra inline function is unnecessary. There's only really one grouping required here, if I understand your requirement correctly. So this is fine:

db("discounts")
  .where("product_code", productCode)
  .where(qb =>
    qb.where("account_code", customer).orWhereNull("account_code")
  )

This generates the following SQL:

SELECT * FROM "discounts"
  WHERE "product_code" = ?
  AND ("account_code" = ? OR "account_code" IS NULL)

You can use .andWhere if you want to be more explicit, but it doesn't change the generated SQL:

  .where("product_code", productCode)
  .andWhere(qb =>

By the way (if you haven't already discovered) you can review what SQL will be executed for any Knex chain by adding .debug() to it. This can be handy if you're refactoring, or just curious.