How to find records, missing related records in has_many through communication with Active Record?

Question

Come on, we have a "Topic - Relationships - Category".

That is, the topic has_many categories through relationships.

I think it’s very easy to get topics that are 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 get a topic that does not have a category? Is there a minus request?

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

It would be better as an attitude, actually. Think this will work:

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

Or that:

 @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

Try this, he selects only the topic in which the Relation category has a length of zero.

 @topics = Topic.all.select {|t| t.categories.length == 0 } 

Answer 3

I was looking for the simplest answer that I think should use includes .

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

Another way that is more correct and makes you think SQL is LEFT OUTER JOINS .

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