How to select a value form n+1 subquery?

How to do this kind for query in Ecto?

select pui.company_id, pui.employee_id, as in_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;


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

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)

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

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

import Ecto.Query

subquery =
  |> 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)

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

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