I have the following data model :
user has_many organization_users
user has_many registrations
a user has a column type
a registration has columns learning_item_type and learning_item_id
The goal of the query is to retrieve all users from a specific organization that dont have registrations to a specific learning item (combinaison of learning_item_type and learning_item_id), it must include users from the organizations that don't have registrations at all (left join) I came up with this with active record query :
User
.joins(:organizations_users)
.left_joins(:registrations)
.where(
type: 'Collaborator',
'organizations_users.organization_id': organization.id
)
.where.not(
'registrations.learning_item_id': learning_item.id,
'registrations.learning_item_type': learning_item.class.to_s
).distinct
which in raw sql looks like :
"SELECT DISTINCT \"users\".* FROM \"users\" INNER JOIN \"organizations_users\" ON \"organizations_users\".\"user_id\" = \"users\".\"id\" LEFT OUTER JOIN \"registrations\" ON \"registrations\".\"account_id\" = 28 AND \"registrations\".\"user_id\" = \"users\".\"id\" WHERE \"users\".\"account_id\" = 28 AND \"users\".\"type\" = 'Collaborator' AND \"organizations_users\".\"organization_id\" = 1 AND NOT (\"registrations\".\"learning_item_id\" = 10164 AND \"registrations\".\"learning_item_type\" = 'Session')"
I can't figure out what's wrong with this query but it keeps returning users who actually have a registration with learning_item_id = 10164 and learning_item_type 'Session'.
Why is that negative NOT criteria not taken into account here ?
User.left_joins(:registrations)
After the join you get something like this:
| users.id | registrations.learning_item_id |
|---|---|
| 1 | 10164 |
| 1 | 10165 |
| 2 | 10166 |
Then filter out learning item 10164:
User.left_joins(:registrations)
.where.not("registrations.learning_item_id": 10164)
Result is:
| users.id | registrations.learning_item_id |
|---|---|
| 1 | 10165 |
| 2 | 10166 |
The returned sql result is correct, but you still get User#1 that is registered to LearningItem#10164.
What you need is to filter out the user:
User.where.not(
id: User.joins(:registrations).where("registrations.learning_item_id": 10164)
)
| users.id | registrations.learning_item_id |
|---|---|
| 2 | 10166 |