Reputation: 3725
I have a many-to-one relationship between Team
and League
The following code joins the tables, and orders the leagues by goals_for
for each team
in that league
:
for league in League.query.join(League.teams).order_by(desc(Team.goals_for)):
total_goals = 0
for team in league.teams:
total_goals += team.goals_for
print("Total goals scored in", league.full_name, "is", total_goals)
Correctly produces:
Total goals scored in Germany Bundesliga is 22
Total goals scored in English Premier League is 15
I'm wondering about two things:
Given that teams
is basically a list, and therefore has no total_goals
as that belongs to each team
instance, is there a way to sum the value of team
goals without the for loop?
If 1 is possible, is it faster/better than the for loop above?
Upvotes: 0
Views: 2067
Reputation: 76962
Why not try to get aggregate result directly from the SQL
query, and avoid retrieving extra data from the database and loading whole relationship tree.
Following should given you an idea:
from sqlalchemy import func
q = (
session
.query(League.full_name, func.sum(Team.goals_for).label("total_goals"))
.join(Team, League.teams)
.group_by(League.full_name)
)
for full_name, total_goals in q:
print("Total goals scored in", full_name, "is", total_goals)
Upvotes: 3