join tables and see the count of categories in each category name in laravel 8

excuse me want to ask. I’m sorry in advance if my language is not neat

I have a category table with colom -id (pk) -name -and others

and i have a post table with colom -id (pk) -category_id(fk)

• and others

I want to count the number of posts from each category along with the category name in laravel 8. this is my jquery but still wrong

$categ = DB::table('posts')
 ->select('category_id', 'categories.id', DB::raw('count(posts.id) as total_product'))
 ->join('categories', 'posts.category_id', '=', 'categories.id')
 ->groupBy('posts.category_id')
 ->get();

anyone want to help me?

Answer

If You are willing to use Eloquent, the answer would be much simple.

Post would be a model for posts table

Category would be a model for categories table

there would be one-to-one or any other relationship as per requirements between models

Let’s say there is a one-to-many relationship between Category and Post model This can be achieved by adding a function in your Category model

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

then you can use something like…

Category::withCount('posts')->get()