Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hi, following tables are posts and categories and there is a one to many relation which says each posts belongs to a single category.
Categories table
id title parent id
Posts table
id title category_id
Now I am using following query to gain total number of subcategories and total number of posts which belongs to that category and all it's children and grand children and ... categories:
select c1.id, (c2.id) c2count, count(p.id) as pcount from post_categories c1 inner join post_categories c2
on c1.id = c2.parent_id inner join posts p on p.category_id = c2.id where c1.id=1 group by c1.id;
But It's not woking find. it retuns something like below:
c1 c2count pcount
1 10 10
The point is that it always return the same value for pcount and c2count meaning they are equal the actual count of all posts! How can I change query to get my desirable effect? thank you in advance
Did you forget the count keyword here?
select c1.id, COUNT(c2.id) c2count, count(p.id) as pcount from post_categories c1 inner join post_categories c2
on c1.id = c2.parent_id inner join posts p on p.category_id = c2.id where c1.id=1
group by c1.id;
@Sinnbeck Oh no I just forgot to mention in in question! but still the same problem here
@danimohamadnejad Is this set up using eloquent? If so you can perhaps do something like this
$data = Category::withCount('subcategories as c2count')->withCount('subcategories.posts as pcount')->get();
Please or to participate in this conversation.