在ActiveRecord查询中包含所有ID

本文介绍了在ActiveRecord查询中包含所有ID的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在开发游戏平台，并且具有以下(简化的)模型:

I am developing a gaming platform and I have the following (simplified) models:

class Game < ActiveRecord:Base has_many :game_players has_many :players, through: :game_players end class Player < ActiveRecord:Base has_many :game_players has_many :games, through: :game_players end class GamePlayer < ActiveRecord:Base belongs_to :game belongs_to :player end

我需要执行ActiveRecord查询，以查找特定用户组玩过的所有游戏.例如，给定数据:

I need to perform an ActiveRecord query that looks for all games played by a certain group of users. For example, given the data:

+---------+-----------+ | game_id | player_id | +---------+-----------+ | 10 | 39 | | 10 | 41 | | 10 | 42 | | 12 | 41 | | 13 | 39 | | 13 | 41 | +---------+-----------+

我需要找到一种方法来确定ID为39和41的玩家玩了哪些游戏，在这种情况下，将为ID为10和13的游戏.我到目前为止所发现的查询是:

I need to find a way to determine which games are played by the players with ids 39 and 41 which, in this case, would be the games with ids 10 and 13. The query I have found up to now is:

Game.joins(:players).where(players: {id: [39, 41]}).uniq

但是，此查询返回的是其中任何一个玩家所玩的游戏，而不是返回他们两个都玩的游戏.

However, this query is returning the games played by any of these players, instead of the games played by both of them.

推荐答案

此功能更像是SQL INTERSECT，在这种情况下，应该可以为您提供所需的结果:

This functions more like a SQL INTERSECT, and should give you the results that you need in this case:

Game.joins(:players).where(players: {id: [39,41]}).group('"games"."id"').having('COUNT("games"."id") > 1')

真的，魔术的发生是通过选择任一玩家正在玩的游戏，然后按game.id分组以将结果减少到结果组中具有多个game.id的那些游戏.它从Rails控制台中产生以下结果:

Really, the magic happens by selecting the games where either player is playing, and then grouping by game.id to reduce the results to those having more than one game.id in the result group. It produces the following results from the Rails console:

=> #<ActiveRecord::Relation [#<Game id: 10, created_at: "2016-05-07 01:17:25", updated_at: "2016-05-07 01:17:25">, #<Game id: 13, created_at: "2016-05-07 01:17:25", updated_at: "2016-05-07 01:17:25">]>

请注意，此解决方案仅返回第10和13个游戏(基于示例数据).手动验证显示，只有游戏10和13都有玩家39和41参加.

Note that only games 10 and 13 (based on the sample data) are returned by this solution. Manual verification shows that only games 10 and 13 had both players 39 and 41 playing.