mySQL statment count of post in each category and sub category

admin管理员组

文章数量:1122832

im trying to get a count of all post for each Categorty and sub category

it should show

main_cat     10
  -a1       5
  -a2       5 

i have tried this but my counts are all of and im getting categories that are not listed

SELECT t.name as `name`, t1.name as parentName, count(p.ID) as `TotalPosts`,
    SUM(if(p.post_date>'2015-12-01 00:00:00',1,0)) as 'LastMonth' 
    FROM wp_posts p
    LEFT JOIN wp_term_relationships rel ON rel.object_id = p.ID 
    LEFT JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id AND tax.taxonomy ='category' 

    LEFT JOIN wp_terms t ON t.term_id = tax.term_id 

    LEFT JOIN wp_term_taxonomy tax1 ON tax.parent = tax1.term_taxonomy_id 

    LEFT JOIN wp_terms t1 ON t1.term_id = tax1.term_id 

    WHERE p.post_status = 'publish' 
    GROUP BY t.name
    ORDER BY t1.name,LastMonth DESC, TotalPosts Desc

im trying to recreate the page->category section the same way it is listed

im trying to get a count of all post for each Categorty and sub category

it should show

main_cat     10
  -a1       5
  -a2       5 

i have tried this but my counts are all of and im getting categories that are not listed

SELECT t.name as `name`, t1.name as parentName, count(p.ID) as `TotalPosts`,
    SUM(if(p.post_date>'2015-12-01 00:00:00',1,0)) as 'LastMonth' 
    FROM wp_posts p
    LEFT JOIN wp_term_relationships rel ON rel.object_id = p.ID 
    LEFT JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id AND tax.taxonomy ='category' 

    LEFT JOIN wp_terms t ON t.term_id = tax.term_id 

    LEFT JOIN wp_term_taxonomy tax1 ON tax.parent = tax1.term_taxonomy_id 

    LEFT JOIN wp_terms t1 ON t1.term_id = tax1.term_id 

    WHERE p.post_status = 'publish' 
    GROUP BY t.name
    ORDER BY t1.name,LastMonth DESC, TotalPosts Desc

im trying to recreate the page->category section the same way it is listed

• mysql

Share Improve this question asked Jan 2, 2017 at 10:12 Reuben GomesReuben Gomes 11122 bronze badges 3

• And you need to do that from MySQL and not via the WordPress Query? Right? – prosti Commented Jan 2, 2017 at 10:25
• This looks close: wordpress.stackexchange.com/questions/160672/… – prosti Commented Jan 2, 2017 at 10:25
• yea in mysql @prosti – Reuben Gomes Commented Jan 2, 2017 at 10:35

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You may try this amateur code.

SELECT  COUNT(ID)
FROM wp_posts  
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
WHERE 1=1  
AND ( 
  wp_term_relationships.term_taxonomy_id IN (1)
) 
AND wp_posts.post_type = 'post' 
AND ((wp_posts.post_status = 'publish')) 
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

Note the term_taxonomy_id IN (1) is where your category ID is 1. You may remove the line for published posts, so it will get any post.

You just need to run this code for any category you have.

本文标签： mySQL statment count of post in each category and sub category