Oh, I misread your post 
What is the id? From your ideal result, it seems like you want to query by agent id. If so, it’s better to iterate by agent_list, and join others and transform the result to your “ideal” result.
Some notes
- If you need a list from a query, then you should chose that table for
from. - You can preload with select - so that you don’t need to fetch all columns, if it matters
- Don’t overuse join to fetch everything in one query - it can be actually slower then running two queries, even index is being used
Here is the one query version - joining everything, taking duplicate common fields, and cleaning up with elixir
query =
from al in AgentLicense,
join: a in Agent,
join: s in Site,
join: o in Organization,
on:
a.id == al.agent_id and
a.site_id == s.id and
s.organization_id == o.id,
where: a.id == ^id,
select: {
%{agent_name: a.name, site_name: s.name, organization_name: o.name},
al # the shole schema; or you may specify %{key: al.key} to select columns
}
{common, licenses} =
query
|> Repo.all()
|> Enum.reduce({nil, []}, fn {common, al}, {_, list} -> {common, [al | list]} end)
Map.put(common, :licenses, licenses)
However, the above approach will consume more db connection bandwidth and client side memory, since the common part is being duplicated.
To avoid that, the simplest solution is to make two queries in a transaction - 1) agent & org with agent id, and 1) agent licenses with one query.
Repo.transaction(fn ->
query = from al in AgentLicense, where: al.agent_id == ^id
licenses = query |> Repo.all()
query =
from a in Agent,
join: s in Site,
join: o in Organization,
on:
a.site_id == s.id and
s.organization_id == o.id,
where: a.id == ^id,
select: %{agent_name: a.name, site_name: s.name, organization_name: o.name}
common = query |> Repo.one()
Map.put(common, :licenses, licenses)
end)
If you just need to access assoc from the record (e.g. agent/site/org from agent license) - then you can just use preload without join - then it ecto will preload them in separate query automatically.
