0

I'm trying to get users from few groups (with given ids) and exclude the users from other groups.

I've tried something like :

User.joins(:groups).where(groups: {id: ["8939","8950"]}).where.not(groups: {id: 8942}).map(&:id)
  User Load (0.9ms)  SELECT "users".* FROM "users" INNER JOIN "groups_users" ON "groups_users"."user_id" = "users"."id" INNER JOIN "groups" ON "groups"."id" = "groups_users"."group_id" WHERE "groups"."id" IN (8939, 8950) AND "groups"."id" != $1  [["id", 8942]]
=> [119491, 119489, 119490, 119492, 119488, 119484, 119483, 119491, 119482]

But that's not correct

The users in group 8942.

Group.find(8942).users.pluck(:id)
  Group Load (0.4ms)  SELECT  "groups".* FROM "groups" WHERE "groups"."id" = $1 LIMIT 1  [["id", 8942]]
   (0.6ms)  SELECT "users"."id" FROM "users" INNER JOIN "groups_users" ON "users"."id" = "groups_users"."user_id" WHERE "groups_users"."group_id" = $1  [["group_id", 8942]]
=> [119490, 119492, 119491, 119457, 119423]

The where.not doesn't work on user "groups"."id" != $1 [["id", 8942]]. Why ?

Simone Carletti
  • 173,507
  • 49
  • 363
  • 364
Mio
  • 1,412
  • 2
  • 19
  • 41

2 Answers2

1

Correct way to do such things is to use SQL EXISTS condition. I wish there was a specific ActiveRecord helper method for that, but there isn't at the moment.

Well, using pure SQL is just fine:

User.where("EXISTS (SELECT 1 FROM groups_users WHERE groups_users.user_id = users.id AND groups_users.group_id IN (?))", [8939, 8950]).
  where("NOT EXISTS (SELECT 1 FROM groups_users WHERE groups_users.user_id = users.id AND groups_users.group_id IN (?))", [8942])

What you were doing with your original query is asking for not joining groups with [8942] ids to your query, and only joining groups with ids [8939, 8950]. Well, you can see right now that this doesn't make any sense: that's like asking to select every user whose name is bob and NOT charlie. Second condition doesn't add anything to the first one.

Join query is multiplicating columns, so if your user is in every group, result set would be:

user_id | group_id
1       | 8939
1       | 8950
1       | 8942

Then you filter out the latter row: 1 | 8942. Still, user 1 is in the result set and is returned.

And to ask the database to return only records which doesn't connect with another relation you should explicitly use NOT EXISTS which exists explicitly for that purpose :)

EugZol
  • 6,476
  • 22
  • 41
  • Thanks EugZol again. You're my `NOT EXISTS` support guy. I definitely need to learn more sql request. With the one you give me I have a small error. `PG::UndefinedTable: ERROR: missing FROM-clause entry for table "groups_users" LINE 1: ..."users" WHERE (EXISTS (SELECT 1 FROM groups WHERE groups_use...` – Mio Aug 13 '15 at 15:31
  • 1
    Haha, I really am an `EXISTING` (existential?) support guy :) `WHERE groups_use` – it's your typo, change `_` into a `.` (`groups.user_id`). – EugZol Aug 13 '15 at 15:33
  • `PG::UndefinedColumn: ERROR: column groups.user_id does not exist LINE 1: ..."users" WHERE (EXISTS (SELECT 1 FROM groups WHERE groups.use...` with your version – Mio Aug 13 '15 at 15:36
  • Ah, my bad, you have many-to-many relationship. Updated my answer, please try that one. – EugZol Aug 13 '15 at 15:40
  • 1
    @eirikir It doesn't allow to do what I did here (and I faced with such tasks really often) – to make a query with `WHERE EXISTS (some join condition)`. Well, it's possible to write `GroupUser.where('group_id IN (?)', [1,2,3]).where('user_id = users.id').to_sql` instead of inner SQL, but it doesn't really help. – EugZol Aug 13 '15 at 15:47
  • The other with active record is : `(User.joins(:groups).where(groups: {id: ["8939","8950"]}) - User.joins(:groups).where(groups: {id: 8942})).map(&:id)`. But two sql request. – Mio Aug 13 '15 at 15:53
  • 1
    @BeniMio That is ineffective way, because it will subtract records with Ruby code, not inside of the database. So you lose time for transferring roughly twice as much data back and forth, and lose CPU/RAM because Ruby is less effective in computations than database engine (which is written in C and optimized for the task). Under normal conditions you want to delegate such things to DB engine. – EugZol Aug 13 '15 at 15:58
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/86964/discussion-between-beni-mio-and-eugzol). – Mio Aug 14 '15 at 08:17
1

There is now a Where Exists gem which you can use. (Full disclosure: I've created that gem recently.)

With it you can achieve your task as simple as:

User.where_exists(:groups, id: [1, 2]).where_not_exists(:groups, id: [3, 4])
EugZol
  • 6,476
  • 22
  • 41