Rails find where ALL associated records meet condition

Question

I'm trying to get all the clients that have doctors associated BUT none of them has started their first session (one client has_many doctors and can have first sessions with each of them).

So far I have:

@clients =  Client.joins(:doctors).where('doctors.first_session IS NULL').order('clients.id DESC')

But this doesn't work when a client has for example 2 doctors. the first doctor.first_session = null but the second one is not. This case will return the client and it don't want it to.

Any ideas?

Answer 1

This is one of those cases where in order to find records that don't meet a certain condition, you do it by finding all records except those that meet the condition. In SQL this is done with a subquery in the WHERE clause.

For cases like this, the squeel gem is extremely helpful, because it encapsulates the SQL complexity. This is how I would do it (with squeel):

scope :visited_doctor, joins(:doctors).where { doctors.first_visit != nil }
scope :not_visited_doctor, where { id.not_in(Patient.visited_doctor.select(:id)) }

Note that you can do this without squeel, but you'll have to get your hands (and your code) dirty with SQL.