CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-5
user2954587
user2954587

Reputation: 4861

Query for empty has_many through

How can I query a has_many :through to see which records have an empty association on the other side? (I'm using rails 5)

class Specialty
  has_many :doctor_specialties
  has_many :doctor_profiles, through: :doctor_specialties

class DoctorProfile
  has_many :doctor_specialties
  has_many :specialties, through: :doctor_specialties

class DoctorSpecialty
  belongs_to :doctor_profile
  belongs_to :specialty

I can do this by enumerating over Specialty but I'd like to do it in a SQL query. 

Specialty.includes(:doctor_profiles).all.select{|x| x.doctor_profiles.length == 0 }

Upvotes: 3

Views: 539

Answers (3)

Andrey Deineko
Andrey Deineko

Reputation: 52347

Specialty.includes(:doctor_profiles).where(doctor_profiles: { id: nil })

See Active Record Query Interface for more info on AR querying.

Since you're on Rails >= 5, you could use left_outer_joins (thx @gmcnaughton):

Specialty.left_outer_joins(:doctor_profiles).where(doctor_profiles: { id: nil })

Upvotes: 5

dnsh
dnsh

Reputation: 3633

You can also achieve this using following query.

Specialty.where.not(id: DoctorSpecialty.select(:speciality_id))

Above statement will create a query inside query. No need for table joins.

Upvotes: 1

ilan berci
ilan berci

Reputation: 3881

Speciality.joins('left join doctor_profiles on doctor_profiles.speciality_id = specialities.id').where(doctor_profiles: {id: nil})

Upvotes: 0

Related Questions