Hi guys, I’ve been stuck for a couple of hours on this query I’m hoping you can help me with. I am querying data for an API response.
There are five schemas I’m dealing with. User, Institution, Account, Balance and BalanceType. A User
has multiple Institution
, each Institution
has multiple Account
, each Account
has a time series of Balance
, for which each Balance
has one BalanceType
. I want to return all the institutions and accounts for a user with the most recent balance (of type “current”) for each account.
I can get bits and pieces to work, but not the whole thing. This is the closest I’ve been able to get, but it’s missing two things: 1) preloading Balance
and 2) joining/preloading BalanceType
.
Institution
|> join(:inner, [i], a in assoc(i, :account))
|> join(:inner, [i, a], u in assoc(a, :user))
|> join(:inner_lateral, [i, a, u], b in fragment("SELECT * FROM balance WHERE account_id = ? ORDER BY date DESC LIMIT 1", a.id))
|> where([_, _, u, _], u.id == ^user.id)
|> preload([_, a, _, _], [account: a])
|> Repo.all()
Ecto won’t preload the result of the fragment because it’s not an association. I also can’t seem to join BalanceType
to the fragment explicitly which I tried by putting the below in the pipe after the fragment.
join(:inner, [i, a, u, b], bt in BalanceType, on: b.balance_type_id == bt.id)
It throws: (ArgumentError) argument error :erlang.apply({:join_count, [], Ecto.Query.Builder.Join}, :type, [])
Including Balance
and BalanceType
is possible using keyword syntax, but at the cost of getting the most recent Balance
:
Repo.all(
from i in Institution,
join: a in assoc(i, :account),
join: u in assoc(a, :user),
join: b in assoc(a, :balance),
join: bt in assoc(b, :balance_type),
preload: [account: {a, balance: {b, balance_type: bt}}],
where: u.id == ^user.id,
where: bt.name == "current"
)
In the above, I’d like to replace:
join: b in assoc(a, :balance)
with:
inner_lateral_join: b in fragment("SELECT * FROM balance WHERE account_id = ? ORDER BY date DESC LIMIT 1", a.id)
Unfortunately this doesn’t work because the fragment doesn’t produce a schema which is required to join and preload the Balance
association.
Am I doing something wrong, or is what I’m trying to do just not possible in Ecto?