Laravel查询员工数据出现重复条目问题求助

问题根源 #

你的代码同时使用了Eloquent预加载（with()）和数据库JOIN查询，引发了三个核心问题：

1. 预加载与JOIN同时拉取关联数据，叠加后造成员工条目重复；
2. SELECT语句中部分字段别名（如sub_department）和预加载的关联关系名称重名，引发数据冲突；
3. 若关联表存在多条匹配记录，JOIN操作会让主表（employees）的同一条记录被多次返回。

解决方案 #

方案一：使用Eloquent预加载（推荐） #

放弃手动JOIN，完全依赖Laravel的预加载机制处理关联，再通过数据格式化得到需要的字段，这是Eloquent的最佳实践，代码更易维护且避免重复。

步骤1：确保Employee模型定义所有关联关系

// app/Models/Employee.php
class Employee extends Model
{
    public function department()
    {
        return $this->belongsTo(Department::class);
    }

    public function sub_department()
    {
        return $this->belongsTo(Department::class, 'sub_department_id');
    }

    public function first_supervisor()
    {
        return $this->belongsTo(Supervisor::class, 'first_supervisor_id');
    }

    public function second_supervisor()
    {
        return $this->belongsTo(Supervisor::class, 'second_supervisor_id');
    }

    public function reportingTo()
    {
        return $this->belongsTo(Employee::class, 'reporting_to_id');
    }

    public function position()
    {
        return $this->belongsTo(Position::class);
    }

    public function country()
    {
        return $this->belongsTo(Country::class);
    }

    public function city()
    {
        return $this->belongsTo(City::class);
    }

    public function attendanceTime()
    {
        return $this->belongsTo(AttendanceTime::class, 'attendance_time_id');
    }

    public function payFrequency()
    {
        return $this->belongsTo(PayFrequency::class, 'pay_frequency_id');
    }

    public function dutyType()
    {
        return $this->belongsTo(DutyType::class, 'duty_type_id');
    }

    public function rateType()
    {
        return $this->belongsTo(RateType::class, 'rate_type');
    }
}

步骤2：修改查询代码并格式化数据

$all_employees = Employee::with([
    'department',
    'sub_department',
    'first_supervisor',
    'second_supervisor',
    'reportingTo',
    'position',
    'country',
    'city',
    'attendanceTime',
    'payFrequency',
    'dutyType',
    'rateType',
])
->orderBy('employees.id', 'DESC')
->get();

// 格式化数据，将关联字段平级返回（可根据接口需求调整）
$formattedEmployees = $all_employees->map(function ($employee) {
    return array_merge($employee->toArray(), [
        'attendance_time' => $employee->attendanceTime?->start_time,
        'city' => $employee->city?->city,
        'country' => $employee->country?->country,
        'pay_frequency' => $employee->payFrequency?->frequency,
        'duty_type' => $employee->dutyType?->name,
        'rate_type' => $employee->rateType?->name,
        'position_name' => $employee->position?->name,
        'sub_department_name' => $employee->sub_department?->department,
        'supervisor_name' => $employee->first_supervisor?->name,
    ]);
});

return $this->customSuccessResponseWithPayload($formattedEmployees);

方案二：保留JOIN并去重 #

如果一定要使用JOIN查询，需要去掉预加载，同时通过distinct()或groupBy()确保员工条目唯一，还要避免字段别名冲突。

$all_employees = Employee::select(
        'employees.*',
        'attendance_times.start_time as attendance_time',
        'cities.city',
        'countries.country',
        'pay_frequencies.frequency as pay_frequency',
        'duty_types.name as duty_type',
        'rate_types.name as rate_type',
        'positions.name as position_name',
        'departments.department as sub_department_name', // 修改别名避免冲突
        'supervisors.name as first_supervisor_name' // 修改别名更清晰
    )
    ->leftJoin('positions', 'employees.position_id', '=', 'positions.id')
    ->leftJoin('countries', 'employees.country_id', '=', 'countries.id')
    ->leftJoin('supervisors', 'employees.first_supervisor_id', '=', 'supervisors.id')
    ->leftJoin('cities', 'employees.city_id', '=', 'cities.id')
    ->leftJoin('attendance_times', 'employees.attendance_time_id', '=', 'attendance_times.id')
    ->leftJoin('departments', 'employees.sub_department_id', '=', 'departments.id')
    ->leftJoin('pay_frequencies', 'employees.pay_frequency_id', '=', 'pay_frequencies.id')
    ->leftJoin('duty_types', 'employees.duty_type_id', '=', 'duty_types.id')
    ->leftJoin('rate_types', 'employees.rate_type', '=', 'rate_types.id')
    ->orderBy('employees.id', 'DESC')
    ->distinct() // 或使用 ->groupBy('employees.id') 确保唯一
    ->get();

return $this->customSuccessResponseWithPayload($all_employees);

总结 #

优先选择方案一，预加载机制能自动处理关联数据的加载，避免JOIN带来的重复问题，代码也更符合Laravel的设计理念。如果必须使用JOIN，务必去掉预加载并做好去重和字段别名处理。

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