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')
Thanks to the suggestion of using .outerjoin from @snakecharmerb, I have found a working solution.
Using outerjoin alone still resulting in the rows where the count was 0 to not be returned, but by moving the additional filter to the condition of the outerjoin included all rows.
Modified query:
query = db.session.query(Team, sa.func.count(PlayerTeam.id).label('count')) \
.outerjoin(
PlayerTeam,
(Team.id == PlayerTeam.team_id) & (PlayerTeam.end_date.is_(None))
) \
.group_by(Team) \
.order_by(getattr(sa, order)('count'))