Wordpress popular post list

popular_post_list.sql

# 全投稿view数順

SELECT
p.post_title,
pm.meta_value,
p.guid
FROM wp_posts p
INNER JOIN wp_postmeta pm
ON p.id = pm.post_id
AND pm.meta_key = "post_views_count"
WHERE
p.post_status = "publish" AND p.post_type = "post"
ORDER BY cast(pm.meta_value AS UNSIGNED) DESC;

# 先月投稿した記事のview数順

SELECT 
    p.post_title,
    pm.meta_value,
    CONCAT('https://blog.yabaiwebyasan.com/',
            p.post_name,
            '/'),
    p.post_date
FROM
    wp_posts p
        INNER JOIN
    wp_postmeta pm ON p.id = pm.post_id
        AND pm.meta_key = 'post_views_count'
WHERE
    p.post_status = 'publish'
        AND p.post_type = 'post'
        AND p.post_date BETWEEN DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'),
        INTERVAL - 1 MONTH) AND LAST_DAY(DATE_ADD(NOW(), INTERVAL - 1 MONTH))
ORDER BY CAST(pm.meta_value AS UNSIGNED) DESC;

# 今月投稿した記事のview数順

SELECT 
    p.post_title,
    pm.meta_value,
    CONCAT('https://blog.yabaiwebyasan.com/',
            p.post_name,
            '/'),
    p.post_date
FROM
    wp_posts p
        INNER JOIN
    wp_postmeta pm ON p.id = pm.post_id
        AND pm.meta_key = 'post_views_count'
WHERE
    p.post_status = 'publish'
        AND p.post_type = 'post'
        AND p.post_date BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND LAST_DAY(NOW())
ORDER BY CAST(pm.meta_value AS UNSIGNED) DESC;