In my system I have a following structure:
class Worker
has_many :worker_memberships
end
class WorkerMembership
belongs_to :worker
belongs_to :event
end
class Event
has_many :worker_memberships
end
Imagine I have a certain @event. How can I find all workers that have NO worker_memberships belonging to this @event?
This is pretty much synthesis of both other answers.
First: stick to has_many through as @TheChamp suggests. You're probably using it already, just forgot to write it, otherwise it just wouldn't work. Well, you've been warned.
I generally do my best to avoid raw SQL in my queries whatsoever. The hint about select I provided above produces a working solution, but does some unneessary stuff, such as join when there's no practical need for it. So, let's avoid poking an association. Not this time.
Here comes the reason why I prefer has_many through to has_and_belongs_to_many in many-to-many associations: we can query the join model itself without raw SQL:
WorkerMembership.select(:worker_id).where(event: @event)
It's not the result yet, but it gets us the list of worker_ids we don't want. Then we just wrap this query into a "give me all but these guys":
Worker.where.not(id: <...> )
So the final query is:
Worker.where.not(id: WorkerMembership.select(:worker_id).where(event: @event) )
And it outputs a single query (on @event with id equal to 1):
SELECT `workers`.* FROM `workers` WHERE (`workers`.`id` NOT IN (SELECT `worker_memberships`.`worker_id` FROM `worker_memberships` WHERE `worker_memberships`.`event_id` = 1))
I also give credit to @apneadiving for his solution and a hint about mysql2's explain. SQLite's explain is horrible! My solution, if I read the explain's result correctly, is as performant as @apneadiving's.
@TheChamp also provided performance costs for all answers' queries. Check out the comments for a comparison.
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다