WordPress – Get number of posts AND comments by day

4 Views

I need to weight how much activity has been in a WordPress blog. Say, some day there are 3 posts and 10 comments, the points awarded for a post is 10 and just 1 for a comment, then said day had 40 points in total. However, there might be some days with no post activity or with no comment activity.

My first idea was a simple LEFT JOIN from the posts to the comments table. However, this will exclude days without posts. I’m no MySQL guru, but I’ve been researching and it seems that the best way to solve this is a with FULL OUTER JOIN (explained by Jeff Atwood), but MySQL doesn’t suppor this!

Read More

Then, there actually is a workaround, but it’s not working for me. It seems that the RIGHT OUTER JOIN is not returning what I need.
Here’s the LEFT one, it works pretty good. 

SELECT
    DISTINCT DATE(post_date) AS day,
    COUNT(ID) AS post_total,
    COUNT(comment_ID) as comment_total,
    (COUNT(ID)*10 + COUNT(comment_ID)*1) AS total
FROM wp_posts
    LEFT OUTER JOIN wp_comments ON
        DATE(post_date) = DATE(comment_date)
GROUP BY day ORDER BY total DESC

But something’s wrong with the RIGHT one. 

SELECT
    DISTINCT DATE(post_date) AS day,
    COUNT(ID) AS post_total,
    COUNT(comment_ID) as comment_total,
    (COUNT(ID)*10 + COUNT(comment_ID)*1) AS total
FROM wp_posts
    RIGHT OUTER JOIN wp_comments ON
        DATE(post_date) = DATE(comment_date)
GROUP BY day ORDER BY total DESC

Hence, the UNION workaround is useless.

What am I doing wrong? Is there a simpler way to do this?

Thanks.

Note: You’ll have to add some posts and comments in different dates.

Post Views: 4

Related posts

Leave a Reply

1 comment

  1. I think this isn’t the best query you can write but seems to work

    CREATE VIEW commentsCount (date, counter) AS
SELECT
    DISTINCT DATE(comment_date) AS date,
    IFNULL(COUNT(comment_ID),0) AS total
FROM wp_comments
GROUP BY date ORDER BY total DESC

    CREATE VIEW postsCount (date, counter) AS
SELECT
    DISTINCT DATE(post_date) AS date,
    IFNULL(COUNT(ID),0) AS total
FROM wp_posts
GROUP BY date ORDER BY total DESC

    SELECT
    postsCount.date,
    IFNULL(postsCount.counter,0),
    IFNULL(commentsCount.counter,0),
    (IFNULL(postsCount.counter,0)*10 + IFNULL(commentsCount.counter, 0))
FROM commentsCount RIGHT JOIN postsCount 
    ON DATE(postsCount.date) = DATE(commentsCount.date)
GROUP BY postsCount.date
union
SELECT
    commentsCount.date,
    IFNULL(postsCount.counter,0),
    IFNULL(commentsCount.counter,0),
    (IFNULL(postsCount.counter,0)*10 + IFNULL(commentsCount.counter, 0))
FROM commentsCount LEFT JOIN postsCount 
    ON DATE(postsCount.date) = DATE(commentsCount.date)
GROUP BY commentsCount.date