使用 activerecord 发出请求以仅获取组中的用户而不是其他用户

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_id1 | 89391 | 89501 | 8942Then 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 发出请求以仅获取组中的用户而不是其他用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！