Working with Ecto and joins

The data returned by the db will always be a list of lists of column values. Ecto has some rudimentary means of mapping the list of columns to structs or maps, … and deduplicating things like assocs, but in your case being parts of the return values deduplicated and parts not (without a known assoc tree) is not possible without postprocessing results afterwards.

You can use Repo.one/2 to fetch single result (instead of a list) from a query.

Here’s what I get out of this query (if it doesn’t match your application, the advice that follows is probably wrong):

• organization has many sites
• site has many agents
• agent has many agent licenses

What it looks like your query is ultimately looking for is a result for each agent containing the agent’s licenses and some metadata about the agent. That query would look like:

from(a in Agent,
  join: s in Site, on: s.id == a.site_id,
  join: o in Organization, on: o.id == s.organization_id
  where: o.id == ^id,
  preload: [:licenses, site: :organization]
)
|> Enum.map(fn agent ->
  %{
    organization_name: agent.site.organization.name,
    site_name: agent.site.name,
    agent_domain_name: agent.domain_name,
    agent_hostname: agent.hostname,
    activation_licenses: agent.licenses
  }
end)

(adjust licenses above if your association isn’t named that)

This loads a little more data than strictly necessary from organizations and sites but will group the results like you’re looking for.

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.