CODEWITHSUNDEEP
sqlrubypostgresqlactiverecord
ichigolas
ichigolas

Reputation: 7735

postgresql: select records where ALL associated records match condition

I have:

class A < ActiveRecord::Base
  has_many :abs
  has_many :bs, through: :abs
end

class AB
  belongs_to :a
  belongs_to :b
end

class B < ActiveRecord::Base
  has_many :abs
  has_many :as, through: :abs
  # and has boolean db field :matches
end

So I want to implement an scope for A that retrieves the As where all it's associated Bs matches=true. Normally, I would do something like:

A.joins(:bs).where(bs: { matches: true })

But this will retrieve As where at least one b matches the conditions, not all.

Ideas?

Upvotes: 3

Views: 677

Answers (1)

user229044
user229044

Reputation: 239551

I would instead look for records where there are zero instances of matches: false. I'd probably use a sub-query, something like...

A.joins(:bs).where('(select count(*) from bs where matches = false) = 0')

But there might be a more ActiveRecord way of doing it.

Upvotes: 3

Related Questions