sql - 修改 Wordpress SQL 查询以从类别中提取

Question

我正在使用一个名为“kf most read”的 wordpress 插件，它存储帖子被阅读次数的计数，并允许您输出最常阅读帖子的列表。

这很好用。问题是，我试图提取阅读次数最多的帖子，但只提取您正在查看的当前类别中阅读次数最多的帖子。

对于sql，我几乎一窍不通。

这里是插件当前用来拉取阅读最多的帖子的内容:

$sql = "SELECT count(mr.post_ID) as totHits, p.ID, p.post_title from $wpdb->posts p JOIN {$wpdb->prefix}kf_most_read mr on mr.post_ID = p.ID where mr.hit_ts >= '".(time() - ( 86400 * $period))."' GROUP BY mr.post_ID order by totHits desc, ID ASC LIMIT $limit";

我如何将从特定类别中提取的以下查询合并到上面的查询中？

$sql .= "LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)" ; $sql .= "WHERE $wpdb->term_taxonomy.term_id IN ($currentcat)" ; $sql .= "AND $wpdb->term_taxonomy.taxonomy = 'category'" ;

如有任何帮助，我们将不胜感激。

Answer 1

您需要将 from + join 部分合并在一起，并将 where 子句合并在一起。您的原始查询是

SELECT  count(mr.post_ID) as totHits, p.ID, p.post_title
-- from & join
  FROM  $wpdb->posts p
  JOIN  {$wpdb->prefix}kf_most_read mr
    ON  mr.post_ID = p.ID
-- where
  WHERE mr.hit_ts >= '".(time() - ( 86400 * $period))."'
-- group, etc.
GROUP BY mr.post_ID
ORDER BY totHits desc, ID ASC LIMIT $limit

和你的附加条款

-- from & join
LEFT JOIN $wpdb->term_taxonomy
    ON  ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
-- where
  WHERE $wpdb->term_taxonomy.term_id IN ($currentcat)
    AND $wpdb->term_taxonomy.taxonomy = 'category'

所以合并查询应该是

SELECT  count(mr.post_ID) as totHits, p.ID, p.post_title
-- from & join
  FROM  $wpdb->posts p
  JOIN  {$wpdb->prefix}kf_most_read mr
    ON mr.post_ID = p.ID
LEFT JOIN $wpdb->term_taxonomy
    ON  ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
-- where
  WHERE mr.hit_ts >= '".(time() - ( 86400 * $period))."'
    AND $wpdb->term_taxonomy.term_id IN ($currentcat)
    AND $wpdb->term_taxonomy.taxonomy = 'category'
-- group, etc.
GROUP BY mr.post_ID
ORDER BY totHits desc, ID ASC LIMIT $limit

(注意你只能有一个 where 所以添加第二个作为 and)
即类似

$sql = "SELECT count(mr.post_ID) as totHits, p.ID, p.post_title FROM  $wpdb->posts p"
$sql .= " JOIN  {$wpdb->prefix}kf_most_read mr ON mr.post_ID = p.ID"
$sql .= " LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)"
$sql .= " WHERE mr.hit_ts >= '".(time() - ( 86400 * $period))."'"
$sql .= " AND $wpdb->term_taxonomy.term_id IN ($currentcat)"
$sql .= " AND $wpdb->term_taxonomy.taxonomy = 'category'"
$sql .= " GROUP BY mr.post_ID ORDER BY totHits desc, ID ASC LIMIT $limit"

取决于您喜欢换行符的方式。希望有用!

实际上，我怀疑您并不是真的想要类别/分类表的 LEFT JOIN，而应该使用普通的 JOIN。但我不是 WP 架构专家。