How does one use a macro fragment in order_by for a virtual field

Questions / Help
1

We’re using a macro calling fragment to build out a CASE statement that calculates the discount amount, a virtual field. Is there a clean way to reuse this CASE statement / fragment / macro in an order_by call?

defmacro calc_discount_amount(account) do
  quote do
    fragment(
      "CASE
          WHEN ? = 'buyers_club' THEN
              round(? * 0.95 * (? / 100), 0)::integer
          WHEN ? = 'employee' AND ? >= 80 THEN
              ?
          ELSE
              round(? * (? / 100), 0)::integer
          END",
      unquote(account).account_type,
      unquote(account).amount,
      unquote(account).discount_percentage,
      unquote(account).account_type,
      unquote(account).discount_percentage,
      unquote(account).amount,
      unquote(account).amount,
      unquote(account).discount_percentage
    )
  end
end

def with_discount_amount(query \\ base()) do
  from e in query, select_merge: %{discount_amount: type(calc_discount_amount(e), Money.Ecto.Type)}
end

We’re currently duplicating the case statement which returns results in the expected order.

order = :asc

...

order_by(query, [a], [
  {^order,
   fragment(
      "CASE
          WHEN ? = 'buyers_club' THEN
              round(? * 0.95 * (? / 100), 0)::integer
          WHEN ? = 'employee' AND ? >= 80 THEN
              ?
          ELSE
              round(? * (? / 100), 0)::integer
          END",
	      a.account_type,
	      a.amount,
	      a.discount_percentage,
	      a.account_type,
	      a.discount_percentage,
	      a.amount,
	      a.amount,
	      a.discount_percentage
   )}
])
...

I would love to remove this duplication. Any ideas? Would dynamic help?

1 Like
2

What happens if you use the macro in the order_by:

order = :asc

order_by(query, [a], [
  {^order, calc_discount_amount(a)}
])

Since you mention you’re “currently duplicating” it, I assume that something goes awry…

1 Like
3

Thanks Jones!

As it turns out, I needed to import the macro from the module where it was defined.