How do I perform an aggregated SQL query like sum on the filtered query?

[joerichsen]

Hi

Flop is super great and useful. I hope someone can help me with this. We use Flop to filter a table of invoices. Each invoice has an amount.

How do I sum the amounts of the filtered invoices? (before pagination)

I have tried with a combination of Flop.validate, Flop.query, and Flop.filter but I don't seem to be able to make it work :-|

Does anybody have any tips for how to resolve this issue?

[joerichsen]

OK, using validate and filter seems to work in the simple cases. But I have a custom field with custom filtering for which validate_and_run seems to work fine. But validate and then filter seems to query for the customer field directly on the invoices table.

[woylie]

That sounds as if you're not passing the for option to the filter function, in which case Flop wouldn't know about the custom field. If you use multiple of the Flop functions, it's always a good idea to assign the necessary opts to a variable and pass it to each function. In your case, probably something like this:

flop_opts = [for: MyApp.Invoice]

with {:ok, flop} <- Flop.validate(params, flop_opts) do
  MyApp.Invoice
  |> Flop.filter(flop, flop_opts)
  |> ...
end

If all you want to do is to disable pagination, you can also do this:

Flop.validate_and_run(MyApp.Invoice, params, for: MyApp.Invoice, pagination: false)

or in your case, probably something similar to:

MyApp.Invoice
|> select([i], sum(i.amount))
|> Flop.validate_and_run(params, for: MyApp.Invoice, pagination: false, ordering: false)