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?

CodePudding user response：

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()

CodePudding user response：

I assuming that you are using wrong table names in the query or may be have put the wrong table name in the question.

Let say your post table name is posts and categories table is categories, then your query should be:

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

Please note - Make sure to replace the table names with your tables.

If you are using this for one time load, then you can use the model relationship as well to get the complete information without creating a custom query.

https://laravel.com/docs/8.x/eloquent-relationships

You can have category to post relation. Say you have Models Category and Post, then you can have below relation in Category model.

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

And then in your code you can use $category->posts to get collection of posts for the category and can use $category->posts->count() to get the count of posts for the category.