Find all record without association with a certain field

Question

My question is very similar to this question: Want to find records with no associated records in Rails 3

But with a twist. Let's use their example and add my problem:

class Person
   has_many :friends
end

class Friend
   belongs_to :person
   attr_accessor :type # this can be 'best' or 'acquaintance'
end

I want to get all people without 'best' friends. The normal query for most cases that I see is to get people without any friends. And that would be:

Person.includes(:friends).where( :friends => { :person_id => nil } )

But that's not what I want. Is there a way to get all people who have no 'best' friends regardless of how many other type of friends they have?

Answer 1

If you are using rails 4.2 which supports negation on queries you could do something like:

Person.includes(:friends).where.not( friends: { type: "best" } )

In any other case:

Person.includes(:friends).where("friends.type != 'best'")

Update

Maybe a little bit off-topic, but you may consider using enum from active record so you can map these kind of things, like:

class Friend
   belongs_to :person
   enum type: {best: 0, acquaintance: 1}
end

Then you could query like this:

Person.includes(:friends).where.not( friends: { type: Friend.types[:best] } )

Which makes it more readable, ruby-friendly and avoids using strings since the value is stored as an integer in the db.

Answer 2

The most straightforward, though not the only nor necessarily the most performant, way to do this is with a NOT EXISTS subquery:

Person.where('NOT EXISTS(SELECT 1 FROM friends WHERE person_id=persons.id AND type=?)', 'best')

You can define this as a scope on Person for easy composition.

Note: I also want to point out that, while Gustavo's solution reads like you expect, it will return anyone who has a friend who's not their best friend (not just people without ANY best friends). This is due to how SQL's where clauses function on a per-row basis, and have trouble asserting over groups or one-to-many relations.