CODEWITHSUNDEEP
mysqlsqlruby-on-railspostgresqlassociations
Railsana
Railsana

Reputation: 1842

Find a record with ALL associations having a certain value

This is more an SQL question, but I try to solve this in Rails:

I have an User who has_many :days. Days have a status which can be available or booked. I want to find all users who have only available days. I use:

User.includes(:days).where.not(days: { status: 'booked' }).references(:days)

The generated SQL query looks like this:

SELECT * FROM "users" LEFT OUTER JOIN "days" ON "days"."user_id" = "users"."id" WHERE ("days"."status" != 'booked')

The results contain users which have both kind of statuses. Thanks for any idea how to solve this!

Upvotes: 3

Views: 975

Answers (2)

Railsana
Railsana

Reputation: 1842

@CptMisery answered it correctly. The Rails syntax looks like this:

User.where.not(id: Day.select(:user_id).distinct.where(status: 'booked'))

Upvotes: 0

Andrey Deineko
Andrey Deineko

Reputation: 52357

I want to find all users who have only available days.

For this type of things you should use joins:

User.joins(:days).where(days: { status: 'available' }).group('users.id')

The generated SQL will look something like this:

SELECT users.* FROM users INNER JOIN days ON days.user_id = users.id WHERE days.status = available

EDIT

If I misunderstood you, and status in not either "booked" or "available" (but, for example, can be blank or nil), then the query should be as you had it with where.not:

User.joins(:days).where.not(days: { status: 'booked' }).group('users.id')

EDIT2

to get users with available days and without booked ones I'd probably do:

User.joins(:days).where.not(days: { status: 'booked' }).where(days: { status: 'available' }).group('users.id')

Upvotes: 3

Related Questions