I managed to figure it out.
The tricky part was to use the ? fragment to reference the relationship because of it’s indirect in sql -vs- direct in ecto relationship magic nature.
query =
from b in Gaming.Stakes.Backer,
left_join: wt in assoc(b, :wallet_transactions),
left_join: pwt in assoc(b, :promotional_wallet_transactions),
where: b.id == ^backer.id,
where: wt.type == ^"winnings" or pwt.type == ^"winnings",
group_by: b.id,
select: %{
backer_id: b.id,
wallet_sums:
fragment(
"""
SUM( COALESCE( ?.amount,
(
'USD',
0
)
:: money_with_currency ) )
""",
wt
),
promotional_wallet_sums:
fragment(
"""
SUM( COALESCE( ?.amount,
(
'USD',
0
)
:: money_with_currency ) )
""",
pwt
)
}
query =
from e in subquery(query),
select: e.wallet_sums + e.promotional_wallet_sums
Repo.one(query)
