Many-to-many in same model-CodePudding

I need to use a many-to-many relationship to one model. I have an Article model, and I want to make a function so that other articles, typically recommended, can be attached to one article.

This is the function, it should work correctly.


$article = Article::where('id', $request->article_id)->first();

$articles_ids = json_decode($request->articles_ids);

$article->articles()->attach($articles_ids);

I have a question about how to create a table of relations in the database and in the model correctly, I did it like this, but even the migration does not work for me

Model

public function articles()
    {
        return $this->belongsToMany('App\Models\Article');
    }

    public function article_recommended()
    {
        return $this->belongsToMany('App\Models\Article');
    }

Schema::create('article_article_recommended', function (Blueprint $table) {
            $table->unsignedBigInteger('article_recommended_id')->nullable();
            $table->foreign('article_recommended_id')
                ->references('id')->on('article_recommended')->onDelete('set null');
            $table->unsignedBigInteger('article_id')->nullable();
            $table->foreign('article_id')
                ->references('id')->on('articles')->onDelete('cascade');
        });

error in migration

SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table 
'article_recommended' (SQL: alter table `article_article_recommended` add constraint 
`article_article_recommended_article_recommended_id_foreign` foreign key 
(`article_recommended_id`) references `article_recommended` (`id`) on delete set null)

CodePudding user response：

What are you trying to do should be fairly simple , it's like a following system where each user is related to many users ( same model ) but also same table !

you are here trying to create another table which i consider unnecessary , you only need two tables

articles & recommendations

and the recommendation tabel will act as a pivot table for articles with its slef thus creating a many to many relationship with the same table .

Article.php

public function recommendations() {
        return $this->belongsToMany(Article::class , 'recommendations' , 'article_id' , 'recommended_article_id');
    }

create_recommendations_table.php

 Schema::create('recommendations', function (Blueprint $table) {
            $table->primary(['article_id','recommended_article_id']);
            $table->foreignId('article_id');
            $table->foreignId('recommended_article_id');
            $table->timestamps();


            $table->foreign('article_id')->references('id')->on('article')->onDelete('cascade');
            $table->foreign('recommended_article_id')->references('id')->on('articles')->onDelete('cascade');
        });

usage

$article->recommendations()->attach($articles_ids);

CodePudding user response：

be sure that the ref-column has exact the same type as the source column (signed, unsigned etc.)