Optimize the Ecto.Query using Ecto.SubQuery

Questions / Help
,
2
  • You can’t preload in a query you are going to use for a subquery
  • So your subquery focuses on the information that the rest of the query needs
  • You don’t actually have a usecase for a subquery
  • You need composition which was explained in your last topic

Again freehand, no guarantees but something along the lines of:

query =
  from(pipeline in CandidatePipeline,
    join: ap in assoc(pipeline, :applicant),
    where: pipeline.job_opening_id == ^job_id
      and pipeline.job_stage_id == ^stage_id
      and pipeline.status == ^status,
    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, as: :applicant,
      on: applicant.id == pipeline.applicant_id,
    preload: [applicant: ap]
  )

query =
  if result == "unattempted" or result == "skipped" do
    from([opening: a, applicant: applicant] in query,
      where: a.state == ^applied_state,
      order_by: [asc: applicant.name]
    )

  else
    from([opening: a, applicant: applicant] in query,
      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]
    )
  end
2 Likes