Rails where condition with OR is NULL inside

November 22, 2022

I want to get all the records where disbursed at is either NULL or within a timeframe. How can I add a or where disbursed_at IS NULL to the condition inside the where?
My actual query is way longer, I wouldn’t want to repeat it all using .or

Investor.left_join(:disbursements).where(disbursements: { disbursed_at: year_period })

>Solution :

Generally speaking you can add an OR column_X IS NULL condition by providing an Array, containing at least one nil element, as part of the Hash arguments.

For Example:

Investor
  .left_join(:disbursements)
  .where(disbursements: { disbursed_at: [year_period,nil] })

Assuming year_period is a Range this should result in

SELECT 
  investors.* 
FROM 
  investors 
  LEFT OUTER JOIN disbursements ON disbursements.investor_id = investors.id
WHERE 
  disbursements.disbursed_at BETWEEN xxxxx AND xxxxx 
  OR disbursements.disbursed_at IS NULL