Laravel9+Filament2书籍与分类多对多关系存储报错求助

错误信息 #

提交表单时触发SQL错误：

SQLSTATE[HY000]: General error: 1364 Field 'category_id' doesn't have a default value
INSERT INTO
  `books` (
    `image`,
    `title`,
    `author_id`,
    `publication_date`,
    `copies`,
    `updated_at`,
    `created_at`
  )
VALUES
  (
    Uqvz9qyKeqt11XmrlntLZuWWkgKYjT - metaNDAzNjI1ODg3XzE4MDU4MDc2MTY1MzUyNjdfOTIxNjYxODQ2MjE5MTc5ODE1OV9uLmpwZw = = -.jpg,
    AS,
    1,
    AS,
    AS,
    2023 -12 -13 16: 14: 00,
    2023 -12 -13 16: 14: 00
  ) 

相关代码 #

Filament表单代码 #

public static function form(Form $form): Form
{
    return $form
        ->schema([
            Card::make()
                ->schema([
                    FileUpload::make('image')->image(),
                    TextInput::make('title')
                        ->required()
                        ->maxLength(255),
                    Select::make('author_id')   
                        ->relationship('author', 'full_name')
                        ->required(),
                    Select::make('category_id')
                        ->multiple()    
                        ->relationship('categories', 'name')
                        ->preload()
                        ->required(),
                    TextInput::make('publication_date')
                        ->required()
                        ->maxLength(255),
                    TextInput::make('copies')
                        ->required()
                        ->maxLength(255)
                ])
        ]);
}

模型代码 #

class Book extends Model
{
    use HasFactory;

    protected $fillable = ['author_id','image','category_id','title','language','publication_date','copies'];

    protected $casts = [
        'category_id' => 'array',
    ];

    public function author(){
        return $this->belongsTo(Author::class);
    }

    public function categories(){
        return $this->belongsToMany(Category::class);
    }
}

class Category extends Model
{
    use HasFactory;

    protected $fillable = ['name'];
        
    public function books(){
        return $this->hasMany(Book::class);
    }
}

迁移代码 #

// books表迁移
public function up()
{
    Schema::create('books', function (Blueprint $table) {
        $table->id();
        $table->foreignId('author_id')->constrained()->cascadeOnDelete();
        $table->foreignId('category_id')->constrained()->cascadeOnDelete();
        $table->string('image');
        $table->string('title');
        $table->string('publication_date');
        $table->string('copies');    
        $table->timestamps();
    });
}

// categories表迁移
public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->timestamps();
    });
}

问题分析与解决步骤 #

核心问题是混淆了多对多关联和数组存储分类两种实现逻辑，数据库结构、模型关联与表单字段不匹配导致SQL错误。以下分两种场景给出解决方案：

场景1：使用标准多对多关联（推荐，符合数据库设计规范） #

多对多关联需要中间表存储书籍与分类的关联关系，而非在books表中添加category_id字段。

1. 创建中间表迁移
   生成book_category中间表（命名规则：两个模型名按字母顺序排列）：

php artisan make:migration create_book_category_table

修改迁移文件：

public function up()
{
    Schema::create('book_category', function (Blueprint $table) {
        $table->id();
        $table->foreignId('book_id')->constrained()->cascadeOnDelete();
        $table->foreignId('category_id')->constrained()->cascadeOnDelete();
        $table->timestamps();
    });
}

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

执行迁移：

php artisan migrate

2. 移除books表的category_id字段

• 若未上线：回滚books表迁移并删除$table->foreignId('category_id')->constrained()->cascadeOnDelete();，重新执行迁移：

php artisan migrate:rollback --step=1
php artisan migrate

• 若已上线：创建新迁移移除字段：

php artisan make:migration remove_category_id_from_books_table

迁移内容：

public function up()
{
    Schema::table('books', function (Blueprint $table) {
        $table->dropForeign(['category_id']);
        $table->dropColumn('category_id');
    });
}

public function down()
{
    Schema::table('books', function (Blueprint $table) {
        $table->foreignId('category_id')->constrained()->cascadeOnDelete();
    });
}

执行迁移：

php artisan migrate

3. 修正模型关联

• Book模型：移除$fillable和$casts中的category_id配置：

class Book extends Model
{
    use HasFactory;

    protected $fillable = ['author_id','image','title','language','publication_date','copies'];

    public function author(){
        return $this->belongsTo(Author::class);
    }

    public function categories(){
        return $this->belongsToMany(Category::class);
    }
}

• Category模型：将hasMany改为belongsToMany：

class Category extends Model
{
    use HasFactory;

    protected $fillable = ['name'];
        
    public function books(){
        return $this->belongsToMany(Book::class);
    }
}

4. 调整Filament表单字段
   将category_id改为categories（对应模型关联方法名），Filament会自动处理多对多存储：

Select::make('categories')
    ->multiple()    
    ->relationship('categories', 'name')
    ->preload()
    ->required(),

5. Filament表格展示分类
   添加分类字段，用换行展示多个关联分类：

Tables\Columns\TextColumn::make('categories.name')
    ->label('分类')
    ->listWithLineBreaks(),

场景2：将分类ID以数组形式存储到books表（不推荐，不符合数据库范式） #

若坚持数组存储，需将books表的category_id改为JSON类型：

1. 修改books表字段类型
   创建新迁移：

php artisan make:migration alter_category_id_to_json_in_books_table

迁移内容：

public function up()
{
    Schema::table('books', function (Blueprint $table) {
        $table->dropForeign(['category_id']);
        $table->dropColumn('category_id');
        $table->json('category_ids')->nullable();
    });
}

public function down()
{
    Schema::table('books', function (Blueprint $table) {
        $table->dropColumn('category_ids');
        $table->foreignId('category_id')->constrained()->cascadeOnDelete();
    });
}

执行迁移：

php artisan migrate

2. 修正Book模型
   更新$fillable和$casts：

class Book extends Model
{
    use HasFactory;

    protected $fillable = ['author_id','image','category_ids','title','language','publication_date','copies'];

    protected $casts = [
        'category_ids' => 'array',
    ];

    public function author(){
        return $this->belongsTo(Author::class);
    }
}

3. 调整Filament表单字段
   将字段名改为category_ids：

Select::make('category_ids')
    ->multiple()    
    ->options(Category::pluck('name', 'id'))
    ->preload()
    ->required(),

4. Filament表格展示分类
   将数组ID转换为分类名称：

Tables\Columns\TextColumn::make('category_ids')
    ->label('分类')
    ->formatStateUsing(function ($state) {
        if (empty($state)) return '';
        $categories = Category::find($state);
        return $categories->pluck('name')->implode(', ');
    }),

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