Working with Ecto and joins

Phoenix Forum Questions / Help
, ,
1

Hello, Elixir Forum,

I’m trying to build a query that joins four tables together and returns a map that looks like this:

IDEAL RESULT

%{
  agent_domain: "Agent Domain",
  agent_hostname: "Agent Hostname",
  organization_name: "Organization Name",
  site_name: "Site Name",
  licenses: [
    %{
      ...license keys and values.
    },
    %{
      ...license keys and values.
    }
  ]
}

So far, I have this query working:

query =
  from o in Organization,
  join: s in Site,
  join: a in Agent,
  join: al in AgentLicense,
  on:
    o.id == s.organization_id and
    s.id == a.site_id and
    a.id == al.agent_id,
  where: [id: ^id],
  select: %{
    organization_name: o.name,
    site_name: s.name,
    agent_domain_name: a.domain_name,
    agent_hostname: a.hostname,
    activation_licenses: al
  }

Which returns a structure that looks like this:

ACTUAL RESULT

[
  %{
    license: %{
      ...license keys and values.
    },
   agent_domain: "Agent Domain",
   agent_hostname: "Agent Hostname",
   organization_name: "Organization Name",
   site_name: "Site Name"
  },
  %{
    license: %ActiphyPortal.Agents.AgentLicense{
      ...license keys and values.
    },
    agent_domain: "Agent Domain",
    agent_hostname: "Agent Hostname",
    organization_name: "Organization Name",
    site_name: "Site Name"
  }
]

The ACTUAL RESULT makes sense based on my query. My question is whether or not there is a way in Ecto to keep this as one query but have Ecto nest the result so that information isn’t duplicated, like the IDEAL RESULT structure above?

Thanks in advance for your help.

2

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.

1 Like
3

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

1 Like
4

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.

5

Oh, I misread your post :man_facepalming:

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.

1 Like