Laravel项目中Blade模板显示ID与数据库实际值不符求助

问题现象 #

页面显示的条目ID与数据库中存储的真实ID不匹配：

数据库存储数据 #

• 条目1
  • id: 2
  • name: John
  • job: driver
• 条目2
  • id: 3
  • name: Sara
  • job: Nurse

Blade页面显示数据 #

• 条目1
  • id: 1
  • name: John
  • job: driver
• 条目2
  • id: 2
  • name: Sara
  • job: Nurse

相关代码 #

控制器代码 #

public function sponsorship_transfer(Request $request)
{
    $data['q'] = $request->query('q');
    $data['profession_id'] = $request->query('profession_id');
    $data['nationality_id'] = $request->query('nationality_id');
    $data['professions'] = WorkerProfession::all();
    $data['nationalities'] = WorkerNationality::all();

    $query = InWorker::select('in_workers.*', 'worker_statuses.*', 'worker_nationalities.*', 'worker_professions.*')
        ->join('worker_statuses', 'worker_statuses.id', '=', 'in_workers.status_id')
        ->join('worker_nationalities', 'worker_nationalities.id', '=', 'in_workers.nationality_id')
        ->join('worker_professions', 'worker_professions.id', '=', 'in_workers.profession_id')
        ->with(['languages', 'education', 'experience', 'birthday_place', 'media'])
        ->where('status_id', 3)->where(function ($query) use ($data) {
            $query->orWhere('name', 'like', '%' . $data['q'] . '%');
        });

    if ($data['profession_id'])
        $query->where('worker_professions.id', $data['profession_id']);

    if ($data['nationality_id'])
        $query->where('worker_nationalities.id', $data['nationality_id']);


    $data['in_workers'] = $query->get();

    return view('frontend.sponsorship_transfer', $data);
}

Blade代码 #

@foreach ($in_workers as $inWorker)
    <div class="invest-title-tow">
        <h3>{{ $inWorker->id }}</h3>
    </div>
@endforeach

尝试过的修改（无效） #

@foreach ($in_workers as $inWorker)
    <div class="invest-title-tow">
        <h3>{{ $inWorker->ID }}</h3>
    </div>
@endforeach

问题原因 #

查询语句中使用了select('in_workers.*', 'worker_statuses.*', 'worker_nationalities.*', 'worker_professions.*')，而worker_statuses、worker_nationalities、worker_professions这三张表都包含id字段。当查询结果返回时，后面表的id字段会覆盖前面in_workers表的id值，导致页面显示的是关联表的ID而非in_workers的真实ID。

解决方案 #

方案1：为in_workers的id设置别名 #

修改控制器中的select语句，明确指定in_workers.id的别名，并只选择需要的字段以减少冲突：

public function sponsorship_transfer(Request $request)
{
    $data['q'] = $request->query('q');
    $data['profession_id'] = $request->query('profession_id');
    $data['nationality_id'] = $request->query('nationality_id');
    $data['professions'] = WorkerProfession::all();
    $data['nationalities'] = WorkerNationality::all();

    $query = InWorker::select(
            'in_workers.id as worker_id', // 为in_workers的id设置别名
            'in_workers.name',
            'in_workers.status_id',
            'in_workers.nationality_id',
            'in_workers.profession_id',
            // 只选择关联表需要的字段，避免用*导致字段冲突
            'worker_statuses.name as status_name',
            'worker_nationalities.name as nationality_name',
            'worker_professions.name as profession_name'
        )
        ->join('worker_statuses', 'worker_statuses.id', '=', 'in_workers.status_id')
        ->join('worker_nationalities', 'worker_nationalities.id', '=', 'in_workers.nationality_id')
        ->join('worker_professions', 'worker_professions.id', '=', 'in_workers.profession_id')
        ->with(['languages', 'education', 'experience', 'birthday_place', 'media'])
        ->where('status_id', 3)->where(function ($query) use ($data) {
            $query->orWhere('name', 'like', '%' . $data['q'] . '%');
        });

    if ($data['profession_id'])
        $query->where('worker_professions.id', $data['profession_id']);

    if ($data['nationality_id'])
        $query->where('worker_nationalities.id', $data['nationality_id']);

    $data['in_workers'] = $query->get();

    return view('frontend.sponsorship_transfer', $data);
}

同时修改Blade代码，使用别名获取ID：

@foreach ($in_workers as $inWorker)
    <div class="invest-title-tow">
        <h3>{{ $inWorker->worker_id }}</h3>
    </div>
@endforeach

方案2：调整select顺序（不推荐） #

如果一定要使用*选择所有字段，可以将in_workers.*放在最后，这样它的id不会被后面的表覆盖，但这种方式容易引入其他字段冲突，不建议使用：

$query = InWorker::select('worker_statuses.*', 'worker_nationalities.*', 'worker_professions.*', 'in_workers.*')
// ... 其余代码不变

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