Rails, query with Active record does not take into account negative where condition-CodePudding

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 ?

CodePudding user response：

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