Filtering associations with a join?

Questions / Help
1

Hi all,
I know that we can filter associations (such as belongs_to) with a where option. In my situation, it would be handy if I could filter a belongs_to association with a full query or at least something that includes a join.

Here’s the scenario. We have a schema that defines a field like assigned_to_id like this:

belongs_to(:assigned_to, User)

where User is a schema representing a user record.

Now, we’ve changed the business definition of what “assigned to” means. We want instead for it to be dynamic based on joining to at least one other table. The resulting value is still an id from the User schema but we need to find that user with a bit of joining and whereing.

Given all the code in place that’s already reading and using assigned_to, I was hoping I could do something in the schema definition to make this business logic change nearly invisible to the calling code.

Does such a solution exist?

Given that schema definitions are compile time and not runtime, I’m guessing this is a non-starter.

2

your example is not likely to bring you an useful answers from the community because we don’t know how your schemas / tables changed and what your current code expects to be in place

3

Show us some of the Ecto schemas?

4

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.

5

It sounds like a PatientTask is now fundamentally :assigned_to a CareTeamMember rather than a User i.e. the patient task is owned by a care team member representing whichever care provider/user happens to be currently staffed on that care team for a given role.

If so, you could point the belongs_to at a CareTeamMember and then use has_one ..., through: ... since a CareTeamMember already belongs to User.

schema "patient_tasks" do
  ...
  belongs_to(:assigned_to, CareTeamMember)
  has_one :assigned_user, through: [:care_team_member, :user]
end

schema "care_team_members" do
  belongs_to :user, User
  ...
end

This way PatientTask always points to the correct assigned User handling that task because it’s directly based on the fluid CareTeamMember association.

6

Thanks for the idea. This is definitely something that we could try. It certainly captures the logical association that we want to describe.

I think our hope is that the association would be dynamic but that seems like it’s a bridge too far. Too much magic. Whatever metaphor we want here. :slight_smile: