Help with Ecto query fetching nested associations

Questions & Help Questions
1

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?

2

You could try casting the fragment via type to the type of the schema maybe?

3

Clever suggestion! Unfortunately, Ecto does’t like that:

|> join(:inner_lateral, [i, a, u], b in type(^fragment("SELECT * FROM balance WHERE account_id = ? ORDER BY date DESC LIMIT 1", a.id), Balance))

raises:

== Compilation error on file ....ex ==
** (Ecto.Query.CompileError) malformed join `type(^fragment("SELECT * FROM balance WHERE 
account_id = ? ORDER BY date DESC LIMIT 1", a.id()), Balance)` in query expression

It produces this error with and without the ^.

4

Ecto cannot currently support the combination of preloads and fragments I need to make my query work.