Schema - sub-schema field value sum

Questions / Help
,
1

Hey I have a User and a Team schema and they are related as such:
User: belongs_to(:team, Userteam1.Team) and
Team: has_many(:users, Userteam1.User, on_delete: :nilify_all)
my question is that my User has a field called score, and i want to have a team score, where the users with the given team id would have their scores summed up.
How does one go about this?
Do I need a field for team_score or can i do it without storing the current one, or if i store it how do i make sure it is always up to date?
Or should it always be queried?
And where does the calculation happen?

Thank you

2

If the dataset is not big, I’d always query it or create a view.

3

So you mean to query the users with the team id and add up the scores and pass that back?

4

Oh, right. Sorry, I was unclear.

I meant that I’d sum up the scores in the database as part of the query,

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

or something like that.

The view approach might be easier at the call-site, you’d define a view, then define an ecto schema for it as if it was a table

defmodule UserWithTeamScore do
  use Ecto.Schema

  schema "users_with_team_score" do
    # ...
    field :team_score, :integer
    # ...
  end
end

and then add it to other schemas

has_many(:users, UserWithTeamScore)

or use it directly.

The view itself would be added in a migration and could look like

defmodule TourApp.SomeMigration do
  use Ecto.Migration
  
  def up do
    execute """
    CREATE VIEW users_with_team_score AS
      SELECT id,
             team_id,
             score AS individual_score,
             sum(score) OVER (PARTITION BY team_id) AS team_score
      FROM users
    """
  end

  def down do
    execute "DROP VIEW users_with_team_score;"
  end
end
1 Like
5

I am trying to use the first approach, like this:

def show(conn, _params) do
user = Guardian.Plug.current_resource(conn)
team = Web.get_team!(user.team.id)

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

team_score = Repo.all(score_query)

IO.puts("score")
IO.inspect(team_score)

conn |> render("team.json", team: team)

end

but it says that team is an unbound variable, why?

I tried to put it into a case like this:

def show(conn, _params) do
    user = Guardian.Plug.current_resource(conn)

    case Web.get_team!(user.team.id) do
      {:ok, team} ->
        score_query =
          from(
            u in User,
            where: u.team.id == team.id,
            select: sum(u.score)
          )

        team_score = Repo.all(score_query)

        IO.puts("score")
        IO.inspect(team_score)

        conn |> render("team.json", team: team)
    end
  end

but the same error persists

6

ok missed the^

1 Like
7

Actually why is that that I get back results like ‘/n’, ‘/a’, ‘/v’ instead of the scores if there are more than one score values to sum up?
got it :smile: https://stackoverflow.com/questions/30037914/elixir-lists-interpreted-as-char-lists

1 Like