Modify Wordpress SQL Query to pull from within a category

Question

I am using a wordpress plugin called "kf most read" which stores a count of how many times a post was read, and lets you output a list of most read posts.

This works well. The issue is, I am trying to pull the most read posts, but only the most read posts within the current category you are viewing.

I am close to clueless when it comes to sql.

Here us what the plugin is currently using to pull the most read posts:

$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";

How could I incorporate the below query which pulls from a specific category into the above?

$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'" ;

Any Help on this would be much appreciated.

You might have better luck here wordpress.stackexchange.com as its a focused community for wordpress — Robb

Rup Rup · Accepted Answer · 2011-01-07T15:42:35

You need to merge the from + join sections together, and the where clauses together. Your original query is

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

and your extra clauses

-- 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'

so the merged query should be

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

(NB you can only have one where so the adding the second as an and)
i.e. something like

$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"

depending on how you like your line-breaks. Hope that works!

Actually I suspect you don't really want a LEFT JOIN for the categories / taxonomy table but should have a plain JOIN instead. But I'm no WP schema expert.

Modify Wordpress SQL Query to pull from within a category

1 Answers