Finding null values with .whereIn

user_10604378 0
Viewed 2332

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?

knex.js sql
2 Answers

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.

user_1226430

More optimized I will do

   knex('discounts')
   .where('product_code', productCode)
   .where((builder1) => {
        builder1
          .whereIn('account_code', customer)
          .orWhereNull('account_code');
      });
  })
.then();

Was added inline where and .orWhere('account_code', null); was replaced withorWhereNull. Tips if you select * is not need to write select, default query builder is select. Inside whereIn array values can be one/multiple variables with null values, knex not translate whereIn to =>whereIn or where Is NOT NULL

user_32044730
Related