@OvermindDL1 - @mbuhot answered my question on StackOverflow a few weeks ago. (Thanks @mbuhot !!)
Here is the response:
Move the fragment into a macro to keep the code clear:
defmacro balance_amount(transaction) do
quote do
fragment("CASE WHEN ? = 'credit' THEN (?) ELSE - (?) END",
unquote(transaction).type, unquote(transaction).amount, unquote(transaction).amount)
end
end
Create a subquery with %{user_id, merchant_id, balance}
def user_merchant_balance do
from t in Transaction,
select: %{user_id: t.user_id, merchant_id: t.merchant_id, balance: sum(balance_amount(t))},
group_by: [t.user_id, t.merchant_id]
end
Join to the subquery from the main query, use the map update syntax %{|} to populate the virtual field:
def merchant_customers(merchant_id) do
from u in User,
join: b in subquery(user_merchant_balance()), on: u.id == b.user_id,
where: b.merchant_id == ^merchant_id,
select: %{u | balance: b.balance}
end
This is the final code I ended up using:
@doc """
Retrieves all customers with balances for a merchant
"""
@spec find_merchant_customers_with_balance(struct) :: [%MyApp.User{}]
def find_merchant_customers_with_balance(%{"merchant_id" => id}) do
q = merchant_customers(id)
Repo.all(q)
end
@doc """
User balance calcuation macro
"""
defmacro balance_amount(transaction) do
quote do
fragment("CASE WHEN ? = 'debit' THEN (?) ELSE - (?) END",
unquote(transaction).type, unquote(transaction).amount, unquote(transaction).amount)
end
end
@doc """
Query for retrieving user balance for merchant
"""
@spec user_merchant_balance :: %Ecto.Query{}
def user_merchant_balance do
from t in Transaction,
select: %{user_id: t.user_id, merchant_id: t.merchant_id, balance: sum(balance_amount(t))},
group_by: [t.user_id, t.merchant_id]
end
@doc """
Query for retrieving all the merchants for a customer with abalance
"""
@spec merchant_customers(number) :: %Ecto.Query{}
def merchant_customers(merchant_id) do
from u in User,
join: b in subquery(user_merchant_balance()), on: u.id == b.user_id,
where: b.merchant_id == ^merchant_id,
select: %{u | balance: b.balance}
end
I still need to figure out if I can get the balance
attribute in the returned query returned as a Money.Ecto
struct instead of a raw number. Any ideas on how I might be able to do that?
On the customer_balance
function I transform it afterward into Money
, but that doesn’t return as a User
def customer_balance(user_id: user_id, merchant_id: merchant_id) do
q = from t in Transaction,
select: fragment("SUM(CASE WHEN ? = 'credit' THEN (?) ELSE - (?) END)", t.type, t.amount, t.amount),
where: t.user_id == ^user_id and t.merchant_id == ^merchant_id
balance = Repo.one(q) || 0
do_balance(balance, "asset")
|> Money.new(:USD)
end