CODEWITHSUNDEEP
ruby-on-rails
ZK Zhao
ZK Zhao

Reputation: 21513

How to find records missing associated records in has_many through association with Active Record?

Let'say, we have "Topic - Relationship - Category".

That is, Topic has_many categories through relationship.

I think its very easy to get the topics that with a category

  #Relationship  Model
  Topic_id: integer
  Category_id: integer

  @topics=Topic.joins(:relationships)

But, not every topic has a category. So how do we retrieve the topic which has no category? Is there a minus query?

Perhaps it looks like @topics=Topic.where('id NOT IN (?)', Relationship.all) I find it in activerecord equivalent to SQL 'minus' but not sure about this solution.

Upvotes: 6

Views: 4117

Answers (3)

tenzin dorjee
tenzin dorjee

Reputation: 326

For newer Rails (6.1 and above) we can do something like

Topic.where.missing(:categories)

Find records missing an association with where.missing

Upvotes: 1

Blair Anderson
Blair Anderson

Reputation: 20171

I was looking for the simplest answer, which i think is to use includes.

topics = Topic.includes(:relationships).where(relationships: {id: nil})

Another way, which is more correct and gets you thinking SQL is LEFT OUTER JOINS.

Topic.joins("LEFT OUTER JOINS relationships ON relationships.topic_id = topics.id")
     .where(relationships: {id: nil})

Upvotes: 6

MrTheWalrus
MrTheWalrus

Reputation: 9700

Would be better as a relation, really. Think this would work:

@topics = Topic.joins('left join relationships on relationships.topic_id = topics.id').where('relationships.category_id is null')

Or this:

@topics = Topic
    .joins('left join relationships on relationships.topic_id = topics.id join categories on categories.id = relationships.category_id')
    .group('topics.id').having('count(categories.id) = 0')

Upvotes: 11

Related Questions