Schema - query issue with value depening on other schema

Questions / Help
,
1

Hey there,
I have this structure of schemas user has score and he is a member of a team.
So I am calculating the team’s score based on the users score’s who has the given team id.
like this:
def get_team_score(user) do
team = Web.get_team!(user.team.id)

score_query =
  from(
    u in User,
    where: u.team_id == ^team.id,
    select: sum(u.score)
  )

Repo.all(score_query)
end

my problem is now that i want to get all the team with the scores, how could I do that?
I am returning my team like this now:

def render("team.json", %{team: team, team_score: team_score}) do
%{
  id: team.id,
  name: team.name,
  team_score: team_score
}
end

as you see the team_score is a separate value not inside the team, so I am not able to do it like this:

 def render("index.json", %{teams: teams}) do
%{data: render_many(teams, ApiTeamView, "team.json", as: :team)}
end

since i don’t have the scores.
So is it possible to query it in some way, or change the team to have scores too, but then i need to save the team’s score every time a user’s score changes.
Thoughts?

2

This comes back to the age-old question of ‘How would you do this in straight SQL’, once that is figured out you just transcribe it to the more typed Ecto Query format. Essentially you will just select all the teams with a join to the scores doing whatever you wish, like summing them. :slight_smile: