CODEWITHSUNDEEP
ruby-on-railsactiverecord
leemour
leemour

Reputation: 12073

Find records that don't have has_many :through association

I have 3 tables:
advert.rb

class Advert < ActiveRecord::Base
  has_many :postings, dependent: :destroy
  has_many :sites, through: :postings
end

posting.rb

class Posting < ActiveRecord::Base
  belongs_to :advert
  belongs_to :site
end

site.rb

class Site < ActiveRecord::Base
end

I need to find adverts that do not have postings linking them to all sites (adverts that have at least one site that they are not linked to). What query should I write? I tried 

Advert.joins(
  "LEFT JOIN postings ON postings.advert_id = adverts.id " +
  "JOIN sites ON sites.id = postings.site_id").
  group('adverts.id').having('COUNT(sites.id) = 0')

but it doesn't seem to work.

My spec:

site1 = create :site
site2 = create :site
advert1 = create :advert
advert2 = create :advert
create(:posting, advert: advert1, site: site1)
create(:posting, advert: advert1, site: site2)
create(:posting, advert: advert2, site: site1)
expect(Advert.not_posted).to match_array([advert2])

Upvotes: 2

Views: 331

Answers (3)

Hieu Pham
Hieu Pham

Reputation: 6692

adverts that have at least one site that they are not linked to

So the same logic will be:

  • A = Find the postings which belong to all sites
  • Final result = the adverts which don't relate to A

My query like this:

a_query = %Q{
  SELECT advert_id
  FROM postings
  GROUP BY advert_id
  HAVING COUNT(DISTINCT site_id) = #{Site.count}
}

result = Advert.where("id NOT IN (#{a_query})")

Upvotes: 1

Andrey Deineko
Andrey Deineko

Reputation: 52367

Here is your query:

Advert.includes(postings: :sites).where(sites: { id: nil })

You would probably want to read through ActiveRecord querying guide.

Upvotes: 2

Long Nguyen
Long Nguyen

Reputation: 373

Do you want to get all Adverts do not any Site? Make it easy like this

Advert.where.not(id: Posting.select(:advert_id))

Upvotes: 0

Related Questions