Sunday, November 1, 2009

Adding Search Rank to your Expression Engine Search Module

Search Rank is as simple as counting the number of occurrences of the keyword terms in the result. Here are the changes to make to your file, briefly:

Add the case option in the orderby:

case 'rank' : $end .= " ORDER BY occurrences ";

Modify the query:

$sql = "SELECT DISTINCT(t.entry_id), t.entry_id, t.weblog_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.url_title, t.status, t.dst_enabled, t.view_count_one, t.view_count_two, t.view_count_three, t.view_count_four, t.allow_comments, t.comment_expiration_date, t.allow_trackbacks, t.sticky, t.entry_date, t.year, t.month,, t.entry_date, t.edit_date, t.expiration_date, t.recent_comment_date, t.comment_total, t.trackback_total, t.sent_trackbacks, t.recent_trackback_date, t.site_id as entry_site_id,

w.blog_title, w.blog_name, w.search_results_url, w.search_excerpt, w.blog_url, w.comment_url, w.tb_return_url, w.comment_moderate, w.weblog_html_formatting, w.weblog_allow_img_urls, w.weblog_auto_link_urls, w.enable_trackbacks, w.trackback_use_url_title, w.trackback_field, w.trackback_use_captcha, w.trackback_system_enabled,
m.username,, m.url, m.screen_name, m.location, m.occupation, m.interests, m.aol_im, m.yahoo_im, m.msn_im, m.icq, m.signature, m.sig_img_filename, m.sig_img_width, m.sig_img_height, m.avatar_filename, m.avatar_width, m.avatar_height, m.photo_filename, m.photo_width, m.photo_height, m.group_id, m.member_id, m.bday_d, m.bday_m, m.bday_y,,

(LENGTH(t.title) - LENGTH(REPLACE(LOWER(t.title), LOWER('".$DB->escape_str((sizeof($terms) == 1) ? $terms[0] : $this->keywords)."'), '')))/LENGTH('".$DB->escape_str((sizeof($terms) == 1) ? $terms[0] : $this->keywords)."') + (LENGTH(wd.field_id_1) - LENGTH(REPLACE(LOWER(wd.field_id_1), LOWER('".$DB->escape_str((sizeof($terms) == 1) ? $terms[0] : $this->keywords)."'), '')))/LENGTH('".$DB->escape_str((sizeof($terms) == 1) ? $terms[0] : $this->keywords)."') + (LENGTH(wd.field_id_2) - LENGTH(REPLACE(LOWER(wd.field_id_2), LOWER('".$DB->escape_str((sizeof($terms) == 1) ? $terms[0] : $this->keywords)."'), '')))/LENGTH('".$DB->escape_str((sizeof($terms) == 1) ? $terms[0] : $this->keywords)."') AS `occurrences`

FROM exp_weblog_titles AS t
LEFT JOIN exp_weblogs AS w ON t.weblog_id = w.weblog_id
LEFT JOIN exp_weblog_data AS wd ON t.entry_id = wd.entry_id
LEFT JOIN exp_members AS m ON m.member_id = t.author_id
LEFT JOIN exp_member_data AS md ON md.member_id = m.member_id
WHERE t.entry_id IN (";

foreach ($query->result as $row)
$sql .= $row['entry_id'].',';

$sql = substr($sql, 0, -1).') '.$end;

return $sql;

Modify the query result:

$query = $DB->query(preg_replace("/SELECT(.*?)\s+FROM\s+/is", "SELECT COUNT(*) AS count, COUNT(*) AS `occurrences` FROM ", $sql));

Modify the language term for rank:

'lang:rank ' => $LANG->line('search_rank'),

With the modified module in place, you will want to change your Search form input to "rank":

<input type="hidden" name="orderby" value="rank" />

You'll be searching by rank in no time. Enjoy.