Creating Autosumming Fields

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.

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.

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.

get_field gets the value from changeset and if the field is not present in changes, then it gets it from the record.

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.