Parametrized left join in Rails 7 - select records that are null or true-CodePudding

This code doesnt do what I want:

joins(
  'LEFT OUTER JOIN cats ON owners.id = cats.owner_id AND "cats"."hungry" IS FALSE'
).where(
  'cats.hungry IS FALSE OR cats.hungry IS NULL'
)

There are 3 owner records: A, B & C

There are two cat records: Cat-A (hungry), Cat-B (not-hungry)

I want to segment my owners into 2 groups:

I want owners with:

1.) Owners with cats that ARE hungry. 2.) No cat linked (ie cats.hungry IS NULL) - or linked cat that is not hungry

1.) Owner-A has a hungry cat: cat-A

2.) Owner-B's cat is not hungry (cat-B) & Owner-C has no cat.

I thought the left-join above would get me - cats where there is a join but only joins where the cat is NOT hungry.

CodePudding user response：

you need to remove the second ON condition, so that evety owner with or without cat is present, and only the where lause decides which rows must be removed

joins(
  'LEFT OUTER JOIN cats ON owners.id = cats.owner_id'
).where(
  'cats.hungry IS FALSE OR cats.hungry IS NULL'
)