CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Daniel Bonnell
Daniel Bonnell

Reputation: 4997

ActiveRecord find records from association with no associations

I have a simple many-to-many relation between two models and a join table. In my Rails controller, I have an ActiveRecord::Relation object called job_descriptions which is initialized as JobDescription.all and then passed through flow controls to apply additional where statements. Here are my models:

class JobDescription < ActiveRecord::Base
    has_many :job_description_experience_levels
    has_many :experience_levels, through: :job_description_experience_levels
end

class JobDescriptionExperienceLevel < ActiveRecord::Base
    belongs_to :job_description
    belongs_to :experience_level
end

class ExperienceLevel < ActiveRecord::Base
    has_many :job_description_experience_levels
    has_many :job_descriptions, through: :job_description_experience_levels
end

I'm trying to write a query for job_descriptions to select only those instances that do not have an experience_level association. How can I do this?

I've tried variations of ob_descriptions.includes(:job_descriptions).where(job_description_experience_levels: {job_description_id: nil}) and job_descriptions.where('id NOT IN (SELECT DISTINCT(job_description_id) FROM job_description_experience_levels)') but without success.

Most of the resoures I've been able to find on this subject work by calling a query on the model itself (e.g. JobDescription.where(something)) rather than on an existing relation object.

Thanks!

Edit #1

I managed to solve this on my own before madyrockss answer, but I think that answer is more semantic than my solution. Here is my solution anyways in case it helps someone down the line.

job_descriptions_with_experience_levels = job_descriptions.joins(:experience_levels)
job_descriptions = job_descriptions - job_descriptions_with_experience_levels

It works by finding all the job_descriptions with experience_levels and then subtracting that relation from job_descriptions to get the ones without any experience_levels.

Upvotes: 0

Views: 546

Answers (1)

mandar.gokhale
mandar.gokhale

Reputation: 1875

If job_description do not have any experience_level there will be no entry present for that job_description in job_description_experience_levels table. So you can do something like following :

job_desc_ids_with_exp_level = JobDescriptionExperienceLevel.pluck(:job_description_id).uniq

job_descriptions = job_descriptions.where.not(id: job_desc_ids_with_exp_level)

Upvotes: 1

Related Questions