Laravel中如何获取同公司同事的已获得奖励？

一、原代码的核心问题 #

1. 模型关联定义错误 #

• GainedReward与User的关联：gained_rewards表通过user_id关联users表，每个GainedReward属于一个User，应该用belongsTo，而非hasMany。
• GainedReward与Reward的关联：gained_rewards表通过reward_id关联rewards表，每个GainedReward对应一个Reward，同样应该用belongsTo，而非hasMany。

2. API查询逻辑错误 #

• 用GainedReward::find(1)->users查询同事完全偏离需求，这只会取ID为1的奖励记录关联的用户，再过滤公司ID，逻辑错误。
• $rewards = GainedReward::all()会获取所有奖励记录，未和目标公司的同事关联。
• 返回JSON时用$colleagues作为键，会把集合对象当作键名，语法错误且结构不符合预期。

二、修正方案 #

1. 修正模型关联 #

修改GainedReward模型的关联方法：

class GainedReward extends Model
{
    // 每个获得记录属于一个用户
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    // 每个获得记录对应一个奖励
    public function reward()
    {
        return $this->belongsTo(Reward::class);
    }
}

可选：在User模型中添加关联，方便后续查询：

class User extends Model
{
    // 一个用户拥有多条获得奖励记录
    public function gainedRewards()
    {
        return $this->hasMany(GainedReward::class);
    }
}

2. 修正API方法 #

方式一：从用户表出发，关联奖励记录

public function getColleaguesGainedRewards(Request $request)
{
    $companyId = $request->company_id;

    // 获取目标公司所有用户，预加载他们的获得奖励及奖励详情
    $colleagues = User::where('company_id', $companyId)
        ->with(['gainedRewards.reward'])
        ->get();

    // 整理成预期格式
    $result = $colleagues->map(function ($user) {
        return [
            'user_id' => $user->id,
            'name' => $user->name,
            'gained_rewards' => $user->gainedRewards->map(function ($gainedReward) {
                return [
                    'reward_id' => $gainedReward->reward->id,
                    'reward_name' => $gainedReward->reward->name
                ];
            })
        ];
    });

    return response()->json([
        "status" => "success",
        "data" => $result
    ], 200);
}

方式二：从获得奖励表出发，过滤公司用户

public function getColleaguesGainedRewards(Request $request)
{
    $companyId = $request->company_id;

    // 获取目标公司用户的所有获得奖励，预加载用户和奖励详情
    $gainedRewards = GainedReward::with(['user', 'reward'])
        ->whereHas('user', function ($query) use ($companyId) {
            $query->where('company_id', $companyId);
        })
        ->get();

    // 按用户分组整理结果
    $result = $gainedRewards->groupBy('user.id')->map(function ($rewards, $userId) {
        $user = $rewards->first()->user;
        return [
            'user_id' => $userId,
            'name' => $user->name,
            'gained_rewards' => $rewards->map(function ($gainedReward) {
                return [
                    'reward_id' => $gainedReward->reward->id,
                    'reward_name' => $gainedReward->reward->name
                ];
            })
        ];
    })->values();

    return response()->json([
        "status" => "success",
        "data" => $result
    ], 200);
}

三、预期返回结果 #

查询公司ID为1（Abc公司）时，返回JSON如下：

{
    "status": "success",
    "data": [
        {
            "user_id": 1,
            "name": "John",
            "gained_rewards": [
                {
                    "reward_id": 1,
                    "reward_name": "Day Off"
                },
                {
                    "reward_id": 2,
                    "reward_name": "Coffee"
                }
            ]
        },
        {
            "user_id": 2,
            "name": "Jack",
            "gained_rewards": [
                {
                    "reward_id": 2,
                    "reward_name": "Coffee"
                }
            ]
        }
    ]
}

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