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
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 LeadTime
s with no blackouts
1 Like
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?
LOL that’s just me posting code without trying it 
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