CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresql
moveson
moveson

Reputation: 5213

Rails scope for parent records that do not have particular child records

I have a parent model Effort that has_many split_times:

class Effort
  has_many :split_times
end

class SplitTime
  belongs_to :effort
  belongs_to :split
end

class Split
  has_many :split_times
  enum kind: [:start, :finish, :intermediate]
end

I need a scope that will return efforts that do not have a start split_time. This seems like it should be possible, but so far I'm not able to do it.

I can return efforts with no split_times with this:

scope :without_split_times, -> { includes(:split_times).where(split_times: {:id => nil}) }

And I can return efforts that have at least one split_time with this:

scope :with_split_times, -> { joins(:split_times).uniq }

Here's my attempt at the scope I want:

scope :without_start_time, -> { joins(split_times: :split).where(split_times: {:id => nil}).where('splits.kind != ?', Split.kinds[:start]) }

But that doesn't work. I need something that will return all efforts that do not have a split_time that has a split with kind: :start even if the efforts have other split_times. I would prefer a Rails solution but can go to raw SQL if necessary. I'm using Postgres if it matters.

Upvotes: 2

Views: 416

Answers (1)

jpn
jpn

Reputation: 796

You can left join on your criteria (i.e. splits.kind = 'start') which will include nulls (i.e. there was no matching row to join). The difference is that Rails' join will by default give you an inner join (there are matching rows in both tables) but you want a left join as you need to check that there is no matching row on the right table.

With the results of that join you can group by event and then count the number of matching splits - if it's 0 then there are no matching start splits for that event!

This might do the trick for you:

scope :without_start_time, -> { 
  joins("LEFT JOIN split_times ON split_times.effort_id = efforts.id").
    joins("LEFT OUTER JOIN splits ON split_times.split_id = splits.id " \
          "AND splits.kind = 0").
    group("efforts.id").
    having("COUNT(splits.id) = 0")
}

Upvotes: 2

Related Questions