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

Question

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.

Answer 1

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')

Answer 2

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})