Rails: Either no associated records OR none of the associated records created by aspecific condition

Question

class User
   has_many :contacts
end

class Contact
   belongs_to :user
   # id
   # contact_type -> ['main', 'work', 'home']
end

What is the cleanest way to get only those users that either don't have any contact OR if they have contacts then none of the contact_type value is main ?

Example:

User: id: 1, name: 'Dev'
Contacts: []

User: id: 2, name: 'Mike'
Contacts: [
  id: 1, contact_type: 'work'
  id: 2, contact_type: 'main'
]

User: id: 3, name: 'John'
Contacts: [
  id: 3, contact_type: 'work'
  id: 4, contact_type: 'home'
]

Result: Should return users with id 1 and 3

Answer 1

You can left join users and contacts, group the rows by the users.id column, filter the groups without main contacts (using count with a case statement), and select the columns from the users table.

If you're using Rails 5+, you can use left_outer_joins.

User
  .left_outer_joins(:contacts)
  .group('users.id')
  .having("count(case when contact_type = 'main' then 1 end) = 0")

On older versions, you can pass a SQL fragment to the joins method to perform a left join.

 User
   .joins('left join contacts on contacts.user_id = users.id')
   .group('users.id')
   .having("count(case when contact_type = 'main' then 1 end) = 0")

SQL fiddle