php - SQLSTATE[23000] 完整性约束冲突 : 1452 in a many-to-many relationship in laravel 5. 3

Question

我有一个 BlogPost Controller ，它有一个用户表单，可以通过 create() 方法从用户那里获取输入。然后该表单重定向到同一 Controller 上的 store 方法。我面临的问题是多对多关系。 BlogPost 模型与 BlogTag 模型具有多对多关系。我正在通过复选框获取用户的输入。用户可以选择与各个标签关联的多个单选按钮(表单代码已在下面发布)。一切似乎都工作正常，但是当我尝试附加所有标签 ID 到我的 store() 方法中的 post 实例时。我收到如下所示的错误。我已经浏览了互联网上所有可用的解决方案，但似乎没有一个适合我的情况。商店方法也贴在下面。

QueryException in Connection.php line 761: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (erp_system_solution.posts_tags, CONSTRAINT posts_tags_ref_tag_id_foreign FOREIGN KEY (ref_tag_id) REFERENCES blog_tags (tag_id) ON DELETE CASCADE ON UPDATE CASCADE) (SQL: insert into posts_tags (ref_post_id, ref_tag_id) values (2, 34), (7, 34), (8, 34), (15, 34))

发布博客帖子的表单

{{Form::open(array(
                        'action' => 'Blog\BlogPostController@store',
                        'class' => 'form-horizontal',
                        'method' => 'POST',
                    ))}}
                        {{ Form::hidden('user_id', Auth::user()->user_id) }}
                                <div class="form-group">
                                        {{ Form::label("post_title", "Post Title", array("class" => "col-sm-2 control-label")) }}
                                        {{ Form::text ("post_title", "", array("class" => "col-sm-6")) }}
                                </div>
                                <div class="form-group">
                                    {{ Form::label("post_body", "Post Body", array("class" => "col-sm-2 control-label")) }}
                                    {{ Form::textarea("post_body", "", array("class" => "col-sm-6 col-sm-offset-2 top-info-panels", "rows" => "16", "columns" => "2")) }}
                                </div>
                                <div class="form-group">
                                        {{ Form::label("published", "Publish Blog Post", array("class" => "col-sm-2 control-label")) }}
                                        {{ Form::radio("published", "Yes", true) }} Yes
                                        {{ Form::radio("published", "No") }} No
                                </div>
                                <div class="form-group">
                                    {{ Form::label("category_id", "Choose a Category", array("class" => "col-sm-2 control-label")) }}
                                    {{ Form::select("category_id", $categories, '-----Choose Any One-----') }}
                                </div>
                                <div class="form-group">
                                    {{ Form::label("tag_id", "Choose Appropriate Tags", array("class" => "col-sm-2 control-label")) }}
                                    <div class="col-sm-offset-2">
                                    @for($i=1; $i<count($tags)+1; $i++)
                                        {{ Form::checkbox("tag_id[]", $i), array("class" => "col-sm-offset-2 col-sm-10") }} {{ $tags[$i] }}
                                    @endfor
                                    </div>
                                </div>
                                <div class="form-group">
                                    <div class="col-sm-offset-2 col-sm-10">
                                        {{Form::submit('Create Post', array("class" => "btn btn-warning"))}}
                                    </div>
                                </div>
                    {{Form::close()}}

BlogPostModel

<?php

namespace App;

use App\PostsTagsPivot as Pivot;
use App\BlogCategory;
use App\BlogTag;
use App\Comment;
use App\RegisteredUser;
use Illuminate\Database\Eloquent\Model;

class BlogPost extends Model
{
    public $timestamps = true;
    protected $table = 'blog_posts';
    protected $primaryKey = 'post_id';
    protected $fillable = ['post_title', 'post_body', 'published', 'registered'];

    public function post_category() {
        return $this->belongsTo(BlogCategory::class, 'category_id');
    }
    public function posts_with_tags() {
        return $this->belongsToMany(BlogTag::class, 'posts_tags', 'ref_tag_id', 'ref_post_id');
    }
    public function comments() { 
        return $this->hasMany(Comment::class, 'comment_id');
    }
    public function posts_author() {
        return $this->belongsTo(RegisteredUser::class, 'user_id');
    }
}

BlogPostController

public function store(Request $r) {
        $u_id = $this->user::find((int)$r->input('user_id'));
        $cat_id = $this->category::find((int)$r->input('category_id'));
        $tag_id = $r->input('tag_id');
         $role = $this->user::find((int)$r->input('user_id'))->has_role($u_id);
         $registered = ($role == 'super_admin') ? true : false;
         $published = ($r->input('published') == 'Yes') ? true : false;
         $create_post = $this->blog_posts::create(['post_title' => $r->input('post_title'), 'post_body' => $r->input('post_body'), 'published' => $published, 'registered' => $registered]);
         $create_post->save();
         $create_post->posts_with_tags()->attach($tag_id);
         $create_post->save();
         $create_post->posts_author()->associate($u_id);
         $create_post->save();
        $create_post->post_category()->associate($cat_id);

           if($create_post->save()) return redirect()->route('blog_post.create')->with('post_saved', 'Your post has been saved');
           else return redirect()->route('blog_post.create')->with('post_not_saved', 'Something went wrong');
    }

博客标签模型

<?php

namespace App;

use App\BlogPost;
use Illuminate\Database\Eloquent\Model;

class BlogTag extends Model
{
    public $timestamps = true;
    protected $table = 'blog_tags';
    protected $primaryKey = 'tag_id';
    protected $fillable = ['tag_name', 'post_id', 'tag_id'];

    public function tags_on_posts() {
        return $this->belongsToMany(BlogPost::class, 'posts_tags', 'ref_tag_id', 'ref_post_id');
    }
}

迁移将 BlogPost 与 BlogTag 连接起来的数据透视表

public function up()
{
    Schema::create('posts_tags', function ($table) {
        $table->integer('ref_tag_id')->unsigned()->index();
        $table->foreign('ref_tag_id')->references('tag_id')->on('blog_tags')->onDelete('cascade')->onUpdate('cascade');
        $table->integer('ref_post_id')->unsigned()->index();
        $table->foreign('ref_post_id')->references('post_id')->on('blog_posts')->onDelete('cascade')->onUpdate('cascade');
    });
}

Answer 1

嗯，我明白了。解决方案是更改 posts_with_tags() 操作/方法中的顺序

它是:

  public function posts_with_tags() {
        return $this->belongsToMany(BlogTag::class, 'posts_tags', 'ref_tag_id', 'ref_post_id');
    }

应该是:

  public function posts_with_tags() {
        return $this->belongsToMany(BlogTag::class, 'posts_tags', 'ref_post_id', 'ref_tag_id');
    }