使用 activerecord 发出请求以仅获取组中的用户而不是其他用户 [英] Make a request with activerecord to get only the users from groups and not from others

问题描述

我试图从几个组中获取用户(具有给定的 ID)并从其他组中排除用户.

我尝试过类似的东西:

User.joins(:groups).where(groups: {id: ["8939","8950"]}).where.not(groups: {id: 8942}).map(&:ID)用户负载 (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]

但这不正确

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]

where.not 不适用于用户 "groups"."id" != $1 [["id", 8942]].为什么?

解决方案

正确的做法是使用 SQL EXISTS 条件.我希望有一个特定的 ActiveRecord 辅助方法，但目前没有.

好吧，使用纯 SQL 就好了:

User.where("EXISTS (SELECT 1 FROM groups_users WHERE groups_users.user_id = users.id AND groups_users.group_id IN (?))", [8939, 8950]).where(不存在(从groups_users中选择1个groups_users.user_id = users.id AND groups_users.group_id IN(?))"，[8942])

您对原始查询所做的操作是要求不加入带有 [8942] id 的组到您的查询中，并且只加入id 为 [8939, 8950] 的组.好吧，您现在可以看到这没有任何意义:这就像要求选择名称为 bob 而不是 charlie 的每个用户.第二个条件不会为第一个条件添加任何内容.

加入查询是乘以列，所以如果你的用户在每个组中，结果集将是:

user_id |group_id1 |89391 |89501 |8942

然后过滤掉后一行:1 |8942.尽管如此，用户 1 在结果集中并被返回.

并要求数据库返回仅不与其他关系连接的记录，您应该明确使用 NOT EXISTS ，它为此目的明确存在:)

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 ?

解决方案

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 :)

这篇关于使用 activerecord 发出请求以仅获取组中的用户而不是其他用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！