Laravel中分组求和Points表数据并关联获取Account、Users表信息的方法

Got it, let's break this down step by step. First, I'll start by defining logical relationships between your tables (since you didn't specify them explicitly—this is based on common Laravel patterns):

Step 1: Set Up Model Relationships #

First, make sure your Eloquent models have the correct relationships defined. The battlepointable_id field suggests a polymorphic relationship, but I'll also cover a non-polymorphic setup just in case.

Point Model #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\MorphTo;

class Point extends Model
{
    // Polymorphic relationship (if battlepointable_id links to multiple models)
    public function battlepointable(): MorphTo
    {
        return $this->morphTo();
    }

    // OR non-polymorphic (if it only links to Account)
    // public function account()
    // {
    //     return $this->belongsTo(Account::class, 'battlepointable_id');
    // }
}

Account Model #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\MorphMany;

class Account extends Model
{
    // Link to User
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    // Polymorphic inverse for Points
    public function points(): MorphMany
    {
        return $this->morphMany(Point::class, 'battlepointable');
    }

    // OR non-polymorphic inverse
    // public function points()
    // {
    //     return $this->hasMany(Point::class, 'battlepointable_id');
    // }
}

User Model #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class User extends Model
{
    public function accounts(): HasMany
    {
        return $this->hasMany(Account::class);
    }
}

Step 2: Query Options #

Now that relationships are set up, here are three clean ways to get your desired results:

Option 1: Use Eloquent's Built-in withSum (Simplest) #

If you want to get each Account with its total points and linked User info, this is the most straightforward approach:

use App\Models\Account;

$accountStats = Account::query()
    ->with('user') // Preload User data to avoid N+1 queries
    ->withSum('points', 'value') // Automatically calculates SUM(points.value) as points_sum
    ->get();

// Access the data like this:
foreach ($accountStats as $account) {
    echo "Account ID: {$account->id}, User Name: {$account->user->name}, Total Points: {$account->points_sum}";
}

Option 2: Group Points Directly with Eloquent #

If you need to start from the Points table and group by battlepointable_id (Account IDs), while pulling in Account and User data:

use App\Models\Point;
use App\Models\Account;

$pointStats = Point::query()
    // Filter to only Points linked to Accounts (critical for polymorphic relationships)
    ->where('battlepointable_type', Account::class)
    ->select('battlepointable_id')
    ->selectRaw('SUM(value) as total_points')
    ->groupBy('battlepointable_id')
    // Preload the linked Account and its User
    ->with(['battlepointable.user'])
    ->get();

// Access the data:
foreach ($pointStats as $stat) {
    echo "Account ID: {$stat->battlepointable_id}, User Name: {$stat->battlepointable->user->name}, Total Points: {$stat->total_points}";
}

Option 3: Query Builder with Joins (For Full Control) #

If you prefer using raw SQL joins for more flexibility:

use Illuminate\Support\Facades\DB;

$stats = DB::table('points')
    ->select(
        'accounts.id as account_id',
        'users.id as user_id',
        'users.name as user_name',
        'accounts.name as account_name',
        DB::raw('SUM(points.value) as total_points')
    )
    ->join('accounts', 'points.battlepointable_id', '=', 'accounts.id')
    ->join('users', 'accounts.user_id', '=', 'users.id')
    // Ensure we only target Points linked to Accounts (if polymorphic)
    ->where('points.battlepointable_type', \App\Models\Account::class)
    ->groupBy('accounts.id', 'users.id', 'users.name', 'accounts.name')
    ->get();

Important Notes #

• If you're using MySQL with strict mode enabled (default in Laravel), all non-aggregated fields in your SELECT must be included in the GROUP BY clause (which is why we include accounts.id, users.id, etc., in Option 3).
• For non-polymorphic relationships, you can remove the where('battlepointable_type', ...) condition since battlepointable_id only links to Accounts.

内容的提问来源于stack exchange，提问作者rahel