Laravel Eloquent: sum with groupBy
You cannot chain groupBy() onto the result of a sum() call because sum() is an eager-evaluating method. When you execute $document->sum('no_of_pages'), Eloquent immediately sends a SQL query to the database, calculates the aggregate value in PHP memory, and returns a single integer or null. By the time your code attempts to invoke groupBy(), the query has already finished running, and there is no collection of rows left to group. This results in the runtime error you encountered: call to member function groupBy() on non-object.
To achieve grouped sums in Eloquent, you must leverage the underlying Query Builder before any aggregation happens. The solution involves using the groupBy() method on the query instance itself, followed by a raw SQL aggregation or a specific collection method that respects the grouping.
Using Raw Aggregation with getRawSql
The most straightforward approach is to build the query with groupBy(), execute it to get the raw results as an array, and then sum the values in PHP if needed. However, Eloquent provides a cleaner way to handle this directly within the model scope using sum() in combination with groupBy().
You can perform a grouped sum by utilizing the getSum method on the query builder, but note that standard Eloquent models do not have a built-in getSum method that accepts a group clause out of the box. Instead, you should use the database's native aggregation capabilities via raw SQL or specific query builder methods depending on your Laravel version.
A robust pattern involves using the selectRaw method to construct an SQL aggregate statement directly in the query:
$groupedSums = Document::selectRaw('users_editor_id, SUM(no_of_pages) as total_pages')
->groupBy('users_editor_id')
->get()
->toArray();
This returns a collection of objects where each object contains the users_editor_id and the calculated total_pages. This approach bypasses Eloquent's eager-evaluation limitation by delegating the aggregation logic to the database engine before any hydration occurs.
Using sum() with Grouping via Subqueries or Joins
If you strictly require returning a collection of model instances rather than raw arrays, you might consider joining tables or using subqueries, but this often complicates the schema unnecessarily. The raw SQL approach remains the most performant and readable for pure aggregation tasks.
Alternatively, if you are working with Laravel 8 or later, you can utilize the groupBy method in conjunction with avg, min, max, or other aggregate functions that return a collection-like structure when combined with get(). However, standard sum() always returns a scalar value. To get a grouped sum result set, the query must be structured to return multiple rows, each representing a group.
Performance Considerations
One critical trade-off to consider is performance. When you use selectRaw with SUM, the database engine handles the heavy lifting of aggregation. This is significantly faster than fetching all records into memory and summing them in PHP, especially for large datasets. Attempting to calculate sums in a loop after fetching grouped data would defeat the purpose of using an ORM's relationship management features efficiently.
Additionally, ensure that your users_editor_id column has an index if you plan to run this query frequently on large tables. While the database optimizer is smart, explicit indexing on grouping columns ensures consistent performance as your data grows.
Conclusion
The core issue was attempting to group a scalar value returned by an eager-evaluation method. By shifting the logic to the Query Builder level using selectRaw and groupBy, you allow the database to perform the aggregation per group before returning the results. This pattern is standard for reporting requirements where you need totals broken down by specific attributes without fetching the full dataset.
