Laravel搜索查询中WorkHours ID被User ID替换问题排查求助

问题描述 #

在WorkHours控制器的index方法中，页面首次加载无search参数时，返回的WorkHours数据ID正常；但传入search参数后，$wh->id不再是WorkHours记录的ID，而是关联User记录的ID。对比UserController中无join操作的搜索逻辑却能正常运行，问题出在使用join操作的代码段中。

相关代码 #

WorkHours模型

class WorkHours extends Model
{
    use HasFactory, SoftDeletes;

    protected $fillable = [
        'date',
        'hours',
        'reason',
        'comments',
        'approved'
    ];

    public function user()
    {
        return  $this->belongsTo(User::class);
    }

    public function approver()
    {
        return $this->belongsTo(User::class, 'approver_id');
    }

    public function area()
    {
        return $this->belongsTo(Area::class);
    }
}

有问题的WorkHours控制器index方法

public function index(User $user)
{
    return Inertia::render('WorkHours/Index', [
        'workHours' => WorkHours::query()
            ->when(Request::input('search'), function ($query, $search) {
                $query
                    ->join('users', 'users.id', '=', 'work_hours.user_id')
                    ->where('users.last_name', 'like', "%{$search}%")
                    ->orWhere('users.first_name', 'like', "%{$search}%")
                    ->orWhere('users.member_number', 'like', "%{$search}%");
            })
            ->paginate(20)
            ->withQueryString()
            ->through(fn ($wh) => [
                'id' => $wh->id,
                'hours' => $wh->hours,
                'date' => $wh->date,
                'reason' => $wh->reason,
                'comments' => $wh->comments,
                'area' => $wh->area,
                'approver' => $wh->approver,
                'user' => $wh->user
            ]),
            'filters' => Request::only(['search'])
    ]);
}

原因分析 #

当执行join('users')操作时，work_hours表和users表都包含名为id的字段。SQL查询返回的结果集中，users.id会覆盖work_hours.id（因join后字段优先级导致），而Laravel的Eloquent模型在实例化时，会直接使用结果集中的id字段值，因此最终$wh->id被替换为User的ID。

解决方案 #

方法1：使用whereHas替代join（推荐） #

利用Eloquent的关联查询功能，无需手动join表，既避免字段冲突，又符合Laravel最佳实践：

public function index(User $user)
{
    return Inertia::render('WorkHours/Index', [
        'workHours' => WorkHours::query()
            ->when(Request::input('search'), function ($query, $search) {
                // 通过关联关系过滤用户字段
                $query->whereHas('user', function ($q) use ($search) {
                    $q->where('last_name', 'like', "%{$search}%")
                      ->orWhere('first_name', 'like', "%{$search}%")
                      ->orWhere('member_number', 'like', "%{$search}%");
                });
            })
            ->paginate(20)
            ->withQueryString()
            ->through(fn ($wh) => [
                'id' => $wh->id,
                'hours' => $wh->hours,
                'date' => $wh->date,
                'reason' => $wh->reason,
                'comments' => $wh->comments,
                'area' => $wh->area,
                'approver' => $wh->approver,
                'user' => $wh->user
            ]),
            'filters' => Request::only(['search'])
    ]);
}

方法2：明确指定查询字段，避免冲突 #

如果必须使用join，可通过select指定只查询work_hours的所有字段，同时将搜索条件包裹在闭包里避免逻辑错误：

public function index(User $user)
{
    return Inertia::render('WorkHours/Index', [
        'workHours' => WorkHours::query()
            ->when(Request::input('search'), function ($query, $search) {
                $query
                    ->select('work_hours.*') // 只查询work_hours表的字段
                    ->join('users', 'users.id', '=', 'work_hours.user_id')
                    ->where(function ($q) use ($search) {
                        $q->where('users.last_name', 'like', "%{$search}%")
                          ->orWhere('users.first_name', 'like', "%{$search}%")
                          ->orWhere('users.member_number', 'like', "%{$search}%");
                    });
            })
            ->paginate(20)
            ->withQueryString()
            ->through(fn ($wh) => [
                'id' => $wh->id,
                'hours' => $wh->hours,
                'date' => $wh->date,
                'reason' => $wh->reason,
                'comments' => $wh->comments,
                'area' => $wh->area,
                'approver' => $wh->approver,
                'user' => $wh->user
            ]),
            'filters' => Request::only(['search'])
    ]);
}

方法3：给冲突字段添加别名 #

通过给work_hours.id设置别名，在返回数据时使用该别名：

public function index(User $user)
{
    return Inertia::render('WorkHours/Index', [
        'workHours' => WorkHours::query()
            ->when(Request::input('search'), function ($query, $search) {
                $query
                    ->select('work_hours.id as work_hours_id', 'work_hours.*')
                    ->join('users', 'users.id', '=', 'work_hours.user_id')
                    ->where(function ($q) use ($search) {
                        $q->where('users.last_name', 'like', "%{$search}%")
                          ->orWhere('users.first_name', 'like', "%{$search}%")
                          ->orWhere('users.member_number', 'like', "%{$search}%");
                    });
            })
            ->paginate(20)
            ->withQueryString()
            ->through(fn ($wh) => [
                'id' => $wh->work_hours_id, // 使用别名获取WorkHours的ID
                'hours' => $wh->hours,
                'date' => $wh->date,
                'reason' => $wh->reason,
                'comments' => $wh->comments,
                'area' => $wh->area,
                'approver' => $wh->approver,
                'user' => $wh->user
            ]),
            'filters' => Request::only(['search'])
    ]);
}

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