Laravel中1:1与1:M关联查询返回空集合问题排查

数据库结构说明 #

• contacts表仅包含id字段，与person、company为一对一关联，与emails、phones、addresses为一对多关联。

使用的扩展包 #

尝试通过staudenmeir/belongs-to-through和staudenmeir/eloquent-has-many-deep扩展包，利用HasManyThrough与BelongsToThrough关联来获取特定个人或公司的所有邮箱、电话及地址。

当前模型关联代码 #

class Company extends Model
{
    public function phones(): HasManyThrough
    {
        return $this->hasManyThrough(Phone::class, Contact::class, 'id', 'contact_id');
    }

    public function emails(): HasManyThrough
    {
        return $this->hasManyThrough(Email::class, Contact::class, 'id', 'contact_id');
    }

    public function addresses(): HasManyThrough
    {
        return $this->hasManyThrough(Address::class, Contact::class, 'id', 'contact_id');
    }
}


class Contact extends Model
{    
    public function addresses()
    {
        return $this->hasMany(Address::class);
    }

    public function businesses()
    {
        return $this->hasMany(Business::class);
    }

    public function person()
    {
        return $this->hasOne(Person::class);
    }
}

class Email extends Model
{
    use HasFactory, \Znck\Eloquent\Traits\BelongsToThrough;

    public function contact()
    {
        return $this->belongsTo(Contact::class, 'contact_id', 'id');
    }

    public function person(): BelongsToThrough
    {
        return $this->belongsToThrough(Person::class, Contact::class);
    }

    public function company(): BelongsToThrough
    {
        return $this->belongsToThrough(Company::class, Contact::class);
    }
}

问题现象 #

执行以下查询时，Company模型的emails关联返回空集合，但实际数据库中存在对应关联的邮箱数据：

>>> \App\Models\Company::query()->with('emails')->where('id', 101)->first();
=> App\Models\Company {#4953
     id: 101,
     company_name: "Barnes and Small LLC",
     status: "Active",
     contact_id: 1107,
     emails: Illuminate\Database\Eloquent\Collection {#4967
       all: [],
     },
   }

>>> \App\Models\Email::where('contact_id', 1107)->get();
=> Illuminate\Database\Eloquent\Collection {#4980
     all: [
       App\Models\Email {#5008
         id: 1,
         contact_id: 1107,
         email_address: "tihin@mailinator.com",
       },
       App\Models\Contacts\Email {#5007
         id: 2,
         contact_id: 1107,
         email_address: "kecyg@mailinator.com",
       },
     ],
   }

>>> \App\Models\Contact::find(1107);
=> App\Models\Contact {#4946
     id: 1107,
     created_at: null,
     updated_at: null,
   }

问题原因与修复方案 #

错误点 #

Company模型中hasManyThrough的参数传递错误。hasManyThrough的参数顺序及含义为：

1. 最终关联的目标模型（如Email）
2. 中间关联模型（如Contact）
3. 中间模型的主键字段（对应目标模型的外键关联字段）
4. 目标模型关联中间模型的外键字段
5. 当前模型关联中间模型的外键字段（可选，默认是当前模型的主键）

原代码中遗漏了第5个参数，且关联逻辑错误：当前Company表是通过contact_id字段关联到Contact的id，但原代码用Company.id去匹配Contact.id，导致关联条件不成立。

修正后的Company模型代码 #

class Company extends Model
{
    public function phones(): HasManyThrough
    {
        return $this->hasManyThrough(Phone::class, Contact::class, 'id', 'contact_id', 'contact_id');
    }

    public function emails(): HasManyThrough
    {
        return $this->hasManyThrough(Email::class, Contact::class, 'id', 'contact_id', 'contact_id');
    }

    public function addresses(): HasManyThrough
    {
        return $this->hasManyThrough(Address::class, Contact::class, 'id', 'contact_id', 'contact_id');
    }
}

参数说明（以emails关联为例） #

return $this->hasManyThrough(
    Email::class,       // 目标模型：要关联的Email表
    Contact::class,     // 中间模型：关联的中间表Contact
    'id',               // 中间模型的主键：Contact.id，对应Email的contact_id
    'contact_id',       // 目标模型的外键：Email.contact_id，关联Contact.id
    'contact_id'        // 当前模型的外键：Company.contact_id，关联Contact.id
);

修改后重新执行查询，即可正确获取到关联的邮箱数据。

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