Laravel用户技能多表关联设计与SQLSTATE[22007]错误解决求助

Hey there! Let's work through fixing your multi-skill form setup and that annoying SQL error you're facing. The root cause is almost certainly mismatched database field types or incorrect handling of relationship data when saving. Let's break this down step by step with clean, standardized code.

First, let's make sure your three tables are structured correctly. The key mistake here is likely putting the level field in the wrong table (it belongs to the pivot table, since it's a user-specific skill attribute) or using mismatched field types.

Users Table (Use Laravel's Default) #

Laravel's default users migration works fine here—no changes needed unless you have custom fields.

Skills Table Migration #

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up()
    {
        Schema::create('skills', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique(); // Ensure skill names are unique
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('skills');
    }
};

Skill_User Pivot Table Migration #

This is where your level field lives, and where the SQL error is probably coming from. Make sure field types match the parent tables:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up()
    {
        Schema::create('skill_user', function (Blueprint $table) {
            $table->unsignedBigInteger('user_id'); // Matches users.id type
            $table->unsignedBigInteger('skill_id'); // Matches skills.id type
            $table->integer('level'); // Store skill level as integer (adjust to string if using labels like "Beginner")
            $table->primary(['user_id', 'skill_id']); // Prevent duplicate skill entries for a user

            // Add foreign key constraints for referential integrity
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->foreign('skill_id')->references('id')->on('skills')->onDelete('cascade');
        });
    }

    public function down()
    {
        Schema::dropIfExists('skill_user');
    }
};

Define the many-to-many relationship between User and Skill, and include the level pivot field so Laravel recognizes it.

User Model (app/Models/User.php) #

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
    use HasFactory, Notifiable;

    // ... Keep default Laravel code here

    // Define many-to-many relationship with Skill
    public function skills()
    {
        return $this->belongsToMany(Skill::class)
            ->withPivot('level') // Include the level field from the pivot table
            ->withTimestamps(); // Add if you included timestamps in the pivot table
    }
}

Skill Model (app/Models/Skill.php) #

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Skill extends Model
{
    use HasFactory;

    protected $fillable = ['name']; // Allow mass assignment of the skill name

    // Define inverse many-to-many relationship with User
    public function users()
    {
        return $this->belongsToMany(User::class)
            ->withPivot('level')
            ->withTimestamps();
    }
}

That SQLSTATE[22007] Invalid datetime format error usually happens for one of these reasons:

1. You accidentally set level to a datetime type in your migration
2. user_id/skill_id field types don't match the parent tables (e.g., using integer instead of unsignedBigInteger)
3. You're passing non-integer data to an integer field (like a string for level)

Quick Fixes: #

• Roll back and re-run migrations to apply the correct pivot table structure:

  php artisan migrate:rollback
  php artisan migrate
  

• Ensure you cast level to an integer in your controller before saving (see below)
• Validate all incoming form data to avoid invalid values

Here's a complete controller to handle the multi-skill form submission:

<?php

namespace App\Http\Controllers;

use App\Models\Skill;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;

class SkillController extends Controller
{
    // Show the skill form
    public function create()
    {
        $skills = Skill::all(); // Fetch all skills for the dropdown
        return view('skills.create', compact('skills'));
    }

    // Save skills to the database
    public function store(Request $request)
    {
        // Validate form data
        $request->validate([
            'skills' => 'required|array',
            'skills.*.skill_id' => 'required|exists:skills,id',
            'skills.*.level' => 'required|integer|min:1|max:5', // Adjust min/max to match your level scale
        ]);

        $user = Auth::user();

        // Format data for the pivot table
        $skillData = [];
        foreach ($request->skills as $skill) {
            $skillData[$skill['skill_id']] = ['level' => (int)$skill['level']]; // Cast level to integer
        }

        // Sync the skills (replaces existing user skills with new ones)
        $user->skills()->sync($skillData);

        return redirect()->back()->with('success', 'Your skills have been saved!');
    }
}

A dynamic form that lets users add/remove multiple skill entries:

@extends('layouts.app')

@section('content')
<div class="container mt-4">
    <h2>Add Your Skills</h2>

    @if(session('success'))
        <div class="alert alert-success">
            {{ session('success') }}
        </div>
    @endif

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

        <div id="skill-container">
            <!-- Initial skill row -->
            <div class="skill-row mb-3">
                <div class="row">
                    <div class="col-md-5">
                        <label class="form-label">Skill</label>
                        <select name="skills[0][skill_id]" class="form-control">
                            @foreach($skills as $skill)
                                <option value="{{ $skill->id }}">{{ $skill->name }}</option>
                            @endforeach
                        </select>
                    </div>
                    <div class="col-md-4">
                        <label class="form-label">Level (1-5)</label>
                        <input type="number" name="skills[0][level]" min="1" max="5" class="form-control" value="1">
                    </div>
                    <div class="col-md-3 mt-4">
                        <button type="button" class="btn btn-sm btn-danger remove-skill">Remove</button>
                    </div>
                </div>
            </div>
        </div>

        <button type="button" class="btn btn-sm btn-primary mb-3" id="add-skill">Add Another Skill</button>
        <button type="submit" class="btn btn-success">Save Skills</button>
    </form>
</div>

<script>
    let skillIndex = 1;

    // Add new skill row
    document.getElementById('add-skill').addEventListener('click', () => {
        const container = document.getElementById('skill-container');
        const newRow = document.createElement('div');
        newRow.className = 'skill-row mb-3';
        newRow.innerHTML = `
            <div class="row">
                <div class="col-md-5">
                    <label class="form-label">Skill</label>
                    <select name="skills[${skillIndex}][skill_id]" class="form-control">
                        @foreach($skills as $skill)
                            <option value="{{ $skill->id }}">{{ $skill->name }}</option>
                        @endforeach
                    </select>
                </div>
                <div class="col-md-4">
                    <label class="form-label">Level (1-5)</label>
                    <input type="number" name="skills[${skillIndex}][level]" min="1" max="5" class="form-control" value="1">
                </div>
                <div class="col-md-3 mt-4">
                    <button type="button" class="btn btn-sm btn-danger remove-skill">Remove</button>
                </div>
            </div>
        `;
        container.appendChild(newRow);
        skillIndex++;
        bindRemoveButtons();
    });

    // Bind remove button functionality
    function bindRemoveButtons() {
        document.querySelectorAll('.remove-skill').forEach(btn => {
            btn.addEventListener('click', () => btn.closest('.skill-row').remove());
        });
    }

    // Initialize remove buttons
    bindRemoveButtons();
</script>
@endsection

Add these routes to routes/web.php (protected by auth middleware):

use App\Http\Controllers\SkillController;

Route::middleware(['auth'])->group(function () {
    Route::get('/skills/add', [SkillController::class, 'create'])->name('skills.create');
    Route::post('/skills', [SkillController::class, 'store'])->name('skills.store');
});

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