Returning a single record with a list of multiple associated joins

Questions / Help
1

I’m working on a query that will take a record, look for an association (via two joins) and return some information about the original record plus all associated records (kind of like a preload) as a single record.

I have three models, LeadTime Location and Blackout.

LeadTime belongs_to Location
Location has_many LeadTimes and Location has_many Blackouts
Blackout belongs_to Location but not all Blackouts are associated with a location.

When querying a LeadTime I want to select/3 some information about the LeadTime and also return all associated blackouts.

 LeadTime
    |> from(as: :lead_time)
    |> where([lead_time: lt], lt.id == ^id)
    |> join(:inner, [lead_time: lt], lc in Location, on: lc.id == lt.location_id, as: :location)
    |> join(:inner, [location: lc], ltb in Blackout,
      on: lc.id == ltb.location_id,
      as: :blackouts
    )
    |> select(
      [lead_time: lt, blackouts: bo],
      %{
        name: lt.name,
        blackouts: [bo]
      }
    )
    |> Repo.one()

This works fine when there is one blackout, however when there are two returns two records one for each Blackout withe same LeadTime info.

From what I understand the join is returning multiple records and so the select statement is going to iterate over as many as there are and create maps for each one, however I’d like it to just create one and aggregate all of the blackouts into the same map. I realize the above approach may not be the way to do that, I’m open to any and all suggestions.

1 Like
2

A couple possibilities jump out:

  • return whole LeadTime structs instead of maps, and use Ecto.Query.preload like |> preload([blackouts: bo], [blackouts: bo]) (assuming there is a blackouts association on LeadTime)

  • use a database-side aggregate function and group_by lt.id - array_agg or jsonb_agg can be useful. Not likely what you want since you’re looking for intact Blackout structs in the result

  • keep the one-row-per-blackout query, but post-process it a similar-but-different way to how Repo.preload does:

|> select([lead_time: lt, blackout: bo], {lt.name, bo})
|> Repo.all()
|> Enum.group_by(fn {name, _bo} -> name end, fn {_name, bo} -> bo end)
|> then(fn %{name => blackouts} -> %{name: name, blackouts: blackouts} end)
  • this query could be split into two simpler ones (fetch the LoadTime / fetch the blackouts for that LoadTime) if your application doesn’t pass in the ID of LeadTimes with no blackouts
1 Like
3

Thanks. I ended up mostly going with #3. Since blackouts aren’t associated with LeadTime (I could have associated them but that came with other issues in this particular case).

Essentially I did this:

      Map.merge(lead_time, %{
        blackouts: get_val(lead_time, :blackouts)
      })

One thing I’m curious about in your code: then(fn %{name => blackouts} -> when I tried to implement that I got an error saying cannot use variable name as map key inside a pattern. Map keys in patterns can only be literals (such as atoms, strings, tuples, and the like).
Could you explain that a bit more?

4

LOL that’s just me posting code without trying it :man_shrugging:

I always forget that maps are a special case for matching because a single-element map means something different (“match any map that has this key”) than a single-element kwlist:

# doesn't compile
fn %{name => value} -> {name, value} end 

# matches any map that has a `:foo` key and binds the value to value
fn %{:foo => value} -> {:foo, value} end

# matches a kwlist with exactly one element and binds key and value
fn [{key, value}] -> {key, value} end