How to select a value form n+1 subquery?

Phoenix Forum Questions / Help
1

How to do this kind for query in Ecto?

select pui.company_id, pui.employee_id, pui.id as in_id,
       (select puo.id 
          from punch_out puo 
         where pui.employee_id = puo.employee_id 
           and pui.punch_time < puo.punch_time 
         order by puo.punch_time limit 1) as out_id
    from punch_in pui;

IMPORTANT PART IS THE out_id.

2

Instead of a subquery in the select you can use a lateral join.

E.g. as seen here: Lateral joins in Ecto without fragments | justin appears

1 Like
3

I don’t think it’s possible to write an Ecto query that selects directly from a (correlated) subquery, like in your original SQL query, but maybe someone can correct me. The suggestion by @LostKobrakai is valid: a lateral inner join does the trick (probably much more performant). The important part is to name your source binding (with from("punch_in", as: :punch_in)), so you can reference it with Ecto.Query.API.parent_as/1. That’s only possible because you’re using a lateral join.

Given a migration like this:

defmodule Migration do
  use Ecto.Migration

  def change do
    create table(:punch_in) do
      add(:company_id, :bigserial)
      add(:employee_id, :bigserial)
      add(:punch_time, :naive_datetime)
    end

    create table(:punch_out) do
      add(:employee_id, :bigserial)
      add(:punch_time, :naive_datetime)
    end
  end
end

you can get results, similar to your raw SQL query like this:

import Ecto.Query

subquery =
  "punch_out"
  |> select([:id])
  |> where([puo], parent_as(:punch_in).employee_id == puo.employee_id)
  |> where([puo], parent_as(:punch_in).punch_time < puo.punch_time)
  |> order_by(:punch_time)
  |> limit(1)

"punch_in"
|> from(as: :punch_in)
|> join(:inner_lateral, [], ^subquery(subquery), on: true)
|> select([pui, puo], %{
  company_id: pui.company_id,
  employee_id: pui.employee_id,
  in_id: puo.id
})
|> Repo.all()

Replace the literal table names by suitable Schema modules if you have them.