I’m trying to find the total amount of winnings for a specific backer in my database.

    query =
      from b in Gaming.Stakes.Backer,
        left_join: bwt in assoc(b, :wallet_transactions),
        left_join: bpwt in assoc(b, :backer_promotional_wallet_transactions),
        where: b.id == ^backer.id,
        where: bwt.type == "winnings" or bpwt.type == "winnings",
        group_by: b.id

    from e in subquery(query),
      select: [e.backer_id, e.wallet_sums + e.promotional_wallet_sums]

I’m a bit stuck on how to do the coalesce, and the nested select. Ultimately what I need a single value to come back from the database.

This is the raw SQL query that works as intended and give me the data I need. I’m trying to get this into Ecto code.

     SELECT
       backer_id,
       wallet_sums + promotional_wallet_sums AS total
     FROM
       (
         SELECT
           backers.id AS backer_id,
           SUM( COALESCE( wallet_transactions.amount,
           (
             'USD',
             0
           )
           :: money_with_currency ) ) AS wallet_sums,
           SUM( COALESCE( promotional_wallet_transactions.amount,
           (
             'USD',
             0
           )
           :: money_with_currency ) ) AS promotional_wallet_sums
         FROM
           backers
           LEFT JOIN
             backer_wallet_transactions
             ON backer_wallet_transactions.backer_id = backers.id
           LEFT JOIN
             wallet_transactions
             ON wallet_transactions.id = backer_wallet_transactions.wallet_transaction_id
           LEFT JOIN
             backer_promotional_wallet_transactions
             ON backer_promotional_wallet_transactions.backer_id = backers.id
           LEFT JOIN
             promotional_wallet_transactions
             ON promotional_wallet_transactions.id = baker_promotional_wallet_transactions.promotional_wallet_transaction_id
         WHERE
           backers.id = 'c9ffd955-3333-3333-3333-578346b33333'
           AND
           (
             wallet_transactions.type = 'winnings'
             OR promotional_wallet_transactions.type = 'winnings'
           )
         GROUP BY
           backers.id
       )
       sums