GROUP BY/Aggregation Query

[TC-yWu]

Problem Description

The current implementation of Paginator has compatibility issues with GROUP BY and aggregation queries. While basic pagination may work in some cases, the total_count functionality produces incorrect results when used with aggregated queries.

Current Behavior

When using include_total_count: true with a GROUP BY query, the total count calculation strips away the GROUP BY clause, leading to incorrect counts.

Example

# Original GROUP BY query
query = from(o in Order,
  group_by: o.user_id,
  select: %{user_id: o.user_id, order_count: count(o.id)},
  order_by: o.user_id
)

# This will produce incorrect total_count
Repo.paginate(query, 
  cursor_fields: [:user_id], 
  limit: 10,
  include_total_count: true
)

Root Cause Analysis

The issue lies in the total_count/4 function implementation:

# Current implementation in lib/paginator.ex (lines 354-362)
defp total_count(
       queryable,
       %Config{
         total_count_limit: :infinity,
         total_count_primary_key_field: total_count_primary_key_field
       },
       repo,
       repo_opts
     ) do
  result =
    queryable
    |> exclude(:preload)
    |> exclude(:select)
    |> exclude(:order_by)  # ❌ Excludes ORDER BY but not GROUP BY
    |> select([e], struct(e, [total_count_primary_key_field]))
    |> subquery
    |> select(count("*"))
    |> repo.one(repo_opts)

  {result, false}
end

Problems:

1. The function excludes :order_by but not :group_by, :having, or other aggregation-related clauses
2. It selects a struct from the original table instead of respecting the aggregated result set
3. The final count("*") counts individual rows rather than grouped results

Expected Behavior

For a query like:

SELECT user_id, COUNT(*) as order_count 
FROM orders 
GROUP BY user_id;

The total count should return total number of orders, even if they are grouped by users.