Multi Table relation for tracking progress

Questions / Help
,
1

Hey there,
The situation:
I have a User schema and a Challenge schema, and a Team schema.
How would one do so that u can tract that what challenge was done by who and who not?
For example a User is a member of a Team, how could I keep track of what % of the Users from a team has done a given Challenge?
Store them in a separate table? or what is a good way to go about this?

Thanks

2

I’d go with join tables for users_teams and users_challenges.

Assuming the following schemas

  schema "users" do
    field(:name, :string)
    timestamps()
    many_to_many(:teams, Team, join_through: UserTeam)
    many_to_many(:challenges, Challenge, join_through: UserChallenge)
  end

Then a query for which users in teams have completed challenges would be:

    Repo.all(
      from(u in User,
        join: t in assoc(u, :teams),
        join: c in Challenge,
        left_join: uc in UserChallenge,
        on: c.id == uc.challenge_id and u.id == uc.user_id,
        select: %{team: t.name, user: u.name, challenge: c.name, completed: is_nil(uc.id)}
      )
    )
1 Like
3

Hey thanks for the reply,
my user is similar

schema "users" do
    field(:name, :string)
    field(:password_hash, :string)
    field(:password, :string, virtual: true)
    field(:score, :integer, default: 0)
    belongs_to(:role, Userteam1.Web.Role)
    belongs_to(:team, Userteam1.Web.Team)
    timestamps()
  end

it is not connected with the challenge table yet, but a many to many relation seems reasonable yes,

also so this implies that a challenges has a completed field, for now mine is like this:

schema "challenges" do
field(:name, :string)
field(:description, :string)
field(:difficulty, :integer, default: 1)

timestamps()
  end
4

Or use the existence of the users_challenges record as indication that the challenge was completed by that user.

1 Like