As requested, here’s a fuller example that hopefully provides more context.
We have a User schema like this:
schema "users" do
field(:username, :string)
field(:email, :string)
field(:first_name, :string)
field(:last_name, :string)
field(:prefix, :string)
field(:suffix, :string)
field(:full_name, :string, read_after_writes: true)
field(:phone, PhoneNumber)
field(:is_deleted, :boolean, default: false)
field(:deleted_at, :utc_datetime_usec)
field(:permissions, {:array, :map}, virtual: true)
field(:care_team_roles, {:array, :string}, virtual: true)
field(:care_team_ids, {:array, :string}, virtual: true)
field(:address_line1, :string)
field(:address_line2, :string)
field(:city, :string)
field(:state, :string)
field(:postal_code, :string)
has_many(:user_roles, UserRole, on_replace: :delete)
has_many(:care_team_members, CareTeamMember)
timestamps()
end
and so everyone one of our employees has one of these records.
We assign Patient Tasks to those employees and the PatientTask schema looks like:
schema "patient_tasks" do
field(:due_at, :date)
field(:instructions, :string)
field(:priority, :string, default: @normal_priority)
field(:status, :string)
field(:notes, :string)
field(:grouper, Ecto.UUID, autogenerate: true)
field(:start_date, :date)
belongs_to(:patient, Patient)
belongs_to(:assigned_to, User)
belongs_to(:task, TaskType,
foreign_key: :type,
type: :string,
references: :type,
source: :task_type
)
belongs_to(:subtask, TaskType,
foreign_key: :subtask_type,
type: :string,
references: :type,
source: :subtask_type
)
belongs_to(:created_by, User)
belongs_to(:modified_by, User)
timestamps()
end
In that schema, the most relevant bit is the :assigned_to
association. That links the PatientTask to the User directly. In our existing code, we have a number of queries where we are retrieving the PatientTasks that are assigned to a given user with something like:
where(query, [patient_task: pt], pt.assigned_to_id == ^user_id)
Now, our business would rather PatientTask be associated “dynamically” to the User who is assigned as to a particular role as a Care Team Member. So, there are some new schemas in play.
We have the CareTeamMembers schema which tells us which user is on which CareTeam:
schema "care_team_members" do
belongs_to :user, User
belongs_to :care_team, CareTeam
belongs_to :care_team_role, CareTeamRole, foreign_key: :role, references: :key, type: :string
timestamps()
end
and that is also associated to the CareTeamRole schema:
schema "care_team_roles" do
field :label, :string
timestamps()
end
So, our existing query from above:
where(query, [patient_task: pt], pt.assigned_to_id == ^user_id)
now needs to be able to find the right CareTeamMember before applying that user_id
to the query:
def get_patient_nav(patient_id) do
from(
u in User,
left_join: ctm in assoc(u, :care_team_members),
left_join: ct in assoc(ctm, :care_team),
left_join: p in assoc(ct, :patients),
where: p.id == ^patient_id and ctm.role == "health_navigator",
select: u.id
)
|> Repo.one()
end
What I was dreaming about is being able to make the :assigned_to
association in PatientTask
be something more dynamic than just a key to User
. That way, I could remove the boilerplate of this CareTeamMember look up from all the places we have to do it and have it be behind the scenes in the schema itself.
This way, we could centralize the business logic of “who should do this task” and have the benefit of it being dynamic because the Users change their CareTeamMember association somewhat frequently.
Hopefully, that paints a fuller picture of what I’m thinking about.