Want to find records with no associated records in Rails

by

Update 4 – Rails 6.1

Thanks to Tim Park for pointing out that in the upcoming 6.1 you can do this:

Person.where.missing(:contacts)

Thanks to the post he linked to too.

Update 3 – Rails 5

Thanks to @Anson for the excellent Rails 5 solution (give him some +1s for his answer below), you can use left_outer_joins to avoid loading the association:

Person.left_outer_joins(:contacts).where(contacts: { id: nil })

I’ve included it here so people will find it, but he deserves the +1s for this. Great addition!

Update 2

Someone asked about the inverse, friends with no people. As I commented below, this actually made me realize that the last field (above: the :person_id) doesn’t actually have to be related to the model you’re returning, it just has to be a field in the join table. They’re all going to be nil so it can be any of them. This leads to a simpler solution to the above:

Person.includes(:contacts).where(contacts: { id: nil })

And then switching this to return the friends with no people becomes even simpler, you change only the class at the front:

Friend.includes(:contacts).where(contacts: { id: nil })

Update

Got a question about has_one in the comments, so just updating. The trick here is that includes() expects the name of the association but the where expects the name of the table. For a has_one the association will generally be expressed in the singular, so that changes, but the where() part stays as it is. So if a Person only has_one :contact then your statement would be:

Person.includes(:contact).where(contacts: { person_id: nil })

Original

Better:

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

For the hmt it’s basically the same thing, you rely on the fact that a person with no friends will also have no contacts:

Person.includes(:contacts).where(contacts: { person_id: nil })

More Related Contents:

  1. How to do a LIKE query in Arel and Rails?
  2. Combine two ActiveRecord::Relation objects
  3. Rails where condition using NOT NIL
  4. How to implement bulk insert in Rails 3
  5. Fully custom validation error message with Rails
  6. How to use concerns in Rails 4
  7. Devise redirect after login fail
  8. Rails: How to run `rails generate scaffold` when the model already exists?
  9. Webrick is very slow to respond. How to speed it up?
  10. What does inverse_of do? What SQL does it generate?
  11. Devise Secret Key was not set
  12. What does map(&:name) do in this Ruby code?
  13. Adding cookie session store back to Rails API app
  14. Rails 4 images not loading on heroku
  15. Why do people use Heroku when AWS is present? What distinguishes Heroku from AWS? [closed]
  16. Bundler::GemNotFound: Could not find rake-10.3.2 in any of the sources
  17. Rails 3: How to “redirect_to” in Ajax call?
  18. What’s the difference between belongs_to and has_one?
  19. confusing about autoload_paths vs eager_load_paths in rails 4
  20. Find number of months between two Dates in Ruby on Rails
  21. What do the &,
  22. SimpleForm without for (non model form)
  23. rails server bin/rails:6: warning: already initialized constant APP_PATH error
  24. Rails: How to parse date-time string into a specific time zone
  25. What does binding a Rails Server to 0.0.0.0 buy you?
  26. Ruby: problem installing EventMachine under Windows 7
  27. Ruby on Rails Callback, what is difference between :before_save and :before_create?
  28. Ruby on Rails: Advanced search
  29. An error occurred while installing pg (0.17.1), and Bundler cannot continue
  30. ImageMagick – “CORE_RL_magick_.dll not found” or how to install RMagick on windows with ruby 1.9.2

Leave a Comment