How can I pass variables to an elixir/ecto subquery?

Question

I have this query in ecto below:

query =
      from(tx in Treatment,
        join: ax in assoc(tx, :ax_sheet),
        join: ev in assoc(ax, :visit),
        join: evr in assoc(ev, :visit_revisions),
        where: is_nil(tx.visit_revision_id),
        where: not is_nil(ev.current_revision_id),
        select: %{
          id: tx.id,
          visit_revision_id: coalesce(ev.current_revision_id, subquery(
            from vr in VisitRevision,
            where: vr.visit_id == ^ev.id and vr.inserted_at <= ^tx.marked_at,
            order_by: [desc: vr.inserted_at],
            limit: 1,
            select: vr.id
          ))
        }
      )

I am facing an issue that where I get the following error on this line below:

 where: vr.visit_id == ^ev.id and vr.inserted_at <= ^tx.marked_at,

undefined variable ev
undefined variable tx

My question is very simple. How do I resolve this? I've read that the subquery can't retrieve what I've previously defined on the outer query, so how can I simply just pass in the ids that I need? I'm new to elixir and am hoping this is one of those easy ones to fix. Thank you.

Answer 1

You can give the parent root table and parent join tables names using as: ... and access them using parent_as:.

This should work:

query =
      from(tx in Treatment,
        as: :tx,
        join: ax in assoc(tx, :ax_sheet),
        join: ev in assoc(ax, :visit),
        as: :ev,
        join: evr in assoc(ev, :visit_revisions),
        where: is_nil(tx.visit_revision_id),
        where: not is_nil(ev.current_revision_id),
        select: %{
          id: tx.id,
          visit_revision_id: coalesce(ev.current_revision_id, subquery(
            from vr in VisitRevision,
            where: vr.visit_id == parent_as(:ev).id and vr.inserted_at <= parent_as(:tx).marked_at,
            order_by: [desc: vr.inserted_at],
            limit: 1,
            select: vr.id
          ))
        }
      )