function zem_rp_related_posts_db_table_install()
{
global $wpdb;
$tags_table_name = $wpdb->prefix . "zem_rp_tags";
$sql_tags = "CREATE TABLE {$tags_table_name} (\n\t post_id mediumint(9),\n\t post_date datetime NOT NULL,\n\t label VARCHAR(" . ZEM_RP_MAX_LABEL_LENGTH . ") NOT NULL,\n\t weight float,\n\t KEY post_id (post_id),\n\t KEY label (label)\n\t );";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta($sql_tags);
$latest_posts = get_posts(array('numberposts' => ZEM_RP_RECOMMENDATIONS_NUM_PREGENERATED_POSTS));
foreach ($latest_posts as $post) {
zem_rp_generate_tags($post);
}
}
function zem_rp_fetch_related_posts_v2($limit = 10, $exclude_ids = array())
{
global $wpdb, $post;
$related_post_ids = null;
$options = zem_rp_get_options();
$exclude_ids_str = zem_rp_get_exclude_ids_list_string($exclude_ids);
$tags_query = "SELECT label FROM " . $wpdb->prefix . "zem_rp_tags WHERE post_id={$post->ID};";
$tags = $wpdb->get_col($tags_query, 0);
if (empty($tags)) {
$tags = zem_rp_generate_tags($post);
if (empty($tags)) {
return array();
}
}
if ($options['exclude_categories']) {
$exclude_categories = get_categories(array('include' => $options['exclude_categories']));
$exclude_categories_labels = array_map(create_function('$c', 'return "C_" . $c->name;'), $exclude_categories);
} else {
$exclude_categories_labels = array();
}
$total_number_of_posts = $wpdb->get_col("SELECT count(distinct(post_id)) FROM " . $wpdb->prefix . "zem_rp_tags;", 0);
$total_number_of_posts = $wpdb->get_col("SELECT count(post_id) from (SELECT post_id FROM " . $wpdb->prefix . "zem_rp_tags group by post_id) t;", 0);
if (empty($total_number_of_posts)) {
return array();
}
$total_number_of_posts = $total_number_of_posts[0];
$post_id_query = $wpdb->prepare("\n\t\tSELECT\n\t\t\ttarget.post_id, sum(target.weight * log(%d / least(%d, freqs.freq))) as score\n\t\tFROM\n\t\t\t" . $wpdb->prefix . "zem_rp_tags as target,\n\t\t\t(SELECT label, count(1) as freq FROM " . $wpdb->prefix . "zem_rp_tags\n\t\t\t\tWHERE label IN (" . implode(', ', array_fill(0, count($tags), "%s")) . ")\n\t\t\t\tGROUP BY label\n\t\t\t) as freqs\n\t\tWHERE\n\t\t\ttarget.post_id NOT IN (%s) AND\n\t\t\t" . ($options['max_related_post_age_in_days'] > 0 ? "target.post_date > DATE_SUB(CURDATE(), INTERVAL %s DAY) AND" : "") . "\n\t\t\ttarget.label=freqs.label AND\n\t\t\ttarget.label IN (" . implode(', ', array_fill(0, count($tags), "%s")) . ")" . (empty($exclude_categories_labels) ? "" : " AND\n\t\t\t\ttarget.post_id NOT IN (\n\t\t\t\t\tSELECT post_id FROM " . $wpdb->prefix . "zem_rp_tags\n\t\t\t\t\tWHERE label IN (" . implode(', ', array_fill(0, count($exclude_categories_labels), "%s")) . ")\n\t\t\t\t)") . "\n\t\tGROUP BY target.post_id\n\t\tORDER BY score desc, target.post_id desc\n\t\tLIMIT %d;", array_merge(array($total_number_of_posts, $total_number_of_posts), $tags, array($exclude_ids_str), $options['max_related_post_age_in_days'] > 0 ? array($options['max_related_post_age_in_days']) : array(), $tags, $exclude_categories_labels, array($limit * 2)));
// limit * 2 just in case
$related_posts_with_score = $wpdb->get_results($post_id_query, 0);
if (empty($related_posts_with_score)) {
return array();
}
$related_posts_with_score_map = array();
foreach ($related_posts_with_score as $rp) {
$related_posts_with_score_map[$rp->post_id] = $rp->score;
}
$related_post_ids = array_keys($related_posts_with_score_map);
$now = current_time('mysql', 1);
$post_query = $wpdb->prepare("\n\t\tSELECT post.ID, post.post_title, post.post_excerpt, post.post_content, post.post_date, post.comment_count\n\t\tFROM {$wpdb->posts} as post\n\t\tWHERE post.ID IN (" . implode(', ', array_fill(0, count($related_post_ids), '%d')) . ")\n\t\t\tAND post.post_type = 'post'\n\t\t\tAND post.post_status = 'publish'\n\t\t\tAND post.post_date_gmt < %s", array_merge($related_post_ids, array($now)));
$related_posts = $wpdb->get_results($post_query);
foreach ($related_posts as $rp) {
$rp->zem_rp_score = $related_posts_with_score_map[$rp->ID];
}
usort($related_posts, create_function('$a,$b', 'return $b->zem_rp_score < $a->zem_rp_score ? -1 : 1;'));
$related_posts = array_slice($related_posts, 0, $limit);
return $related_posts;
}
