I appreciate the help on this. I tried the macro route, but I ran into some compilation issues with how I was using it. It almost feels like there should be an array concept in Ecto that expects a subquery so something like this could be written:
call_recording_query =
from(cr in CallRecording,
where: parent_as(:call).id == cr.call_id,
select: cr.id
)
from(c in Call,
as: :call,
left_lateral_join: call_recording_ids in array(call_recording_query),
select: %{
id: c.id,
call_recording_ids: call_recording_ids
}
)
Maybe something Ecto could support only for lateral joins? I could see this being useful in situations where preloads aren’t an option. I’d be happy to work on a PR to Ecto so.
Fortunately we were able to refactor this query to use preloads where we only selected the fields that we needed. Like so:
from(c in Call,
as: :call,
join: u in assoc(c, :user),
as: :user,
left_join: cp in assoc(c, :contact_phone),
as: :contact_phone,
left_join: con in assoc(cp, :contact),
as: :contact,
left_join: pc in PatientContact,
on: pc.patient_id == c.patient_id and pc.contact_id == con.id,
left_join: ca in assoc(c, :call_audit),
as: :call_audit,
left_join: au in assoc(ca, :auditor),
as: :auditor,
left_join: p in assoc(c, :patient),
as: :patient,
left_join: d in assoc(c, :disposition),
as: :disposition,
left_join: cr in assoc(c, :call_recordings),
preload: [
user: u,
patient: p,
disposition: d,
call_recordings: cr,
call_audit: {ca, auditor: au},
contact_phone: {cp, contact: {con, patient_contacts: pc}}
],
order_by: [desc: c.started_at],
select:
map(c, [
:id,
:zoom_call_id,
:started_at,
:ended_at,
:call_type,
:purpose,
:call_uuid,
:notes,
:patient_id,
:thrio_workitem_id,
:zoom_external_phone_number,
user: [:id, :first_name, :last_name],
patient: [:id, :first_name, :last_name, :preferred_language],
contact_phone: [
:id,
:phone_number,
:status,
contact: [:id, :name, patient_contacts: [:id, :status]]
],
call_audit: [:id, :status, auditor: [:full_name]],
call_recordings: [:id, :call_id, :s3_key, :transcript_raw]
])
)
|> Call.with_duration()
