MySQL 查询 wordpress 元数据

Question

我需要一些关于查询的帮助，该查询应该根据他们的 wp-postratings 分数返回帖子 (http://wordpress.org/extend/plugins/wp-postratings/)。

用户选择最低评分(0 到 5 星)和最高评分(0 到 5 星)，查询应返回匹配的帖子。我让它在用户输入的两个值都高于 0 的情况下工作，但我似乎无法理解 0 值。由于 0 代表未评级的帖子 - 因此 onces 没有评级元数据 - 我不仅需要选择评级不超过指定最大值的帖子，而且还需要选择没有评级元数据的每个帖子。

我该怎么做？非常感谢任何帮助!

这是我当前的查询:

SELECT DISTINCT p.*, (t1.meta_value+0.00) AS ratings_average, (t2.meta_value+0.00) AS ratings_users, (t3.meta_value+0.00) AS ratings_score 
FROM wp_posts p 
INNER JOIN wp_term_relationships tr ON (p.ID = tr.object_id) 
INNER JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) 
INNER JOIN wp_terms t ON  t.term_id = tt.term_id
LEFT JOIN wp_postmeta AS t1 ON t1.post_id = p.ID 
LEFT JOIN wp_postmeta AS t2 ON t1.post_id = t2.post_id 
LEFT JOIN wp_postmeta AS t3 ON t3.post_id = p.ID 
WHERE t1.meta_key = 'ratings_average' 
AND t2.meta_key = 'ratings_users' 
AND t3.meta_key = 'ratings_score' 
AND p.post_date < NOW() 
AND p.post_status = 'publish' 
AND (tt.taxonomy = 'post_tag' AND tt.term_id = t.term_id AND t.slug = 'liverpool')
AND ( (t1.meta_value+0.00) IS NULL OR (t1.meta_value+0.00) <= $max_stars )
ORDER BY p.post_date DESC 
LIMIT 20

Answer 1

前阵子我不得不做类似的事情，当时我正在运行一个 cron 作业以将帖子发送到另一个尚未注册的应用程序。我发现的最佳方法是编写一个查询，检查该 ID 是否不在具有元键的帖子查询中。

SELECT $wpdb->posts.ID
FROM $wpdb->posts 
WHERE $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'post'
AND $wpdb->posts.ID NOT IN (
    SELECT $wpdb->posts.ID
    FROM $wpdb->posts
    left join $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
    WHERE $wpdb->posts.post_status = 'publish'
    AND $wpdb->postmeta.meta_key = 'meta_key')

我相信这应该可行，尽管我显然还没有测试过。

SELECT DISTINCT p.*, (t1.meta_value+0.00) AS ratings_average, (t2.meta_value+0.00) AS ratings_users, (t3.meta_value+0.00) AS ratings_score
FROM wp_posts p
INNER JOIN wp_term_relationships tr ON (p.ID = tr.object_id)
INNER JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
INNER JOIN wp_terms t ON t.term_id = tt.term_id
LEFT JOIN wp_postmeta AS t1 ON t1.post_id = p.ID
LEFT JOIN wp_postmeta AS t2 ON t1.post_id = t2.post_id
LEFT JOIN wp_postmeta AS t3 ON t3.post_id = p.ID
WHERE t1.meta_key = 'ratings_average'
AND t2.meta_key = 'ratings_users'
AND t3.meta_key = 'ratings_score'
AND p.post_date < NOW()
AND p.post_status = 'publish'
AND (tt.taxonomy = 'post_tag' 
AND tt.term_id = t.term_id 
AND t.slug = 'liverpool')
AND ( 
p.ID NOT IN (
    SELECT p.ID 
    FROM wp_posts AS p
    LEFT JOIN wp_postmeta AS pm ON (pm.post_id = p.ID)
    WHERE pm.meta_key = 'ratings_score'
)
OR 
(t1.meta_value+0.00) <= $max_stars )
ORDER BY p.post_date DESC
LIMIT 20