MySQL date/author comparison

Question

There was a previous question on StackOverflow about this subject (can insert the link, I've got no privileges for the moment) entitled "MySQL date comparison filter", and this goes as an extension of that one.

I've got some authors on my WordPress blog and I would like to get their productivity through MySQL. The next query works pretty well under MySQL to get an author's post during certain time range only one day:

SELECT      SQL_CALC_FOUND_ROWS wp_posts.* 
FROM        wp_posts 
  JOIN      wp_postmeta 
  ON        (wp_posts.ID = wp_postmeta.post_id) 
WHERE       wp_posts.post_type = 'post' 
  AND       post_author = '50'
  AND       post_date
    BETWEEN STR_TO_DATE('2011-10-27 14:19:17','%Y-%m-%d %H:%i:%s')
      AND   STR_TO_DATE('2011-10-27 14:51:17','%Y-%m-%d %H:%i:%s')
GROUP BY    wp_posts.ID 
ORDER BY    wp_posts.post_date DESC 
LIMIT       0, 100

But it gives me just the posts of that day during that hour range. I'd like to get a table with with everyday data filled up for each day and each author. On each day and each author, there should be the number of posts published by that author on that day and on that hour range.

The output should be something like this:

October Auth1 Auth2  Auth3
1   0   0   0
2   0   0   0
3   0   1   0
4   0   2   0
5   1   0   0
6   0   2   0
7   0   0   0
8   3   0   0
9   0   0   0
10  5   1   0
11  1   0   0
...
31  2   1   1

So the date should be a variable, but I'd like to include all authors, so I'd remove the post_author AND line.

I'm no expert at MySQL but I wonder if this could be done more or less easily and export the query results (or, more exactly, some fields of the query results) as a table, like the one shown.

Answer 1

I'm not going to rewrite your entire query, but here's how you'd do the data grouping:

SELECT ...
FROM ...
WHERE YEAR(post_date) = 2011 AND MONTH(post_date) = 10
GROUP BY DAY(post_date), HOUR(post_date)

Creating multiple columns for each author is not a good use of a query. That sort of transformation is better done in your wordpress code.

Note that this query will work on exact clock periods, 1am, 2am, 3am, etc... If you need arbitrary times (1:05am, 2:05am, 3:05am, etc...), this won't work and you'll need a more complicated grouping.

Answer 2

I reckon you should create a date reference table, populate that table and then LEFT OUTER JOIN from that table in your query. The problem of 'How Do I Display Missing Dates?' is quite a common SO question but I'll go for it anyway.

Prelim Step

At the mysql prompt run:

use WordPress;

Step 1 - Create Date Reference Table

create table all_date 
(id int unsigned not null primary key auto_increment, 
a_date date not null,
last_modified timestamp not null default current_timestamp on update current_timestamp,
unique key `all_date_uidx1` (a_date));

Step 2 - Populate Date Reference Table

The idea of this table is to have one row for every date. Now you could achieve this by running insert statements ad nauseum but why not write a routine to populate it for you (you could event create a MySQL scheduled event to ensure that you always have a complete set of dates in the table. Here's a suggestion for that routine:

DELIMITER //


CREATE PROCEDURE populate_all_dates(IN from_date DATE, IN days_into_future INT)

BEGIN

 DECLARE v_date DATE;
 DECLARE ix int;


 SET ix := 0;
 SET v_date := from_date;


 WHILE v_date <= (from_date + interval days_into_future day) DO

  insert into all_date (a_date) values (v_date) 
  on duplicate key update last_modified = now();

  set ix := ix +1;

  set v_date := from_date + interval ix day;

 END WHILE;

END//

DELIMITER ;

You can now run:

call populate_all_dates('2011-10-01',30);

To populate all the dates for October (or just crank up the days_into_the_future parameter to whatever you want).

Now that you have a date reference table with all dates that you're interested in populated you can go ahead and run your query for October:

select day(a.a_date) as 'October',
IFNULL(t.a1,0) as 'Auth1',
IFNULL(t.a2,0) as 'Auth2',
IFNULL(t.a50,0) as 'Auth50'
from all_date a
LEFT OUTER JOIN
(
SELECT date(wp.post_date) as post_date,
sum(case when wp.post_author = '1' then 1 else 0 end) as a1,
sum(case when wp.post_author = '2' then 1 else 0 end) as a2,
sum(case when wp.post_author = '50' then 1 else 0 end) as a50,
count(*) as 'All Auths'
FROM wp_posts wp
WHERE  wp.post_type = 'post'
AND wp.post_date  between '2011-10-01' and '2011-10-31 23:59:59'
GROUP BY date(wp.post_date)
) t
ON a.a_date = t.post_date
where a.a_date between '2011-10-01' and '2011-10-31'
group by day(a.a_date);

Answer 3

The query below will group number of posts by author.

SELECT      DAY(post_date) as d, MONTH(post_date) as m, YEAR(post_date) as y, post_author, COUNT(id) as c
FROM        wp_posts 
  JOIN      wp_postmeta 
  ON        (wp_posts.ID = wp_postmeta.post_id) 
 WHERE       wp_posts.post_type = 'post' 
  AND       post_date
    BETWEEN STR_TO_DATE('2011-10-27 14:19:17','%Y-%m-%d %H:%i:%s')
      AND   STR_TO_DATE('2011-10-27 14:51:17','%Y-%m-%d %H:%i:%s')
GROUP BY    d, m, y, post_author
ORDER BY    y, m, d, post_author 
LIMIT       0, 100

This will output a table like:

d   m   y    post_author    c
------------------------------------
2   10  2011  50    23
2   10  2011  51    12
2   10  2011  52    6

meaning for line 1 for example: on 2 October 2011 author with id 50 has 23 posts. You would first fetch the authors in an array to form the header of your table. Then iterating this result with PHP you could generate a table like you want it accordingly placing 's and 's.