I am querying a table Team to get a list of all the teams. This query object is fed to a pagination function that makes use of sqlalchemy’s paginate().

The function takes inputs for order and order_by which determine the column and order of the resulting query. This works fine when the sort is performed directly on one of Team‘s attributes, but I also want to perform the sort on the count of the number of relationships each record has with another table Player.

Using func.count(), .join() and .group_by() this is possible – however if a team does not have any players recorded, the record is ommitted from the query. I want to include all results in this query.

I have thought of creating a second query that omits the results of the first one, and then combine them somehow before passing them to the paginate function, but I haven’t been able to find a way to do this.

Is there a way to include the results where count should return 0, or is there another way to achieve this effect?

The function:

def get_teams(filters):
    """Get the collection of all teams"""
    page = filters['page']
    per_page = filters['per_page']
    order = filters['order']  # validates as either 'asc' or 'desc'
    order_by = filters['order_by']

    if order_by == 'active_players':  # checks if sort needs to be done manually
        query = db.session.query(Team, sa.func.count(PlayerTeam.id).label('count')) 
            .join(Team.player_association) 
            .filter(PlayerTeam.end_date == None) 
            .group_by(Team) 
            .order_by(getattr(sa, order)('count'))
    else:  # sort is directly on attribute, this is easy
        query = sa.select(Team).order_by(getattr(sa, order)(getattr(Team, order_by)))

    return Team.to_collection_dict(query, page, per_page)

Models:

class Team(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), index=True, unique=True, nullable=False)
    # some other fields

    player_association = db.relationship('PlayerTeam', back_populates='team',lazy='dynamic')
    players = association_proxy('player_association', 'player')

    @staticmethod
    def to_collection_dict(query, page, per_page):
        resources = db.paginate(query, page=page, per_page=per_page, error_out=False)
        # convert resources to dict and return

class Player(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    player_name = db.Column(db.String(64), nullable=False)
    # some other fields

    team_association = db.relationship('PlayerTeam', back_populates='player', lazy='dynamic')
    teams = association_proxy('team_association', 'team')

class PlayerTeam(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    player_id = db.Column(db.Integer, db.ForeignKey('player.id'))
    team_id = db.Column(db.Integer, db.ForeignKey('team.id'))
    end_date = db.Column(db.DateTime)
    # some other fields

    player = db.relationship('Player', back_populates='team_association')
    team = db.relationship('Team', back_populates='player_association')