Skip to content

Instantly share code, notes, and snippets.

@narrowtux
Created Jan 20, 2017
Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment