Rails Active Record where child IS NULL

Question

I have a model User that has_one Email and has_many Friends

If the email is a field in the User model, I can use the following to get all users who's email is nil: User.where("email IS NULL")

However, I don't know how I can do this if the email is in a separate Email model where this model belongs_to User with a user_id key.

Also, if I want to find all users with no friends, how can I do this if User has_many Friends and Friends belongs_to User

Answer 1

User.joins(:email).where(email: {user_id: nil})

All users with no friends

Answer 2

You can use a counter cache to do this, example:

class Friend < ActiveRecords::Base
  belongs_to :user, counter_cache: true
end

You need to have friends_count field in the users table (User model) to make this work. After that you can query

User.where(friends_count: 0)

http://guides.rubyonrails.org/association_basics.html

Answer 3

The fastest way to find users with no friends in SQL would be a NOT EXISTS correlated subquery, which a decent RDBMS will implement as an antijoin. This comes into its own performance-wise as the overall data size and the ratio of child records to parent records increases, as it does not require a DISTINCT operation on the parent records in the way that an outer join generally would

In activerecord you'd currently implement it as:

User.where("Not Exists (Select null From friends Where friends.user_id = users.id)")

You probably have friends.user_id indexed already, but this would be a good time to check that.

Like the email count, this would benefit from a counter cache -- searching for particular numbers or ranges of numbers of friends could also then be indexed.