Aggregates and grouped queries

The query Flop builds can be reused for aggregates, and a grouped query can be passed to Flop like any other.

An aggregate over the filtered rows

A total for the whole filtered set, rather than for the current page, needs the filters without the limit. Flop.filter/3 applies the filter parameters and nothing else.

def list_pets(params) do
  opts = [for: Pet]

  with {:ok, flop} <- Flop.validate(params, opts) do
    {pets, meta} = Flop.run(Pet, flop, opts)
    total = Pet |> Flop.filter(flop, opts) |> Repo.aggregate(:sum, :price)

    {:ok, {pets, meta, total}}
  end
end

The sum covers every pet that matches the filters. Repo.aggregate/3 also takes :count, :avg, :min and :max.

If you pass the query returned by Flop.query/3 instead, the aggregate is computed over one page, since that query includes the limit and the offset.

Grouped queries

Group and select in the query you pass to Flop.

query =
  from p in Pet,
    group_by: p.species,
    select: %{species: p.species, total: sum(p.price)}

Flop.validate_and_run(query, %{page: 1, page_size: 10}, for: Pet)

Filters apply before the grouping, since Flop adds them to the WHERE clause. meta.total_count is the number of groups, because Flop counts a grouped query over a subquery.

Sorting by an aggregate

Name the aggregate in the select clause with Ecto.Query.API.selected_as/2 and declare it as an alias field.

@derive {Flop.Schema,
         filterable: [:species],
         sortable: [:species, :total],
         adapter_opts: [alias_fields: [:total]]}

query =
  from p in Pet,
    group_by: p.species,
    select: %{
      species: p.species,
      total: p.price |> sum() |> selected_as(:total)
    }

params = %{order_by: [:total], order_directions: [:desc]}

Flop.validate_and_run(query, params, for: Pet)

An alias field cannot be filtered on, because PostgreSQL does not allow a select alias in a WHERE clause, and it cannot be used as an order field with cursor pagination. Use page or offset pagination when the order is an aggregate.

To sort a list of records by an aggregate of their children instead of grouping them, use a lateral join, as in the to-many joins recipe.