<?php yarpp_save_cache($reference_ID, false); // enforce the cache, but don't force it $body_terms = yarpp_get_cached_keywords($reference_ID, 'body'); $title_terms = yarpp_get_cached_keywords($reference_ID, 'title'); if ($yarpp_debug) { $output .= "<p>last updated keywords: " . $wpdb->get_var("select date from {$wpdb->prefix}yarpp_keyword_cache where ID = '{$reference_ID}'") . "</p>"; } if ($yarpp_debug) { $output .= "<p>body keywords: {$body_terms}</p>"; } if ($yarpp_debug) { $output .= "<p>title keywords: {$title_terms}</p>"; } $output .= '<p>' . __('These are the related entries for this entry. Updating this post may change these related posts.', 'yarpp') . '</p>'; if ($yarpp_debug) { $output .= "<p>last updated: " . $wpdb->get_var("select max(date) as updated from {$wpdb->prefix}yarpp_related_cache where reference_ID = '{$reference_ID}'") . "</p>"; } if ($related_query->have_posts()) { $output .= '<ol>'; while ($related_query->have_posts()) { $related_query->the_post(); $output .= "<li><a href='post.php?action=edit&post={$id}'>" . get_the_title() . "</a>"; // TODO: what if it's a page? $output .= ' (' . round(get_the_score(), 3) . ')'; $output .= '</li>'; } $output .= '</ol>'; $output .= '<p>' . __('Whether all of these related entries are actually displayed and how they are displayed depends on your YARPP display options.', 'yarpp') . '</p>'; } else {
function yarpp_sql($type, $args, $giveresults = true, $reference_ID = false, $domain = 'website') { global $wpdb, $post, $yarpp_debug; if (is_object($post) and !$reference_ID) { $reference_ID = $post->ID; } // set the "domain prefix", used for all the preferences. if ($domain == 'rss') { $domainprefix = 'rss_'; } else { $domainprefix = ''; } $options = array('limit' => "{$domainprefix}limit", 'threshold' => 'threshold', 'show_pass_post' => 'show_pass_post', 'past_only' => 'past_only', 'cross_relate' => 'cross_relate', 'body' => 'body', 'title' => 'title', 'tags' => 'tags', 'categories' => 'categories', 'distags' => 'distags', 'discats' => 'discats', 'recent_only' => 'recent_only', 'recent_number' => 'recent_number', 'recent_units' => 'recent_units'); $optvals = array(); foreach (array_keys($options) as $option) { if (isset($args[$option])) { $optvals[$option] = stripslashes($args[$option]); } else { $optvals[$option] = stripslashes(stripslashes(yarpp_get_option($options[$option]))); } } extract($optvals); // Fetch keywords $body_terms = yarpp_get_cached_keywords($reference_ID, 'body'); $title_terms = yarpp_get_cached_keywords($reference_ID, 'title'); if ($yarpp_debug) { echo "<!--TITLE TERMS: {$title_terms}-->"; } // debug if ($yarpp_debug) { echo "<!--BODY TERMS: {$body_terms}-->"; } // debug // get weights $bodyweight = $body == 3 ? 3 : ($body == 2 ? 1 : 0); $titleweight = $title == 3 ? 3 : ($title == 2 ? 1 : 0); $tagweight = $tags != 1 ? 1 : 0; $catweight = $categories != 1 ? 1 : 0; $weights = array(); $weights['body'] = $bodyweight; $weights['title'] = $titleweight; $weights['cat'] = $catweight; $weights['tag'] = $tagweight; $totalweight = $bodyweight + $titleweight + $tagweight + $catweight; // get disallowed categories and tags $disterms = implode(',', array_filter(array_merge(explode(',', $discats), explode(',', $distags)), 'is_numeric')); $usedisterms = count(array_filter(array_merge(explode(',', $discats), explode(',', $distags)), 'is_numeric')); $criteria = array(); if ($bodyweight) { $criteria['body'] = "(MATCH (post_content) AGAINST ('" . $wpdb->escape($body_terms) . "'))"; } if ($titleweight) { $criteria['title'] = "(MATCH (post_title) AGAINST ('" . $wpdb->escape($title_terms) . "'))"; } if ($tagweight) { $criteria['tag'] = "COUNT( DISTINCT tagtax.term_taxonomy_id )"; } if ($catweight) { $criteria['cat'] = "COUNT( DISTINCT cattax.term_taxonomy_id )"; } $newsql = "SELECT {$reference_ID}, ID, "; //post_title, post_date, post_content, post_excerpt, //foreach ($criteria as $key => $value) { // $newsql .= "$value as ${key}score, "; //} $newsql .= '(0'; foreach ($criteria as $key => $value) { $newsql .= "+ {$value} * " . $weights[$key]; } $newsql .= ') as score'; $newsql .= "\n from {$wpdb->posts} \n"; if ($usedisterms) { $newsql .= " left join {$wpdb->term_relationships} as blockrel on ({$wpdb->posts}.ID = blockrel.object_id)\n\t\tleft join {$wpdb->term_taxonomy} as blocktax using (`term_taxonomy_id`)\n\t\tleft join {$wpdb->terms} as blockterm on (blocktax.term_id = blockterm.term_id and blockterm.term_id in ({$disterms}))\n"; } if ($tagweight) { $newsql .= " left JOIN {$wpdb->term_relationships} AS thistag ON (thistag.object_id = {$reference_ID} ) \n\t\tleft JOIN {$wpdb->term_relationships} AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id\n\t\tAND tagrel.object_id = {$wpdb->posts}.ID)\n\t\tleft JOIN {$wpdb->term_taxonomy} AS tagtax ON ( tagrel.term_taxonomy_id = tagtax.term_taxonomy_id\n\t\tAND tagtax.taxonomy = 'post_tag')\n"; } if ($catweight) { $newsql .= " left JOIN {$wpdb->term_relationships} AS thiscat ON (thiscat.object_id = {$reference_ID} ) \n\t\tleft JOIN {$wpdb->term_relationships} AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id\n\t\tAND catrel.object_id = {$wpdb->posts}.ID)\n\t\tleft JOIN {$wpdb->term_taxonomy} AS cattax ON ( catrel.term_taxonomy_id = cattax.term_taxonomy_id\n\t\tAND cattax.taxonomy = 'category')\n"; } // WHERE $newsql .= " where (post_status IN ( 'publish', 'static' ) and ID != '{$reference_ID}')"; if ($past_only) { // 3.1.8: revised $past_only option if (is_object($post) && $reference_ID == $post->ID) { $reference_post_date = $post->post_date; } else { $reference_post_date = $wpdb->get_var("select post_date from {$wpdb->posts} where ID = {$reference_ID}"); } $newsql .= " and post_date <= '{$reference_post_date}' "; } if (!$show_pass_post) { $newsql .= " and post_password ='' "; } if ($recent_only) { $newsql .= " and post_date > date_sub(now(), interval {$recent_number} {$recent_units}) "; } if ($type == array('page') && !$cross_relate) { $newsql .= " and post_type = 'page'"; } else { $newsql .= " and post_type = 'post'"; } // GROUP BY $newsql .= "\n group by id \n"; // HAVING // safethreshold is so the new calibration system works. // number_format fix suggested by vkovalcik! :) $safethreshold = number_format(max($threshold, 0.1), 2, '.', ''); $newsql .= " having score >= {$safethreshold}"; if ($usedisterms) { $newsql .= " and count(blockterm.term_id) = 0"; } $newsql .= $categories == 3 ? ' and ' . $criteria['cat'] . ' >= 1' : ''; $newsql .= $categories == 4 ? ' and ' . $criteria['cat'] . ' >= 2' : ''; $newsql .= $tags == 3 ? ' and ' . $criteria['tag'] . ' >= 1' : ''; $newsql .= $tags == 4 ? ' and ' . $criteria['tag'] . ' >= 2' : ''; $newsql .= " order by score desc limit " . $limit; if (!$giveresults) { $newsql = "select count(t.ID) from ({$newsql}) as t"; } // if we're looking for a X related entries, make sure we get at most X posts and X pages if // we cross-relate if ($cross_relate) { $newsql = "({$newsql}) union (" . str_replace("post_type = 'post'", "post_type = 'page'", $newsql) . ")"; } if ($yarpp_debug) { echo "<!--{$newsql}-->"; } return $newsql; }