Get users with and without options

Question

I am trying to get group users with specific identifiers that are not admin.

At the moment I have:

group.users .joins(:roles) .where(id: user_ids) .where.not(roles: { role_type: Role::Type::ADMIN }) .pluck(:id) 

In my log I have:

SQL to load group :

  (0.3ms) SELECT "users"."id" FROM "users" INNER JOIN "groups_users" ON "users"."id" = "groups_users"."user_id" WHERE "groups_users"."group_id" = $1 [["group_id", 137375]] 

SQL for the query above:

  (0.6ms) SELECT "users"."id" FROM "users" INNER JOIN "roles" ON "roles"."user_id" = "users"."id" AND "roles"."is_destroyed" = $1 INNER JOIN "groups_users" ON "users"."id" = "groups_users"."user_id" WHERE "groups_users"."group_id" = $2 AND "users"."id" IN (82884, 82885) AND "roles"."role_type" != $3 [["is_destroyed", "f"], ["group_id", 137375], ["role_type", 1]] 

The problem is that I always get all users in the group matching user_ids . where.not not effective.

I needed to do something like

 users_in_group = group.users.where(id: user_ids).pluck(:id) users_in_group -= group.users.joins(:roles).where (roles: { role_type: Role::Type::ADMIN}).pluck(:id) 

I do not understand why.

Answer 1

If you want to exclude admins, even if they have other roles, you can use SQL EXISTS :

 group.users .where(id: user_ids) .where("NOT EXISTS (SELECT 1 FROM roles WHERE user_id = users.id AND role_type = ?", Role::Type::ADMIN) .pluck(:id) 

And, addressing a typical objection to such advice: do fine with dirty hands by writing SQL fragments when you use ActiveRecord in Rails. You should not limit the (not so wide) possibilities of your DSL.

UPD

To simplify the code, you can use the Where Exists gem (disclosure: I wrote it recently).

Add gem 'where_exists' to your Gemfile, run bundle install , and then do the following:

 group.users .where(id: user_ids) .where_not_exists(:roles, role_type: Role::Type::ADMIN) .pluck(:id)