Flask Sqlalchemy - query many to many with several values-CodePudding

I have a many-to-many relationship between matches and teams, where more than one team can play in a match and each team can have more than one match. The models are:

class Match(db.Model):
    __tablename__ = 'match'

    id = Column(
        db.Integer,
        primary_key=True,
        autoincrement=True)

    teams = db.relationship('Team', secondary='team_match_link')

class Team(db.Model):
    __tablename__ = 'team'

    id = Column(
        db.Integer,
        primary_key=True,
        autoincrement=True)

    matches = db.relationship('Match', secondary='team_match_link')


class TeamMatchLink(db.Model):

    __tablename__ = 'team_match_link'

    match_id = Column(
        db.Integer,
        db.ForeignKey('match.id'),
        primary_key=True
    )

    team_id = Column(
        db.Integer,
        db.ForeignKey('team.id'),
        primary_key=True
    )

Given two teams [T1, T2], how do I query for the matches that contain EXACTLY these two teams and no other teams?

This gives me the matches that contain at least the two teams and then I could check if there are other teams in these matches of course. But it looks ugly and I'm sure there is a better/more effcient way? Ideally the solution should work for n teams without sending me to loop hell.

res1 = match.query.filter(match.team.any(id=T1.id)).all()
res2 = match.query.filter(match.team.any(id=T2.id)).all()
res = [i for i in res1 if i in res2]

CodePudding user response：

How about querying the match team links and aggregate by counting the number of entries.

Query and some explanations below:

your_teams = [T1.id, T2.id]
result = db.session.query(Match, TeamMatchLink).filter(TeamMatchLink.team_id.in_(your_teams)).group_by(TeamMatchLink.match_id).having(func.count(TeamMatchLink.team_id) == len(your_teams)).join(Match).all()

The query consists of several parts and ideas strung together:

team_link_query = db.session.query(TeamMatchLink).filter(TeamMatchLink.team_id.in_(your_teams)))

This is selecting all team links that have your desired teams in them.

applicable_matches_query = team_link_query.group_by(TeamMatchLink.match_id).having(func.count(TeamMatchLink.team_id) == len(your_teams))

This aggregates the team match links to find those that have all the teams in them that you require. (A match will appear nr of times that a team link will match the your_teams list)

Finally, a join will get you the matches. You could of course reduce the query to give you only match ids from the team match link table directly.