How to select by third table Laravel?

Question

I have Posts table.

Each Post have categories:

Post_categories
 _________________
id post_id | category_id

Also there is table:

User_categories
__________________
category_id | user_id

So, I need to select all rows from Post table, where User_categories.user_id =9 and User_categories.category_id = Post_categories.

So, others words, I need to show articles on that categories user subscribed.

Answer 1

Giving this another try with the Eloquent way.

In the Post model class, define the many-to-many relationship with Category model. (The pivot table is Post_categories)

class Post {
public function categories()
{
return $this->belongsToMany('App\Category', 'Post_categories', 'post_id', 'category_id');
}
}

In the User model class, define the many-to-many relationship with Category model. (The pivot table is User_categories) Also in this class, define the posts() function as a query on the relationship existence.

class User {
public function categories()
{
return $this->belongsToMany('App\Category', 'User_categories', 'user_id', 'category_id');
}

public function posts()
{
return App\Post::whereHas('categories', function ($query) use($this) {
$query->whereIn('posts.cat_id', array_column($this->categories->toArray(), 'categories.id'));
});
}

}

To get the posts that a user subscribes to

App\User::find(9)->posts;

Hope this helps.