Wordpress-按天获取帖子数和评论 [英] Wordpress - Get number of posts AND comments by day

问题描述

我需要权衡Wordpress博客中有多少活动.假设某天有3条帖子和10条评论，某条帖子的得分为10分，而评论的得分为1分，则表示一天总共获得40分.但是，可能有几天可能没有帖子活动或评论活动.

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.

我的第一个想法是从帖子到评论表的简单LEFT JOIN.但是，这将排除没有帖子的日子.我不是MySQL专家，但我一直在研究，看来解决此问题的最佳方法是使用FULL OUTER JOIN(

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!

然后，实际上有

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

但是RIGHT出了点问题.

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

因此，UNION解决方法没有用.

Hence, the UNION workaround is useless.

我做错了什么?有没有更简单的方法可以做到这一点?

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

谢谢.

注意:您必须在不同的日期添加一些帖子和评论.

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

推荐答案

我认为这不是您可以编写的最佳查询，但似乎可行

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

这篇关于Wordpress-按天获取帖子数和评论的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！