Creating Autosumming Fields

Phoenix Forum Questions / Help
, , , ,
1

I’ve created an Opponent model that has several fields associated with it, each of which represents an actual or computed value. You can check out the whole file as it exists right now here, but the schema itself looks like this:

  schema "opponents" do
    field :name,                :string
    field :external_id,         :string
    field :away_losses,         :float
    field :away_wins,           :float
    field :home_losses,         :float
    field :home_wins,           :float
    field :losses,              :float
    field :neutral_losses,      :float
    field :neutral_wins,        :float
    field :opp_opp_winning_pct, :float
    field :opp_winning_pct,     :float
    field :winning_percentage,  :float
    field :wins,                :float

    belongs_to :dataset, Scorcerer.Datasets.Dataset

    timestamps()
  end

Some of the fields are dependent on each other - for example for there to be a winning_percentage there have to be wins and losses. Others can bet set or not and it doesn’t really matter that much - we can compute wins from home_wins and away_wins even if neutral_wins is nil. Right now I’ve got it setup to handle all of that via functions like this:

  def autosum_fields(opponent) do
    if opponent.wins == nil do
      set_wins(opponent)
    end

    if opponent.losses == nil do
      set_losses(opponent)
    end

    if opponent.winning_percentage == nil do
      set_winning_percentage(opponent)
    end
  end

  defp set_winning_percentage(opp) do
    if (opp.wins != nil && opp.losses != nil) do
      Scorcerer.Opponents.update_opponent(opp, %{ winning_percentage: opp.wins / opp.losses })
    end
  end

  defp set_wins(opp) do
    wins = Enum.reduce([:home_wins, :away_wins, :neutral_wins], 0, fn key, acc ->
      key_value = Map.get opp, key
      acc + (key_value || 1)
    end)

    Scorcerer.Opponents.update_opponent(opp, %{ wins: wins })
  end

  defp set_losses(opp) do
    losses = Enum.reduce([:home_losses, :away_losses, :neutral_losses], 0, fn key, acc ->
      key_value = Map.get opp, key
      acc + (key_value || 1)
    end)

    Scorcerer.Opponents.update_opponent(opp, %{ losses: losses })
  end

I’ve actually got two questions here:

  1. What is the right way to ensure that my autosum_fields method runs whenever the opponent is updated?
  2. Is there a better way to handle the actual updates? I know I’m doing more updates than is required right now because it’s happening on a per-field basis.
1 Like
2

Assuming PostgreSQL as a database.

You have 2 options how to make it “right”:

  • Views in the DB (potentially materialised)
  • If you are on Postgres 12+ then you can use generated fields

Both will make your application much clearer and, at least logically, normalise your data.

1 Like
3

If your application doesn’t write directly to the calculated fields, you could do the calculations in the changeset function; use get_field(changeset, :neutral_losses) and so on and add the resulting values with change (since they don’t need type-casting).

The result is that passing an update to a single field like %{"away_losses" => 3} gives a changeset that updates away_losses and all the derived values.

4

I think this is a very cool way to do it and I’m definitely not against the idea, but some of the calculations I want to do in the future are going to be pretty complicated and I feel like I’ll be able to do that better in Elixir than in SQL.

When you say doesn’t write directly to the calculated fields, do you mean that this approach would cause problems with being able to have those values as user input as well? I think I may have used abused the term “calculated field” a bit. I’m looking to be able to fill out a set of data where some fields can fill out others but some of them can be generated based on more basic values, if that helps clarify what I’m thinking.

I like the idea of doing it like this but I’d love a bit more guidance.

  def set_losses(changeset) do
    # TODO: bail out if losses is already set
    autosum_fields = MapSet.new([:home_losses, :away_losses, :neutral_losses])
    changed_fields = changeset.changes |> Map.keys |> MapSet.new

    fields_that_matter = MapSet.intersection(m1, m2) |> Enum.count

    if fields_that_matter > 0 do
      # TODO: grab other fields from the database related to this record and
      # do calculations

      losses = do_calculations
      changeset = change(changeset, losses: losses) # is this correct?
    end
  end

How can I get fields for this record that aren’t part of the changeset’s changes? That way I can bail out of the function quickly if the losses property is already set and I can grab any other values needed for the calculation.

5

https://hexdocs.pm/ecto/Ecto.Changeset.html#get_field/3
get_field gets the value from changeset and if the field is not present in changes, then it gets it from the record.

2 Likes
6

Yes - if the fields are writable directly, you’ll need to decide what happens when incompatible parameters are assigned; for instance, %{home_losses: 10, away_losses: 5, losses: 3}. Should that ignore losses?

Also consider if directly writing to the fields (even ones like home_losses) is the best approach; if data arrives incrementally (one result at a time) you might instead want operations like “record this game was a win at home” that manipulate multiple fields and recalculate things like winning_percentage.