Conditional aggregates (e.g. sum) in Ecto DSL

[he...@ryanwinchester.ca]

I am trying to do this in some sort of Ecto DSL:

    %{columns: columns, rows: [row]} = Ecto.Adapters.SQL.query!(Repo,

      "SELECT

         SUM(CASE WHEN t.type='income' THEN t.amount ELSE 0 END) as income,

         SUM(CASE WHEN t.type='expense' THEN t.amount ELSE 0 END) as expense,

         SUM(t.amount) as balance

       FROM transactions t"

    )

Because I need to apply some filters based on some possible params...

Is there any way to do this so I can pipe in some other query macros like where, order_by, etc...?

Thanks,

-Ryan

[Michał Muskała]

You could use fragments, for example:

from t in Transaction,

  select: %{

    income: sum(fragment("CASE WHEN ? = ? THEN ? ELSE 0 END", t.type, "income", t.amount)),

    expense: sum(fragment("CASE WHEN ? = ? THEN ? ELSE 0 END", t.type, "income", t.amount)),

    balance: sum(t.amount)

  }

As you can see, I structured the fragments in a way that they both are the same for income and expense. We can enhance the query syntax using a macro:

defmacro sum_type(table, type) do

  quote do

    sum(fragment("CASE WHEN ? = ? THEN ? ELSE 0 END", 

      unquote(table).type, unquote(type), unquote(table).amount)

  end

end

This allows us to change the query to:

from t in Transaction,

  select: %{

    income: sum_type(t, "income"),

    expense: sum_type(t, "expense"),

    balance: sum(t.amount)

  }

Michał.

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/5aa3ca35-c2ad-4b16-aec2-c9e2a9e143d4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[he...@ryanwinchester.ca]

That worked very well,

Thanks!