2

Scenario: 

Team
has_many :players, dependent: :destroy
has_many :users, through: :players

Player
belongs_to :team
belongs_to :user

User

So, let's say that i have 4 teams with different users: 

Team 1
User 1, User 2

Team 2
User 2, User 3

Team 3
User 1

Team 4
User 2, User 4, User 5

Now, suppose i have the id of two users, (User 1, User 5), and i want to know if there is any team which consists of ONLY these two players. Let's say i have a team that consists of users 1, 2 and 5. The query should not bring this team.

How can i use ActiveRecord semantics in my favor to do this? It is easy to get all players from a team, but i couldn't find a way to do the opposite.

MrYoshiji
  • 54,334
  • 13
  • 124
  • 117
MurifoX
  • 14,991
  • 3
  • 36
  • 60

3 Answers3

1

You can use two where clauses:

One for getting all the Teams having exactly two users.

Team.joins(:users).group("teams.id").having("count('distinct users.id') = 2").select("teams.id")

Second for having all Teams with users 1 and 5.

Team.joins(:users).where('users.id in (?)', [1,5]).group("teams.id").having("count('distinct users.id') = 2").select("teams.id")

Intersection of these two should give you what you need. So to combine it all:

Team.where(id: Team.joins(:users).group("teams.id").having("count('distinct users.id') = 2").select("teams.id")).where(id: Team.joins(:users).where('users.id in (?)', [1,5]).group("teams.id").having("count('distinct users.id') = 2").select("teams.id"))
Vijay Agrawal
  • 1,643
  • 12
  • 17
  • 1
    Context: we are looking for Teams composed of User 1 and 2. Team #3 has User 1 and User 3. This Team #3 will be found by your first instruction (has 2 players), and then found again because one of its players has an id in [1,2]. – MrYoshiji Aug 16 '17 at 13:07
1

UPDATE: AH! I got it in pure SQL:

users = User.first(2)
Team.joins(:users).group('teams.id').having('SUM( CASE WHEN users.id in (?) THEN 1 ELSE -1 END ) = ?', users, users.count)

Try it and let me know if it works for you (working here: http://sqlfiddle.com/#!17/bb2a9/8 and the same example but with 3 players: http://sqlfiddle.com/#!17/bb2a9/10)

This is not optimized on the DB level as it uses a lot of ruby/rails code, but can you try it?

users = User.first(2)
# find teams with that exact number of players associated
teams = Team.joins(:users).group('teams.id').having('COUNT(users.id) = ?', users.count)
# find players referencing to those teams with other users than the ones specified
players_to_ignore = Player.where(team_id: teams).where('user_id NOT IN (?)', users)
# get Teams where associated players id is not in the previous list
Team.where(id: teams).joins(:players).where('players.id NOT IN (?)', players_to_ignore)
MrYoshiji
  • 54,334
  • 13
  • 124
  • 117
0
Team.join(:users).where('users.id in (?)', [1,5]).
select { |team| team.users.map(&:id).sort == [1,5] }

Previous answer (For pre edited question)

Will this works for you?

Team.join(:users).where('users.id in (?)', [1,5])

You can do the same on user model by

# user.rb
has_many :teams, through: :works
has_many :works, foreign_key: :user_id

Responding to your edits & comment

Hacky:

Team.join(:users).where('users.id in (?)', [1,5]).
select { |team| team.users.map(&:id).sort == [1,5] }

Better?

SQL Select only rows where exact multiple relationships exist

Yana Agun Siswanto
  • 1,932
  • 18
  • 30