CODEWITHSUNDEEP
ruby-on-railsactiverecord
Jeremy Thomas
Jeremy Thomas

Reputation: 6674

Rails 5: Query for empty association

I have two models Client and MealWeek and I want to be able to pull all clients without any active meal weeks. I can pull active meal weeks by doing either of the following:

Client.find(x).meal_weeks.where(active: true)
Client.find(x).meal_weeks.unused

I can easily pull clients with active meal weeks:

Client.includes(:meal_weeks).where(meal_weeks: { active: true })

But the same cannot be said for clients without active meal weeks. I've tried including a not in the query above but that returns all clients with inactive meal weeks, not client's without any active meal weeks (all meal_weeks are active: false).

Is there a way to do this?

Upvotes: 1

Views: 320

Answers (1)

Anand
Anand

Reputation: 6531

In rails 5+ you can use where.not So in this case, your query would be

Client.where.not(id: 
  Client.select(:id).joins(:meal_weeks)
      .where(meal_weeks: {active: true})
)

This will use a sub query of your original version as a NOT IN clause. Resulting SQL

SELECT 
 clients.* 
FROM 
 clients
WHERE 
 clients.id NOT IN ( 
    SELECT 
     clients.id
    FROM 
     clients
      INNER JOIN meal_weeks ON meal_weeks.client_id=clients.id
    WHERE 
      meal_weeks.active= true
  )

Upvotes: 3

Related Questions