Embeding multiple elixir structs into new column of existing table

Hello there! Elixir-newie question here:

Explanation of what I am trying to do:
I am working on a feature for an app that consists of taking assistance, by a coach, of members of a team present on the day of the training session.
Every time there’s a training session, a new record of this table is created, and the coach can check-in which member was part of it or not. 24hs later, you can check the list of who was there and who wasn’t.
When the check-in is created, an Assistance record is created with the information of the member.
The problem that I am facing is that that list (which is a modal on the page) is rendering the Assistances of that Training Instance, so I know who was there, but not who wasn’t.
I manage to “discard” who wasn’t there by taking the remaining members of the team (Team members - Assistances) for that training instance. But if in the future, a member of the team is deleted, or there’s a new member on the team, that record will be shown on the list modal too.

Idea approach:
The idea to solve this is to create a new column on the Training Instances table, which stores all the members (structs) of the team at that moment, so then I will know which members were part of the team in every past training instance created.

new_field: [
   %TeamMembership{membership: %{id:1, name: Pepe}}, 
   %TeamMembership{membership: %{id:2, name: Lui}},
   %TeamMembership{membership: %{id:3, name: Jo}}

What I manage to do so far: I was following this article and this and this example.
I create the migration for that table:

def change do
  alter table(:training_instances) do
    # :memberships_of_the_sessions = :mots
    add :mots, {:array, :map}, default: []

Then I added embeds_many :mots, TeamMember, on_replace: :delete to the TrainingInstance schema

I cast the new field:

def create_changeset(attrs, training, team) do
    |> cast(attrs, [:date, :start_time, :end_time, :team_size])
    |> cast_embed(:mots)  # <- new line

I created the new module TeamMember

defmodule App.Admin.TeamMemberships.TeamMember do
  use Ecto.Schema
  @primary_key false
  embedded_schema do
    field :membership

Everything seems to work fine at this point. This app has already multiple training instances records created and I will like to populate them with the members data. So for that I did a new migration, but here is where I am starting to have doubts.

defmodule App.Repo.Migrations.AddTrainingMembershipsToMOTS do
  use Ecto.Migration
  import Ecto.Query, warn: false
  alias App.Repo
  alias App.Admin.Teams.Team
  alias App.Admin.TeamMemberships.TeamMembership
  def up do
    |> join(:inner, [ti], t in Team, on: ti.team_id == t.id )
    |> join(:inner, [ti, t], tm in TeamMembership, on: tm.team_id == t.id )
    |> update([ti, t, tm], set: [mots: tm])
    |> Repo.update_all([])

But I get this error:

mix ecto.migrate

16:35:01.901 [info] == Running 20210712115835 App.Repo.Migrations.AddTrainingMembershipsToMOTS.up/0 forward
16:35:01.916 [debug] QUERY ERROR source="training_instances" db=0.0ms
UPDATE "training_instances" AS t0 SET "mots" = t2 FROM "teams" AS t1, "team_memberships" AS t2 WHERE (t0."team_id" = t1."id") AND (t2."team_id" = t1."id") []
** (Postgrex.Error) ERROR 42804 (datatype_mismatch) column "mots" is of type jsonb[] but expression is of type team_memberships
  query: UPDATE "training_instances" AS t0 SET "mots" = t2 FROM "teams" AS t1, "team_memberships" AS t2 WHERE (t0."team_id" = t1."id") AND (t2."team_id" = t1."id")
  hint: You will need to rewrite or cast the expression.
  (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:593: Ecto.Adapters.SQL.raise_sql_call_error/1
  (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:526: Ecto.Adapters.SQL.execute/5
  (ecto_sql 3.4.5) lib/ecto/migration/runner.ex:278: Ecto.Migration.Runner.perform_operation/3
  (stdlib 3.12.1) timer.erl:166: :timer.tc/1
  (ecto_sql 3.4.5) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/7
  (ecto_sql 3.4.5) lib/ecto/migrator.ex:342: Ecto.Migrator.attempt/7
  (ecto_sql 3.4.5) lib/ecto/migrator.ex:242: anonymous fn/4 in Ecto.Migrator.do_up/4
  (ecto_sql 3.4.5) lib/ecto/migrator.ex:324: anonymous fn/3 in Ecto.Migrator.run_maybe_in_transaction/6
  (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:875: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
  (db_connection 2.2.2) lib/db_connection.ex:1427: DBConnection.run_transaction/4
  (ecto_sql 3.4.5) lib/ecto/migrator.ex:323: Ecto.Migrator.run_maybe_in_transaction/6
  (elixir 1.10.4) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
  (elixir 1.10.4) lib/task/supervised.ex:35: Task.Supervised.reply/5
  (stdlib 3.12.1) proc_lib.erl:249: :proc_lib.init_p_do_apply/3

Still not sure if this is the right approach to store multiple structs into a list, or if this strategy is the best to see who was part of the team when the training instance was created, so if someone has a better idea I am open to reading :slight_smile:

Sounds like you’ve identified that there’s a mismatch between your requirement:

And the implementation:

IMO the tension here is because the database fact "no record in assistances" is trying to represent two separate concepts:

  • the player was on the team but did not attend
  • the player was not on the team at the time

A common approach would be to add an attended flag to the assistances table, splitting the cases:

  • a player with a record, with attended true: showed up
  • a player with a record, with attended false: was on the team, did not show up
  • a player with no record: was not on the team

Hi @al2o3cr Thanks for the reply! :slight_smile:

I will think about how to add a flag for the attended records since this record is created only when the coach does the check-in for the member. So actually I only have records of who attended the training session, I don’t store who haven’t attended.
Thinking about it, I could do a new table, that belongs to training_sessions, and store all members that are part of the team when that training session is created.
So for a training session (A) that happened yesterday, with a team of 10 members, and today half of the team drop-out, (A) will still have all the team members (10) on the records, and the training session happening today (B) will have the actual team (5), if that makes sense.

One approach would be:

  • create a training_instance record with attended = false for each team member when the session is created.
  • at check-in, show the list of all training_instances for the session
  • marking a player as present sets attended = true

As a bonus, that initial step provides a good place to add functionality if needed in the future - imagine you needed to say “only send SOME of the team to this session”.

One potential trouble spot: if a player is added to the team after the session is created but before the session happens, they won’t automatically appear on the check-in list. That may be a feature, or you may need to add specific code to put the newly-added player in trainings.