Doing a postgresql coalesce with custom type in Ecto

Questions / Help
1

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
2

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)
2 Likes
3

Do I understand correctly that the intent is to sum :money_with_currency amounts, but treat NULL amounts as 0 amounts in the currently aggregating currency (USD for your example?). That might be possible and reasonable to implement as an additional aggregate function and I’ll take a look (I’m the author of ex_money which it appears you may be using).

1 Like
4

Yes you are correct that would be awesome thank you for all your work on this package. we would be in quite the pickle without ex_money!

5

I’ve been revisiting this topic (sorry for the delay). I have opened an issue to track requirements and issues for implementation.

The Postgres documentation says:

Another bit of default behavior for a “strict” transition function is that the previous state value is retained unchanged whenever a null input value is encountered. Thus, null values are ignored. If you need some other behavior for null inputs, do not declare your transition function as strict; instead code it to test for null inputs and do whatever is needed.

And the current aggregate function is implemented as STRICT:

CREATE OR REPLACE FUNCTION money_state_function(agg_state money_with_currency, money money_with_currency)
RETURNS money_with_currency
IMMUTABLE
STRICT
LANGUAGE plpgsql
AS $$
  DECLARE
    expected_currency char(3);
    aggregate numeric;
    addition numeric;
  BEGIN
    if currency_code(agg_state) IS NULL then
      expected_currency := currency_code(money);
      aggregate := 0;
    else
      expected_currency := currency_code(agg_state);
      aggregate := amount(agg_state);
    end if;

    IF currency_code(money) = expected_currency THEN
      addition := aggregate + amount(money);
      return row(expected_currency, addition);
    ELSE
      RAISE EXCEPTION
        'Incompatible currency codes. Expected all currency codes to be %', expected_currency
        USING HINT = 'Please ensure all columns have the same currency code',
        ERRCODE = '22033';
    END IF;
  END;
$$;


CREATE AGGREGATE sum(money_with_currency)
(
  sfunc = money_state_function,
  stype = money_with_currency
);

I recall that @LostKobrakai had some very good input when I was originally implementing this function so perhaps he will have a point of view.

Basically I can remove the STRICT tag from the function and handle NULL inputs directly so that part is clear. Postgres expects a NULL return for an aggregate if there are now non-NULL values and I can manage that too.

What I’m not sure about is whether overall this is the right thing to do. Treating NULL values as zero money amounts is probably not what most developers would expect.

Therefore I think the right approach is to implement a separate aggregate function that very explicitly treats NULL values as zero-amount money values. It will not only be explicit, but it won’t risk changing the behaviour unexpectantly for current users.

The downside is that it means the built-in support for SUM in Ecto won’t be usable since two functions in the database with the same parameters are not possible. However a fragment should make that transparent (I can add a macro to do that).

Thoughts, suggestions very welcome.

6

In my opinion sum for money should work exactly as sum works on numeric/decimal columns would, granted all currencies are the same. Any different behavior should be a custom and separately documented aggregation function.

2 Likes