Select results where joined table contains records with an attribute, but without another

Question

Example.joins(another: :things).where(examples: {status: 'active'}, things: {status: 'required'}).where.not(things: {status: 'unwanted'})

The result from the query above is the same with or without the "where.not".

Things may have different statuses: 'required', 'unwanted' and many others. I must fetch Example records that contain at least one thing with 'required' status, but exclude records that contain one or more thing with 'unwanted' status.

Any help? Thanks!

Answer 1

I can think of this problem to solve using 2 queries. Rails form will be helpful in this case.

another_ids = Example.from(Another.join(:things)
                     .group(:status)
                     .where(status: [ 'required', 'indifferent'])
                     .select('status, distinct anothers.id as another_id'), :x)
                     .pluck("x.another_id")

then,

Example.where(another_id: another_ids).all

Here is the chat conversations which leads to this answer.