How to write Rails scope for this SQL query

Question

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 SlotAlteration 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 Slot 
  left outer join SlotAlteration a 
  on s.id = a.slot_id 
  where a.action = 1
)
order by s.id;

Alterr is SlotAlteration table

This is sql structure and query for visual representation.

https://dbfiddle.uk/yejKnaAi

My Slot model has association has_many :alterations

  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

Answer 1

I would add a dedicated has_many association for alternations with action = 1. Which means the associations might look like this:

# in app/models/slot.rb
has_many :alternations, class_name: 'SlotAlternation'
has_many :action_1_alternations, -> { where(action: 1) }, class_name: 'SlotAlternation'

Then you should be able to query for records without such an associated record.

Slot
  .where(time_off_id: nil)
  .where.missing(:action_1_alternations)

Note that the missing query method was introduced in Ruby on Rails 6.1 and is not available in Rails versions prior 6.1.