How to get all rows from TableA based on specific id from TableB in pivot table?-CodePudding

I have 3 models: User, Post and Comment. The relationships are set up like so:

User Model:

public function posts()
{
    return $this->hasMany(Post::class);
}

public function comments()
{
    return $this->hasMany(Comment::class);
}

Post Model:

public function user()
{
    return $this->belongsTo(User::class);
}

public function comments()
{
    return $this->belongsToMany(Comment::class);
}

Comment Model:

public function user()
{
    return $this->belongsTo(User::class);
}

public function posts()
{
    return $this->belongsToMany(Post::class);
}

And there is the comment_post pivot table (Which does not have a pivot Model - Do I need it in my case?)

Given a post_id, how can I get all the comments that belongs to it in the pivot table, for example:

comment_id | post_id
-----------|--------
     1     |   1
     2     |   2
     3     |   3
     4     |   4
     5     |   1

If I want to get all the comments that belong to post with post_id 1, which would result in comments 1 and 5 - how can I achieve that? Nothing I tried work.

I've tried adding withPivot to the relationship, and then wherePivot to the query, tried defining the post_id and comment_id explicitly in the relationship, for example:

$comments = Comment::with(['posts' => function ($query) use ($comment_id) {
    $query->wherePivot('post_id', $post_id);
}])->get();

It just doesn't work.

I either get all the comments, or I get Unknown column 'pivot' in 'where clause'

CodePudding user response：

Don't trouble yourself with not needed complexity. Simply use your defined relationships to do so:

Post::find(1)->comments;

And you don't need a model for your pivot table in this case.

You can't access pivot table with wherePivot in the query. I don't have any specific idea about the way you're trying to do the job. But mainly, you have to specify your desired data. If you want the comments of 'a' post, you have to find the post and get the comments related to that post. Your way is getting all the comments and their posts and check if the id of the post is the same as your desired post_id. This way is too complicated and have redundunt data and can be logically incorrect.