Rails 3 / SQL - A complex "exists?" query

Question

My models

class Team < ActiveRecord::Base
  has_many :team_players
  has_many :players, :through => :team_players
end

class TeamPlayer < ActiveRecord::Base
  belongs_to :team
  belongs_to :player
end

Teams can have a different number of players
Players can be on many teams
Retrieving a team_id if it already exists <- My problem

When creating a new team, I'm basically choosing multiple players in a nested form. Upon create, I need to check if a team with the exact player composition already exist.

Example:

Team 1: A (id 1) and B (id 2)
Team 2: A (id 1), B (id 2) and C (id 3)

Lets say I create a new team with A and B, then I need the query somehow tell me if it exists or not, by returning the team_id or something, or an empty result.

I've played around with queries similar to this, but with no satisfying result

SELECT *, count(id) as c FROM "team_players" WHERE player_id IN (1,3) GROUP BY team_id HAVING c = 2

mu is too short · Accepted Answer · 2011-09-12T16:07:47.457

I think you can do this in two steps:

Find the teams that have all the players you're looking at.
Check if those teams have more players than the player set you're looking at.

That translates into this:

select team_id
from team_players
where team_id in (
    select team_id
    from team_players
    where player_id in (1,3)
    group by team_id
    having count(player_id) = 2
)
group by team_id
having count(player_id) = 2

Given this in team_players:

team_id|player_id
     1 | 1
     1 | 3
     1 | 4
     2 | 1
     2 | 3
     3 | 3

The above query says team_id = 2 and there's your exact match. The query you're using gives you the teams that contain the players in question as a subset, not the teams that are set-wise equal to the players in question.

If you only want to know if such a team exists then wrap that query in a select_rows call and you're done:

class Team < ActiveRecord::Base
    # players is an array of Fixnum player IDs
    def self.team_exist?(players)
        return false if(!players.present?)
        connection.select_rows(%Q{
            select team_id
            from team_players
            where team_id in (
                select team_id
                from team_players
                where player_id in (#{players.join(',')})
                group by team_id
                having count(player_id) = #{players.length}
            )
            group by team_id
            having count(player_id) = #{players.length}
        }).present?
    end
end

The #{players.join(',')} interpolation assumes that team_exist? is being given an array of Fixnum so you'll want to properly scrub your data in the caller or add a players = players.map(&:to_i) before the connect.select_rows call to scrub it inside team_exist?.

Thanks! I'm not using the connection.select_rows, because I want to grab the actual team_id value. But the query itself works just fine. (Except that you forgot to define the 'c' in the last having part) — Frexuz, Sep 12 '11 at 12:38
@Frexuz: The `having c...` should have been `having count(player_id)...` right? I was translating between my made up nonsense names and your real names. — mu is too short, Sep 12 '11 at 16:07

Rails 3 / SQL - A complex "exists?" query

1 Answers1