I have an issue to get rows from database by multiple conditions.
I have a table Slot and SlotAlteration.

The slot has a time_off_id field.

SlotAlteration has two columns, slot_id and action. Action can be 0 or 1. Each slot can have up to two entries in the SlotAlteration table with action 0 and 1 (not 0 and 0, and not 1 and 1) or have no connections to SlotAdjustment at all.

Query i need to convert into rails scope is
select *

from Slot s

where time_off_id is null

and not exists (select 1 from Alterr a where s.id = a.slot_id and a.action = 1)

Alterr is SlotAlteration table

This is sql structure and query for visual representation.

https://dbfiddle.uk/yejKnaAi

  scope :free, -> {
    left_joins(:alterations)
      .where(time_off_id: nil)
      .where.not(alterations: { action: 1 })
  }

I tried that, but this is not select slots that are haven’t alterations