Laravel数据透视表问题：医学实验室应用开发技术问询

Hey there! Let's work through that pivot table issue you're facing in your Laravel medical lab app. It sounds like you need to link patient checkups to selected tests while storing the results for each test—totally a common scenario with many-to-many relationships in Laravel, and we can solve this cleanly with pivot fields or even a dedicated pivot model if you need extra logic.

1. 数据库结构调整 #

First, let's make sure your database supports the relationship properly. Assuming you have a tests table already, you'll need:

• A patients table (to store patient core info: id, name, dob, patient_number, etc.)
• A checkups table (to represent a single patient checkup: id, patient_id, checkup_date, etc.)
• A pivot table checkup_test (to link checkups to tests and store results):
  • Fields: id, checkup_id, test_id, result, created_at, updated_at

Note: Laravel expects pivot tables to use snake_case, alphabetical order (so checkup_test instead of test_checkup), but you can override this in your model relationships if needed.

2. 定义模型关联 #

Next, set up the relationships in your Laravel models to handle the pivot data:

Checkup Model #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class Checkup extends Model
{
    protected $fillable = ['patient_id', 'checkup_date'];

    // Link to the patient this checkup belongs to
    public function patient(): BelongsTo
    {
        return $this->belongsTo(Patient::class);
    }

    // Link to tests, with the `result` pivot field included
    public function tests(): BelongsToMany
    {
        return $this->belongsToMany(Test::class, 'checkup_test')
                    ->withPivot('result') // Include the result field from the pivot
                    ->withTimestamps(); // Auto-manage created/updated timestamps
    }
}

Test Model #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class Test extends Model
{
    protected $fillable = ['name', 'description', 'reference_range'];

    // Reverse link to checkups
    public function checkups(): BelongsToMany
    {
        return $this->belongsToMany(Checkup::class, 'checkup_test')
                    ->withPivot('result')
                    ->withTimestamps();
    }
}

Patient Model #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Patient extends Model
{
    protected $fillable = ['name', 'dob', 'gender', 'patient_number'];

    // A patient can have multiple checkups
    public function checkups(): HasMany
    {
        return $this->hasMany(Checkup::class);
    }
}

3. 处理Checkup页面表单提交 #

Your Checkup page needs a form where users select tests and enter results. Here's a sample Blade template structure:

<form method="POST" action="{{ route('checkups.store') }}">
    @csrf

    <!-- Patient Selection -->
    <div class="mb-4">
        <label for="patient_id" class="block font-medium text-gray-700">患者</label>
        <select name="patient_id" id="patient_id" required class="mt-1 block w-full">
            @foreach($patients as $patient)
                <option value="{{ $patient->id }}">{{ $patient->name }} ({{ $patient->patient_number }})</option>
            @endforeach
        </select>
    </div>

    <!-- Test Selection & Result Inputs -->
    <div class="mb-6">
        <h3 class="text-lg font-semibold mb-3">选择检测项目</h3>
        @foreach($tests as $test)
            <div class="flex items-center mb-2">
                <input type="checkbox" name="tests[{{ $test->id }}][id]" value="{{ $test->id }}" id="test_{{ $test->id }}" class="mr-2">
                <label for="test_{{ $test->id }}" class="mr-4">{{ $test->name }}</label>
                <input type="text" name="tests[{{ $test->id }}][result]" placeholder="输入检测结果" class="border rounded px-2 py-1">
            </div>
        @endforeach
    </div>

    <button type="submit" class="bg-blue-500 text-white px-4 py-2 rounded">保存检查记录</button>
</form>

Then handle the submission in your CheckupController:

public function store(Request $request)
{
    // Validate incoming data
    $validated = $request->validate([
        'patient_id' => 'required|exists:patients,id',
        'tests' => 'required|array',
        'tests.*.id' => 'exists:tests,id',
        'tests.*.result' => 'nullable|string', // Adjust validation based on your needs (e.g., numeric for lab values)
    ]);

    // Create the checkup record
    $checkup = Checkup::create([
        'patient_id' => $validated['patient_id'],
        'checkup_date' => now(),
    ]);

    // Format test data for Laravel's attach() method
    $testData = [];
    foreach ($validated['tests'] as $test) {
        if (!empty($test['id'])) {
            $testData[$test['id']] = ['result' => $test['result'] ?? ''];
        }
    }

    // Link tests to the checkup with their results
    $checkup->tests()->attach($testData);

    return redirect()->route('checkups.show', $checkup)->with('success', '检查记录已成功保存！');
}

4. 显示/编辑已有检查记录 #

If you need to edit an existing checkup, here's how to pre-fill the form with saved data:

<form method="POST" action="{{ route('checkups.update', $checkup) }}">
    @csrf
    @method('PUT')

    <!-- Patient Info (read-only for example) -->
    <div class="mb-4">
        <label class="block font-medium text-gray-700">患者</label>
        <p class="mt-1">{{ $checkup->patient->name }} ({{ $checkup->patient->patient_number }})</p>
        <input type="hidden" name="patient_id" value="{{ $checkup->patient->id }}">
    </div>

    <!-- Test Selection & Pre-filled Results -->
    <div class="mb-6">
        <h3 class="text-lg font-semibold mb-3">检测项目</h3>
        @foreach($tests as $test)
            <div class="flex items-center mb-2">
                <input type="checkbox" name="tests[{{ $test->id }}][id]" value="{{ $test->id }}" id="test_{{ $test->id }}" 
                       class="mr-2" {{ $checkup->tests->contains($test->id) ? 'checked' : '' }}>
                <label for="test_{{ $test->id }}" class="mr-4">{{ $test->name }}</label>
                <input type="text" name="tests[{{ $test->id }}][result]" 
                       value="{{ $checkup->tests->find($test->id)?->pivot->result ?? '' }}" 
                       placeholder="输入检测结果" class="border rounded px-2 py-1">
            </div>
        @endforeach
    </div>

    <button type="submit" class="bg-green-500 text-white px-4 py-2 rounded">更新检查记录</button>
</form>

And the corresponding update method:

public function update(Request $request, Checkup $checkup)
{
    $validated = $request->validate([
        'patient_id' => 'required|exists:patients,id',
        'tests' => 'required|array',
        'tests.*.id' => 'exists:tests,id',
        'tests.*.result' => 'nullable|string',
    ]);

    // Update checkup details
    $checkup->update([
        'patient_id' => $validated['patient_id'],
    ]);

    // Format new test data
    $newTestData = [];
    foreach ($validated['tests'] as $test) {
        if (!empty($test['id'])) {
            $newTestData[$test['id']] = ['result' => $test['result'] ?? ''];
        }
    }

    // Sync tests: removes unselected tests, adds/updates selected ones
    $checkup->tests()->sync($newTestData);

    return redirect()->route('checkups.show', $checkup)->with('success', '检查记录已更新！');
}

5. 进阶：自定义Pivot模型 #

If you need extra logic for the test results (like validation against reference ranges), create a custom pivot model:

namespace App\Models;

use Illuminate\Database\Eloquent\Relations\Pivot;

class CheckupTestPivot extends Pivot
{
    protected $fillable = ['result'];

    // Example: Validate if the result falls within the test's reference range
    public function isValidResult(): bool
    {
        $test = $this->test;
        if (!$test->reference_range) {
            return !empty($this->result);
        }

        // Add your custom validation logic here (e.g., numeric range checks)
        list($min, $max) = explode('-', $test->reference_range);
        return is_numeric($this->result) && $this->result >= $min && $this->result <= $max;
    }
}

Then update the tests() relationship in the Checkup model to use this pivot:

public function tests(): BelongsToMany
{
    return $this->belongsToMany(Test::class, 'checkup_test')
                ->using(CheckupTestPivot::class) // Use the custom pivot
                ->withPivot('result')
                ->withTimestamps();
}

内容的提问来源于stack exchange，提问作者Abdul Raheem Ghani