Laravel新手求助：如何关联查询并展示指定sdoh_category分类数据

1. 定义模型与关联 #

首先确保你的sdoh_category表包含category_type字段（用于区分分类类型：residence、water_source、electricity_source）、name字段（存储分类名称）和主键id。然后在两个模型中建立关联：

HouseholdHead 模型

// app/Models/HouseholdHead.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class HouseholdHead extends Model
{
    protected $fillable = ['family_name', 'residence_type', 'water_source', 'electricity_source'];

    // 关联居住类型分类
    public function residenceCategory()
    {
        return $this->belongsTo(SdohCategory::class, 'residence_type');
    }

    // 关联水源分类
    public function waterCategory()
    {
        return $this->belongsTo(SdohCategory::class, 'water_source');
    }

    // 关联电力来源分类
    public function electricityCategory()
    {
        return $this->belongsTo(SdohCategory::class, 'electricity_source');
    }
}

SdohCategory 模型

// app/Models/SdohCategory.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class SdohCategory extends Model
{
    protected $fillable = ['name', 'category_type'];
}

2. 控制器查询数据 #

在控制器中预加载关联数据（避免N+1查询问题），同时按分类类型筛选下拉选项：

// app/Http/Controllers/HouseholdController.php
namespace App\Http\Controllers;

use App\Models\HouseholdHead;
use App\Models\SdohCategory;

class HouseholdController extends Controller
{
    public function index()
    {
        // 获取家庭数据并预加载关联分类
        $households = HouseholdHead::with([
            'residenceCategory', 
            'waterCategory', 
            'electricityCategory'
        ])->get();

        // 按分类类型获取下拉选项
        $residenceOptions = SdohCategory::where('category_type', 'residence')->get();
        $waterOptions = SdohCategory::where('category_type', 'water_source')->get();
        $electricityOptions = SdohCategory::where('category_type', 'electricity_source')->get();

        return view('table', compact(
            'households', 
            'residenceOptions', 
            'waterOptions', 
            'electricityOptions'
        ));
    }
}

3. 视图展示数据与下拉框 #

在table.blade.php中展示已选中的分类，并渲染对应类型的下拉框：

展示已选分类的表格

<table>
    <thead>
        <tr>
            <th>家庭名称</th>
            <th>居住类型</th>
            <th>水源类型</th>
            <th>电力来源</th>
            <th>操作</th>
        </tr>
    </thead>
    <tbody>
        @foreach($households as $household)
        <tr>
            <td>{{ $household->family_name }}</td>
            <!-- 用可选链式调用避免关联不存在时报错 -->
            <td>{{ $household->residenceCategory?->name ?? '未选择' }}</td>
            <td>{{ $household->waterCategory?->name ?? '未选择' }}</td>
            <td>{{ $household->electricityCategory?->name ?? '未选择' }}</td>
            <td>
                <button class="btn-edit" data-id="{{ $household->id }}">编辑</button>
            </td>
        </tr>
        @endforeach
    </tbody>
</table>

新增/编辑表单的下拉框

<!-- 示例：新增家庭表单 -->
<form action="{{ route('households.store') }}" method="POST">
    @csrf
    <div>
        <label>家庭名称</label>
        <input type="text" name="family_name" required>
    </div>

    <div>
        <label>居住类型</label>
        <select name="residence_type">
            <option value="">请选择</option>
            @foreach($residenceOptions as $option)
            <option value="{{ $option->id }}">{{ $option->name }}</option>
            @endforeach
        </select>
    </div>

    <div>
        <label>水源类型</label>
        <select name="water_source">
            <option value="">请选择</option>
            @foreach($waterOptions as $option)
            <option value="{{ $option->id }}">{{ $option->name }}</option>
            @endforeach
        </select>
    </div>

    <div>
        <label>电力来源</label>
        <select name="electricity_source">
            <option value="">请选择</option>
            @foreach($electricityOptions as $option)
            <option value="{{ $option->id }}">{{ $option->name }}</option>
            @endforeach
        </select>
    </div>

    <button type="submit">提交</button>
</form>

注意事项 #

• 确保sdoh_category表的category_type字段值与控制器查询中的字符串完全一致（比如'residence'），建议用常量或枚举类统一管理，避免拼写错误。
• 如果你的Laravel版本低于8.x，替换可选链式调用?->为三元表达式：$household->residenceCategory ? $household->residenceCategory->name : '未选择'。
• 记得配置对应的路由（比如index、store、update），并在表单中正确引用路由。

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