1

I've been successful in managing several authors on a WordPress blog thanks to this question on StackOverflow. What I've realized is that I would like to have a simpler control. On the previous question I wanted to manage authors on different time intervals, but what I'd want now is just their monthly production.

I guess there's an "easy" SQL query to get a list of authors and posts per month for each of them, 

Author  No. of posts 
======  ============

Paul         23
Ringo        14
John         11
George       31

Instead of going through WP admin panels and filtering by month and author, this would be fast and clear, maybe using the information shown on the (thanks, ypercube) WordPress Database Description. One of the problems I see is how to ask only for certain month and year. In fact that would be the important variable in the query...

Is this difficult?

Community
  • 1
  • 1
javipas
  • 281
  • 1
  • 6
  • 16

2 Answers2

5

How about this:

SELECT COUNT( p.id ) post_count, user_nicename, DATE_FORMAT( post_date,  '%Y-%m' ) post_month
FROM  `wp_posts` p,  `wp_users` u
WHERE post_status =  'publish'
AND post_type = 'post'
AND u.ID = post_author
GROUP BY post_author, post_month

EDIT: So combining it all with the month and year split out and grouped along with putting the date range in the WHERE clause:

SELECT COUNT( p.id ) post_count, user_nicename, DATE_FORMAT( post_date,  '%Y' ) post_year, DATE_FORMAT( post_date,  '%m' ) post_month
FROM  `wp_posts` p,  `wp_users` u
WHERE post_status =  'publish'
AND post_type =  'post'
AND u.ID = post_author
AND p.post_date >=  '2011-11-01'
AND p.post_date <  '2011-12-01' + INTERVAL 1 MONTH 
GROUP BY post_author, post_year, post_month
ORDER BY post_year, post_month
davidethell
  • 11,708
  • 6
  • 43
  • 63
  • That query works almost perfectly: I can select the year and month with the Y and m variables, but the result is not the number of posts, but (and I'm guessing here) a SUM of the post's id by each author. – javipas Jan 02 '12 at 14:35
  • It is definitely not a sum, but WP creates multiple posts records per post to track different revisions. When I have time I'll revise it again to take that into account. – davidethell Jan 02 '12 at 14:39
  • thanks davidethell... I'm affraid I don't know what to change to test. My MySQL knowledge is really limited. – javipas Jan 02 '12 at 14:47
  • try it now. I added the check for post_type. Does that look right? – davidethell Jan 02 '12 at 14:57
  • Strange too. If I don't select year and month ('2011-12' in my example) I get the table with all the users and all their posts shown monthly (that's not really bad), and it shows them fine. But when I use the Y-m with current numbers, the post_count again shows what I think is the sum of all posts by each author since they started writing, not only that month. – javipas Jan 02 '12 at 15:04
  • Your group by needs to change if you add more SELECT columns. What is your full query? You can add it to your OP as an edit if you like. – davidethell Jan 02 '12 at 15:09
  • @javipas: That's what this query does. It doesn't limit into any month, it shows count of all posts. – ypercubeᵀᴹ Jan 02 '12 at 15:17
  • davidethell, the query is that simple one, but I wanted to show just one month. Your solution, as I've told to ypercube, is good as well, so I don't know if I can mark them both as valid. Thanks!! – javipas Jan 02 '12 at 16:05
  • @javipas: If you want to limit one month (or any other period), you'll have to add some condition in the `WHERE` clause. – ypercubeᵀᴹ Jan 02 '12 at 16:15
  • @javipas I have edited the post to split out the year and month and include a date range. Just change the interval on the month to get more months in the query. – davidethell Jan 02 '12 at 16:41
  • Thank you so much davidethell, your query works fine, and in fact the other solution is also useful. I'll mark your solution as the right one, though both are good, but you've got fewer points and I'm sure ypercube won't mind. Thank you both!! – javipas Jan 02 '12 at 18:05
1

For just one month (say December 2011):

SELECT 
    u.user_nicename AS Author
  , COUNT(*) AS Number_Of_Posts
FROM 
      wp_posts AS p
  JOIN
      wp_users AS u
    ON u.ID = p.post_author
WHERE p.post_type = 'post'
  AND p.post_status =  'publish'
  AND p.post_date >= '2011-12-01'
  AND p.post_date <  '2011-12-01' + INTERVAL 1 MONTH
GROUP BY
    u.ID

The index of (post_type, post_status, post_date) can be used by the query to select the rows that will be grouped.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • That gives the following error: `ERROR 1054 (42S22): Unknown column 'u.user_name' in 'field list'` – javipas Jan 02 '12 at 14:33
  • Is it `user_nicename` instead? – ypercubeᵀᴹ Jan 02 '12 at 14:47
  • Instead of linking to previous questions (that do not have sufficient info), you could link to [Wordpress DB schema](http://codex.wordpress.org/Database_Description) description page. – ypercubeᵀᴹ Jan 02 '12 at 14:56
  • strange. The query does something, but not what I expected. Only shows one author (don't know what this one specifically, he is a normal author, not the admin for example), and the number of posts shown is the total number adding all the authors. – javipas Jan 02 '12 at 14:58
  • Did you include the `GROUP BY u.ID` ? – ypercubeᵀᴹ Jan 02 '12 at 15:00
  • ypercube, that did it. I don't understand how I missed that :o Thank you so much! In fact, both your answer and the one by davidethell are useful, can I accept both answers as solutions, or I can only set one as the "one and only right"? – javipas Jan 02 '12 at 16:04
  • Last edit was not very good, the `GROUP BY u.id` was not shown as part of the code, probably why you missed it. You can select only one answer as most useful. You can upvote (or downvote) as many as you want. – ypercubeᵀᴹ Jan 02 '12 at 16:08
  • As I've told davidehell, both solutions are OK, so I will update both, but I'll accept his to increase his ranking, hope you don't feel bad, you've got a fantastic ranking!! Greetings and thanks for the quick answer to both of you. Oh, yes, and a happy new year :D – javipas Jan 02 '12 at 18:06