Laravel中间表关联实现：获取公司部门销售及对应关联信息

由于你的销售记录直接关联Company_Department中间表的ID，最清晰的处理方式是给这个中间表创建独立模型，以此为桥梁建立所有关联关系。

1. 创建中间表模型 CompanyDepartment.php #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class CompanyDepartment extends Model
{
    // 若表名是蛇形命名（如company_departments）可省略此配置，Laravel会自动识别
    protected $table = 'Company_Department';

    // 关联所属公司
    public function company()
    {
        return $this->belongsTo(Company::class);
    }

    // 关联所属部门
    public function department()
    {
        return $this->belongsTo(Department::class);
    }

    // 关联该公司部门下的所有销售记录
    public function sales()
    {
        return $this->hasMany(Sale::class, 'company_department_id');
    }
}

2. 更新原有模型 #

修改 Company.php #

class Company extends Model
{
    // 保留原有多对多关联（根据业务需求可选）
    public function departments(): BelongsToMany
    {
        return $this->belongsToMany(Department::class, 'Company_Department');
    }

    // 新增关联到公司-部门中间模型
    public function companyDepartments()
    {
        return $this->hasMany(CompanyDepartment::class, 'company_id');
    }

    // 可选：直接关联该公司的所有销售记录
    public function sales()
    {
        return $this->hasManyThrough(Sale::class, CompanyDepartment::class, 'company_id', 'company_department_id');
    }
}

修改 Department.php #

class Department extends Model
{
    // 修正原有关联方法名（多对多对应复数形式）
    public function companies(): BelongsToMany
    {
        return $this->belongsToMany(Company::class, 'Company_Department');
    }

    // 新增关联到公司-部门中间模型
    public function companyDepartments()
    {
        return $this->hasMany(CompanyDepartment::class, 'department_id');
    }

    // 可选：直接关联该部门的所有销售记录
    public function sales()
    {
        return $this->hasManyThrough(Sale::class, CompanyDepartment::class, 'department_id', 'company_department_id');
    }
}

3. 创建 Sale.php 模型 #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Sale extends Model
{
    protected $table = 'Sales'; // 蛇形命名表可省略此配置

    // 关联对应的公司-部门记录
    public function companyDepartment()
    {
        return $this->belongsTo(CompanyDepartment::class, 'company_department_id');
    }

    // 可选：直接关联所属公司
    public function company()
    {
        return $this->hasOneThrough(Company::class, CompanyDepartment::class, 'id', 'id', 'company_department_id', 'company_id');
    }

    // 可选：直接关联所属部门
    public function department()
    {
        return $this->hasOneThrough(Department::class, CompanyDepartment::class, 'id', 'id', 'company_department_id', 'department_id');
    }
}

实现需求 #

需求1：获取指定公司部门的销售记录 #

// 方式1：通过公司部门ID直接查询
$companyDepartment = CompanyDepartment::find($companyDepartmentId);
$sales = $companyDepartment->sales;

// 方式2：通过公司+部门组合定位后查询
$companyDepartment = CompanyDepartment::where('company_id', $companyId)
    ->where('department_id', $departmentId)
    ->first();
$sales = $companyDepartment->sales;

// 方式3：直接从销售模型筛选
$sales = Sale::where('company_department_id', $companyDepartmentId)->get();

需求2：根据销售记录获取对应的公司部门 #

// 获取单条销售记录的公司部门信息
$sale = Sale::find($saleId);
$companyDepartment = $sale->companyDepartment;

// 直接获取关联的公司和部门
$company = $sale->company;
$department = $sale->department;

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