SQL字符串为我提供了一个类别的所有帖子，但没有没有

Question

I'm running a Wordpress site, and I'm making a widget that gives me all posts that are the most read in the last 8 days.

When I run this SQL string I get all the posts with a category, but when a post isn't in a category, I don't get it.

How do I get all the posts that ARE in a category, AND all the posts that AREN'T in a category?

My sql string

$sql ="SELECT wp_posts.post_date, wp_posts.post_title, wp_posts.post_name, wp_posts.post_content, wp_terms.name, wp_terms.slug, wp_postmeta.meta_value FROM wp_posts
        INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
        INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
        INNER JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
        INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
        WHERE (wp_term_taxonomy.taxonomy = 'category'
           AND wp_postmeta.meta_key = 'readCount'
           AND wp_posts.post_type = 'post'
           AND DATE(wp_posts.post_date) >= '$eight_days_ago'
           AND wp_posts.post_status = 'publish')
           GROUP BY wp_posts.ID
           ORDER BY wp_postmeta.meta_value DESC
           LIMIT 5";

Answer 1

I think this will work. You need to change the joins to left outer joins and then check for NULL in the where clause:

SELECT wp_posts.post_date, wp_posts.post_title, wp_posts.post_name, wp_posts.post_content, wp_terms.name, wp_terms.slug, wp_postmeta.meta_value FROM wp_posts
        LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
        LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
        LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
        LEFT JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
        WHERE ((wp_term_taxonomy.taxonomy = 'category') or (wp_term_taxonomy.taxonomy is NULL))
           AND wp_postmeta.meta_key = 'readCount'
           AND wp_posts.post_type = 'post'
           AND DATE(wp_posts.post_date) >= '$eight_days_ago'
           AND wp_posts.post_status = 'publish')
           GROUP BY wp_posts.ID
           ORDER BY wp_postmeta.meta_value DESC
           LIMIT 5