Laravel defining a many-to-many relationship with the same table

Question

So I have a posts table with a corresponding Post model. I want to have related posts for every post. Since a post can have many other related posts, it is a many-to-many relationship between the posts table and the posts table (same table).

So I created a related_posts pivot table with its corresponding model RelatedPost. I want to define this relationship in the two models. Like so:

Post model:

public function related()
{
 return $this->belongsToMany(RelatedPost::class, 'related_posts', 'related_id', 'post_id');
}

RelatedPost model:

public function posts()
{
  return $this->belongsToMany(Post::class, 'related_posts', 'post_id', 'related_id');
}

Now in my post controller after selecting a particular post, I want to get all its related posts. So I do this:

$post->related()->get();

But when I do this I get the following error message:

"SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'related_posts' (SQL: select related_posts.*, related_posts.related_id as pivot_related_id, related_posts.post_id as pivot_post_id from related_posts inner join related_posts on related_posts.id = related_posts.post_id where related_posts.related_id = 1) "

This is my migration for the pivot table:

  Schema::create('related_posts', function (Blueprint $table) {
      $table->increments('id');
      $table->unsignedInteger('post_id');
      $table->unsignedInteger('related_id');
      $table->timestamps();

      $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
      $table->foreign('related_id')->references('id')->('posts')->onDelete('cascade');
  });

I have searched all over everywhere and though the solutions I've found really make sense I haven't been able to get any of them to work.

Any help will be very much appreciated!

Why are you creating `RelatedPost` model? You don't need it for your intermediate table. Your `related()` relation makes no sense - it should be pointing to `Post` model via `related_posts` table. — d3jn, Jul 27 '18 at 06:27
@d3jn Thanks a lot mate. It's working now. You have saved me hours! — Awa Melvine, Jul 27 '18 at 06:34

score 22 · Accepted Answer · answered Jul 27 '18 at 07:09

Thanks to @d3jn's comment on my question I was able to solve my problem. So I am posting the solution here just in case someone else might need it.

I am relating the Post model to itself not to the pivot model RelatedPost. So I don't need a RelatedPost model. I only need a pivot table (related_post), and the relation's ids namely related_id and post_id.

So with my migration unchanged, I only need to do away with the RelatedPost model and change my related() method in the Post model to look like this:

public function related()
{
  return $this->belongsToMany(Post::class, 'related_posts', 'post_id', 'related_id');
}

And now everything works.

Laravel defining a many-to-many relationship with the same table

1 Answers1

Linked