通过关系查询数据透视表

Question

I'm Using Laravel 5.1 and have these four database tables

posts containing posts from Facebook. The posts table has a foreign key page_id. It's a refernece to the

pages table, which contains all facebook pages.

category table contains data for the categories in my project, e.g. funny, videos, etc.

category_page is a pivot table which saves the category_id and the page_id

---

What I now want to do, is to get all posts, that have been posted by a page in a specific category.

So, let's assume I have three pages

1: Mesut Oezil
2: Borussia Dortmund
3: BMW

The first two pages are in the category sports and the last one in cars. Now I want to get every post that has been posted by the pages from the category sport.

I have already defined the relation ships

Class Page:

public function categories()
{
    return $this->belongsToMany(Category::class);
}

Class Category:

public function pages()
{
    return $this->belongsToMany(Page::class);
}

Now, I'm getting my posts

$topPosts = (new Post)->where('fb_created_time', '>', Carbon::today()->toDateTimeString())
    ->visible()
    ->orderBy('total_count', 'desc');

And now I want to additional filter only the posts from the pages in a specific category. I managed to get pages from a specific category with this code here

$pages = Page::with('categories')
    ->whereHas('categories', function ($query){
        return $query->where('category_id', 1);
    })->get();

But how would I chain this into the posts? Or is it even good to use whereHas? What's the best practice here?

Answer 1

If I'm understanding right, you want to get posts, what belongs to a specific category, through pages.

In this case your setup is fine, you can make queries through relationships:

$category_id = 2;
Post::whereHas('page.categories', function($q) use($category_id) {
    $q->where('categories.id', $category_id);
})->get();

In your Post class:

public function page()
{
    return $this->belongsTo(Page::class);
}

It will make one nested query.