Laravel中基于Offtaker字段过滤Site与Apartment的一对多关联

我曾尝试过相关解决方案但无效。需求是查询所有Offtaker及其关联的Site，同时过滤Site下的Apartment，仅保留ID与该Offtaker的apartment_id一致的Apartment条目。

模型代码 #

Offtaker 模型 #

public function sites(){
   return $this->belongsTo(Sites::class,'site_id','id')->select('id','name');
}

Site 模型 #

public function apartment(){
    return $this->hasMany(Apartment::class,'site_id','id');
}

控制器当前代码 #

return Offtakers::select(['id','site_id','offtaker_id','company_id','apartment_id'])
               ->with('sites')
               ->with('sites.apartment',function($query){
                   $query->where(function($query){
                       $query->whereRaw("id = (select apartment_id from offtakers where apartment_id=apartments.id)");
                   });
               })->get();

当前输出 #

{
        "id": 2,
        "site_id": "1",
        "offtaker_id": 6,
        "apartment_id": "8",
        "sites": {
            "id": 1,
            "name": "first site",
            "apartment": [
                {
                    "id": 8,
                    "company_id": 1,
                    "site_id": 1,
                    "project_id": null,
                    "apartment_name": "apartment five",
                    "apartment_description": "test descriptions",
                    "amount": 8000
                },
                {
                    "id": 55,
                    "company_id": 1,
                    "site_id": 1,
                    "project_id": null,
                    "apartment_name": "apartment eleven",
                    "apartment_description": "test descriptions",
                    "amount": 550000
                }
            ]
        }
},

期望输出 #

{
        "id": 2,
        "site_id": "1",
        "offtaker_id": 6,
        "apartment_id": "8",
        "sites": {
            "id": 1,
            "name": "first site",
            "apartment": [
                {
                    "id": 8,
                    "company_id": 1,
                    "site_id": 1,
                    "project_id": null,
                    "apartment_name": "apartment five",
                    "apartment_description": "test descriptions",
                    "amount": 8000
                }
            ]
        }
},

解决方案 #

你当前的whereRaw条件会匹配所有Offtaker中关联该公寓的条目，导致同一个Site下其他Offtaker关联的公寓也被保留。以下两种方法可以解决问题：

方法一：使用whereExists关联当前Offtaker #

通过子查询关联当前主查询的Offtaker ID，确保只保留当前Offtaker对应的公寓：

return Offtakers::select(['id','site_id','offtaker_id','company_id','apartment_id'])
    ->with([
        'sites' => function ($query) {
            $query->select('id', 'name');
        },
        'sites.apartment' => function ($query) {
            $query->whereExists(function ($subquery) {
                $subquery->select(DB::raw(1))
                    ->from('offtakers as o')
                    ->whereColumn('o.apartment_id', 'apartments.id')
                    ->whereColumn('o.id', 'offtakers.id');
            });
        }
    ])->get();

方法二：遍历预加载（适合小数据量） #

先预加载所有Site，再遍历每个Offtaker单独过滤对应的公寓：

$offtakers = Offtakers::select(['id','site_id','offtaker_id','company_id','apartment_id'])
    ->with('sites:id,name')
    ->get();

$offtakers->each(function ($offtaker) {
    $offtaker->sites->load(['apartment' => function ($query) use ($offtaker) {
        $query->where('id', $offtaker->apartment_id);
    }]);
});

return $offtakers;

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