Optimize the Ecto.Query using Ecto.SubQuery

Questions / Help
,
1

I want to combine two queries into 1 query to optimize my code. I tried my own by getting error.
Also, In doc, it is given that Subqueries are currently only supported in the from and join fields.. So, does following can be done or not. And, if not then how could I optimized them.

Check my implementation-

1st Query

    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,
        preload: [applicant: ap],
        join: a in AppliedOpening,
        on: a.job_opening_id == pipeline.job_opening_id and a.applicant_id == pipeline.applicant_id,
        join: applicant in Applicant,
        on: applicant.id == pipeline.applicant_id,
        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]
      )

2nd Query

    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,
        preload: [applicant: ap],
        join: a in AppliedOpening,
        on: a.job_opening_id == pipeline.job_opening_id and a.applicant_id == pipeline.applicant_id,
        where: a.state == ^applied_state,
        join: applicant in Applicant,
        on: applicant.id == pipeline.applicant_id,
        order_by: [asc: applicant.name]
      )

Query that I tried, but not working

      sub_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,
      preload: [applicant: ap]

      cond do
        result == "unattempted" || result == "skipped" ->
          query = Ecto.subquery(sub_query),
          join: a in AppliedOpening,
          on: a.job_opening_id == pipeline.job_opening_id and a.applicant_id == pipeline.applicant_id,
          where: a.state == ^applied_state,
          join: applicant in Applicant,
          on: applicant.id == pipeline.applicant_id,
          order_by: [asc: applicant.name]
        true ->
          query = Ecto.subquery(sub_query),
          join: a in AppliedOpening,
          on: a.job_opening_id == pipeline.job_opening_id and a.applicant_id == pipeline.applicant_id,
          join: applicant in Applicant,
          on: applicant.id == pipeline.applicant_id,
          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

In my just above code, getting error on 2nd line just after cond do that is- syntax error before: ','
Your help will be appreciated. Also, if you know more better or optimized way let me know.

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
3

Here is What I Tried and working perfectly-

    pre_query =
      from(pipeline in CandidatePipeline,
        join: ap in assoc(pipeline, :applicant),
        where: pipeline.job_opening_id == ^job_id and pipeline.status == ^status,
        preload: [applicant: ap]
      )

    sub_query =
      cond do
        status == "rejected" || (status == "active" && stage_id == 0) ->
          pre_query

        true ->
          from pipeline in pre_query,
            where: pipeline.job_stage_id == ^stage_id
      end

    query =
      cond do
        result in ["unattempted", "skipped"] ->
          from pipeline in sub_query,
            join: a in AppliedOpening,
            on:
              a.job_opening_id == pipeline.job_opening_id and
                a.applicant_id == pipeline.applicant_id,
            where: a.state == ^result,
            join: applicant in Applicant,
            on: applicant.id == pipeline.applicant_id,
            order_by: [asc: applicant.name]

        true ->
          from pipeline in sub_query,
            join: a in AppliedOpening,
            on:
              a.job_opening_id == pipeline.job_opening_id and
                a.applicant_id == pipeline.applicant_id,
            join: applicant in Applicant,
            on: applicant.id == pipeline.applicant_id,
            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