Spatie Query Builder Aggregates + Model Casting for Clean API Responses

When using Spatie Query Builder’s AllowedInclude::sum(), the aggregate attributes come back as numeric strings from MySQL. Here’s how to make them proper integers.

The Problem

// Controller
AllowedInclude::sum('votes_sum_votes', 'votes', 'votes')

// API Response
{
  "total_votes": "5",  // String! Not ideal.
  "total_upvotes": "3"
}

The Solution: Model-Level Casting

1. Use camelCase in the first argument (display name):

// Controller
QueryBuilder::for(Comment::query())
    ->allowedIncludes([
        AllowedInclude::sum('totalVotes', 'votes', 'votes'),
        AllowedInclude::sum('totalUpvotes', 'upvotes', 'votes'),
        AllowedInclude::sum('totalDownvotes', 'downvotes', 'votes'),
    ]);

2. Cast the aggregate attributes in your model:

// Comment model
protected function casts(): array
{
    return [
        // Cast the withSum attributes
        'votes_sum_votes' => 'integer',
        'upvotes_sum_votes' => 'integer',
        'downvotes_sum_votes' => 'integer',
    ];
}

3. Use whenAggregated() in your resource with null coalescing:

// CommentResource
public function toMeta(Request $request): array
{
    return [
        'total_votes' => $this->whenAggregated('votes', 'votes', 'sum', $this->votes_sum_votes ?? 0),
        'total_upvotes' => $this->whenAggregated('upvotes', 'votes', 'sum', $this->upvotes_sum_votes ?? 0),
        'total_downvotes' => $this->whenAggregated('downvotes', 'votes', 'sum', $this->downvotes_sum_votes ?? 0),
    ];
}

Result

{
  "meta": {
    "total_votes": 5,
    "total_upvotes": 3,
    "total_downvotes": 0
  }
}

Why this pattern works

• Model casts handle type conversion automatically
• whenAggregated() only includes the field when the sum was loaded
• ?? 0 ensures you never return null
• No runtime overhead – casts are applied during hydration