How to perform Dynamic Schema Query

Questions / Help
,
19
  • I didn’t know about named bindings
  • I think the fundamental issue here is that you can get quite far with monolithic query expressions without ever needing to understand the binding system. From that perspective the entire Composition section of the documentation is important and relevant here.

The query expression in the OP is monolithic, i.e. the entire query is defined in one pass - no composition is used.

The solution to the problem requires composition - but to use composition one has to understand the binding system.

Now I can’t guarantee that this is correct as this is written freehand but I would build this stage after stage to see if I’m getting the expected results:

query =
  from(pipeline in CandidatePipeline, as: :pipeline)
  |> where([pipeline: p], p.job_opening_id == ^job_id)
  |> where([pipeline: p], p.job_stage_id == ^stage_id)
  |> where([pipeline: p], p.status == ^status)
  |> join(:inner, [pipeline: p], a in AppliedOpening, as: :opening,
    on: a.job_opening_id == p.job_opening_id and a.applicant_id == p.applicant_id,)
  |> join(:inner, [opening: o], r in Result, as: :result, on: r.applied_opening_id == o.id)
  |> where([result: r], r.state == ^result)
  |> join(:inner, [pipeline: p], a in Applicant, as: :applicant, a.id == p.applicant_id)
  |> order_by([result: r, applicant: a], desc_nulls_last: r.percent, asc: a.name)

query =
  case result do
    "drop" ->
      where([opening: o], o.state not in ["attempted", "unattempted"]
    "unattempted" ->
      where([opening: o], o.state == "unattempted"
    _ ->
      where([opening: o], o.state == "attempted")
  end

As @LostKobrakai pointed out the only named binding you actually need is :opening (in his example :joined). I just did the whole query in a compositional style to get the point across.

So given the original query:

# note the change to:
# join: a in AppliedOpening, as: :opening,
query = from(pipeline in CandidatePipeline,
        join: ap in assoc(pipeline, :applicant), # ??? is this actually needed ???
        where:
          pipeline.job_opening_id == ^job_id and pipeline.job_stage_id == ^stage_id and
            pipeline.status == ^status,
        preload: [applicant: ap],
        join: a in AppliedOpening, as: :opening,
        on: a.job_opening_id == pipeline.job_opening_id and a.applicant_id == pipeline.applicant_id,
        join: applicant in Applicant,              # ??? ... because this
        on: applicant.id == pipeline.applicant_id, # seems to do it all over again duplicating rows ???
        join: r in Result,
        on: r.applied_opening_id == a.id,
        where: r.state == ^result,
        order_by: [desc_nulls_last: r.percent, asc: applicant.name]
      )

query =
  case result do
    "drop" ->
      where([opening: o], o.state not in ["attempted", "unattempted"]
    "unattempted" ->
      where([opening: o], o.state == "unattempted"
    _ ->
      where([opening: o], o.state == "attempted")
  end
1 Like