Count post with specific categories and tags laravel

Question

I have 3 model Post, Tag and Category. Post model and Tag model are in many to many relationship with pivot table and Post model and Category model are in one to many relation.

For example let's assume I have following tags and categories:

tag name = php,

category name = tutorial, project, testing

no. of post having above attribute = 3 [ all post have different category, same tag]

I'm trying to count posts and show category name having above category and tag php in view named archive-php.

This is what i have done so far:

My method in controller

 public function countTag()
{
    $posts = Post::whereHas('tag', function($r) {
            $r->where('name','=', "php");
        })->get();
    return view('archive-php')
           ->withPosts($posts)
}

My code in view:

@foreach ($posts as $post)
    <li><a href="#">{{ucfirst($post->category->name)}}</a>
         {{$post->count()}}
    </li>
@endforeach

The above code sucessfully shows the related category name (3 category name) dd($posts); dumps 3 array but counts all post in the db. What piece of puzzle i'm missing here or it is impossible ?

Answer 1

To achieve that you should get all categories. And then get/count posts that have php tag

1 - method

$categories = Category::all();

foreach($categories as $category) {
  $category->phpPosts = Post::where('category_id', '=', $category->id)->whereHas('tag', function($r) {
            $r->where('name','=', "php");
        })->get();
}

Then use it like this:

@foreach ($categories as $category)
    <li><a href="#">{{ucfirst($category->name)}}</a>
         {{ $category->phpPosts->count() }}
    </li>
@endforeach

It can be done by easy way something like:

2 - method

$categories = App\Category::with(['posts' => function ($query) {
    $query->whereHas('tag', function($r) {
            $r->where('name','=', "php");
        });
}])->get();

But I'm not sure whether it is correct. Try the method 1. If I got you correctly.