Query question: coalesce with an aggregate functon

I have two models: Order (the following only shows relevant fields):

schema "orders" do 
   field(:total_amount, :decimal)
   has_many(:order_line_items, OrderLineItem)
end 

and OrderLineItem

schema "orders" do 
   field(:price, :decimal)
   belongs_to(:order, Order)
end

For Order the total_amount field gets calculated later on based on various factors such as tax and shipping, however we sometimes need to display an amount before we have the final figure. In such cases it makes sense to sum the price field of all associated OrderLineItems. I’d like to coalesce/2 these fields, but I keep getting group_by errors:

column "o0.id" must appear in the GROUP BY clause or be used in an aggregate function

This is what I have so far:

Order 
|> from(as: :order)
|> where([order: o], is_nil(o.deleted_at)
|> join(:left, [order: o], oli in assoc(o, :order_line_items), as: :oli)
|> select([order: o, oli: oli], %{total: total_or_sum(oli.price, o.total_amount})
|> Repo.all()
...

 defmacro total_or_sum(column, column2) do
    quote do
      fragment("coalesce(sum(?), ?)", unquote(column), unquote(column2))
    end
 end

total_or_sum was suggested by @dogbert on SO, I’ve also just tried using coalesce/2 without the macro and outside of a fragment and got similar results.

I’m not even sure this is possible.

From the error, you likely just need to add a group_by with the appropriate id.

Sorry I’m not super comfy with the macro syntax but I think it would be:

|> group_by([order: o], [o.id])

Something like that.

1 Like

Thanks a lot! I have a bunch of other associations joined in, not used in aggregate functions, but I guess you need to include each association.id in the group_by statement.

1 Like