Today I encountered an issue where 2 + 2 was only equalling 3 when fetching data using ActiveRecord in Rails.
def permanent_staff People.staff_like.where( contract_type: Settings.permanent_staff_contract_types ) end def non_permanent_staff People.staff_like.where.not( contract_type: Settings.permanent_staff_contract_types ) end People.staff_like.count == permanent_staff.count + non_permanent_staff.count => false # (huh!?)
After a little bit of digging I realised some People were being excluded because they didn’t have a contract type:
People.staff_like.count == permanent_staff.count + non_permanent_staff.count + People.staff_like.where(contract_type: nil).count => true
I assumed those with a
NULL contract type would be included in
non_permanent_staff but it turns out that’s not how databases work (at least not how PostrgreSQL or Sequel Server work, but I’m pretty sure this is “standard” behaviour). As soon as the query optimiser sees
contract_type NOT IN (... it filters out any results that don’t have a
My solution doesn’t feel like idiomatic Ruby or Rails but I solved it with the following method:
def non_permanent_staff People.staff_like.where.not( contract_type: Settings.permanent_staff_contract_types ).or(People.staff_like.where(contract_type: nil)) end
The generated SQL is a bit ugly (the
staff_like scope conditions gets included twice) but I’m sure the database can optimise that… better the DB deal with the data collection than Ruby.
If this method proves confusing to future-me I may just put a
NOT NULL constraint on
contract_type in the database and be done with it. It’s a string we’re importing from a legacy system so the default value would just be “.