lol.ex

  def descendants(organization_id, truncation, filter \\ from o in __MODULE__) do
    sub = from os in filter, where: os.organization_id == ^organization_id and os.truncation == ^truncation, select: [os.organization_id, os.parent_organization_id, os.id, ^1, os.datetime, os.truncation]

    query = from outer in __MODULE__,
        join: t in fragment("""
        WITH RECURSIVE search_orgs(organization_id, parent_organization_id, id, depth, datetime, truncation) AS (
              ?
              UNION ALL
                SELECT
                  o.organization_id,
                  o.parent_organization_id,
                  o.id,
                  os.depth + 1,
                  o.datetime,
                  o.truncation
                FROM organization_statistics o, search_orgs os
                WHERE o.parent_organization_id = os.organization_id AND o.datetime = os.datetime AND o.truncation = os.truncation
        ) SELECT id FROM search_orgs
        """, ^sub),
        on: outer.id == t.id

    {subq_source, subq_params} = Ecto.Adapters.SQL.to_sql(:all, AppsrvWeb.Repo, sub)

    [join] = query.joins
    join = update_in(join.source, fn ({:fragment, [], [{:raw, before}, _, {:raw, behind}]}) ->
      {:ok, i} = Agent.start(fn () -> 0 end)
      source =
        [before, subq_source, behind]
        |> :erlang.iolist_to_binary
        |> String.split(~r/\$[0-9]+/, include_captures: true, trim: true)
        |> Enum.map(fn
          <<"$", _>> ->
            next_id = Agent.get_and_update(i, &({&1, &1 + 1}))
            {:expr, {:^, [], [next_id]}}
          raw -> {:raw, raw}
        end)

        Agent.stop(i)
      {:fragment, [], source}
    end)
    join = put_in(join.params, subq_params)
    query = put_in(query.joins, [join])
  end