//这是一个弱智片段,效率极其低下,思路可供参考,优化方案欢迎提出。
function searchByToken($token)
{
$searchtoken = $this->db->escape_like_str($token);
$searchtoken = preg_replace("/(\s+)|( +)+/", " ", $searchtoken);//替代空格,换行,tab,中文空格
$searchtoken = preg_replace( "/(^\s*)|(\s*$)/ ", "",$searchtoken);//去除首尾空格
$searchtoken = preg_replace("/(\s+)/", " ", $searchtoken);//替换多个空格为一个空格
$kw = explode(" ",$searchtoken); //keywords
$kw3 = Array();
$clause = Array();
$score = Array();
$for_max=count($kw);
$kw2=$kw;
// for($i=count($kw), $j=0;$j<$for_max;$i++, $j++){
// $kw2[$i] = '['.$kw[$j].']';
// }
//$sc = preg_replace("/\s/", "", $searchtoken); //signal character
$characters = array();
//$kw = strtr($kw,array('大' => ''));
$for_max_i=count($kw);
for($i=0; $i<$for_max_i; $i++) {
$for_max_j=mb_strlen($kw[$i]);
for($j=0; $j<$for_max_j; $j++) {
$characters[$i][$j]= mb_substr($kw[$i],$j,1);
}
}
foreach($characters as $i) {
$kw2[]=implode('%', $i); // use for 'LIKE'
$kw3[] = implode('.*', $i); // use for 'REGEXP'
}
$kw3 = implode('|', $kw3);
// for($i=count($kw2), $j=0;$j<$for_max;$i++, $j++){
// $characters[$i] = mb_substr($sc,$j,1);//枚举关键字
// }
//然后是生成SQL语句的代码
$f = array( "`i_u`.`name`",
"`i_u`.`name2`",
"`i_i`.`institutionName`",
"`i_e_p`.`major`",
"`employer`",
"`position`"
); //查询的字段
$s = array(7,4,5,1,1,1); //权重,最后按积分排序
//创建查询条件语句
$for_max_j=count($f);
foreach($kw2 as $i){
for($j=0;$j<$for_max_j;$j++){
//$clause[$c] = " ($f[$j] LIKE '%$i%') ";
$score[] = " IF($f[$j] LIKE '%$i%', $s[$j], 0) ";
//$score[$c] = " IF(LOCATE('$i', $f[$j]), $s[$j], 0) ";
}
}
for($j=0;$j<$for_max_j;$j++){
$clause[] = " ($f[$j] REGEXP '$kw3') ";
}
/*
//最高学历部分,现在不予判定
INNER JOIN
(SELECT userId, MAX(`type`) AS max_type FROM `imhfer_education` GROUP BY `userId`)
AS `max_i_e`
ON (`i_e`.userId = `max_i_e`.userId AND `i_e`.`type` = `max_i_e`.`max_type`)
*/
$sql ="
SELECT SQL_CACHE
`i_u`.`id`, #用户id
`i_u`.`name`, #用户姓名
`i_u`.`name2`, #曾用名
`i_u`.`email`, #用户邮箱
`i_city`.`cityName`, #用户常驻城市
`i_country`.`countryName`, #用户常驻国家
# `i_e_p`.`institutionId`, #所在学校id
`i_i`.`institutionName`, #所在学校名称
`i_e_p`.`type` AS `educationType`, #最高学历
`i_e_p`.`major`, #专业
`employer`, #雇主
`position`, #职位
`leaveYear`, #高中毕业年份
`leaveClass`, #高中毕业班级,大于0有效
(".implode("+",$score).") AS score
FROM `imhfer_user` AS `i_u`
LEFT OUTER JOIN
((SELECT `i_e`.`userId`,`type`,`institutionId`,`major` FROM `imhfer_education` AS `i_e`
) AS `i_e_p` # 最高学历
INNER JOIN
`imhfer_institution` AS `i_i`
ON `i_e_p`.`institutionId` = `i_i`.`id` #获取学校名称
)
ON `i_u`.`id` = `i_e_p`.`userId`
LEFT OUTER JOIN
(SELECT `i_o`.`userId`,`employer`,`position` FROM `imhfer_occupation` AS `i_o` INNER JOIN
(SELECT userId, MIN(`id`) AS min_id FROM `imhfer_occupation` GROUP BY `userId`)
AS `min_i_o`
ON (`i_o`.userId = `min_i_o`.userId AND `i_o`.`id` = `min_i_o`.`min_id`)
) AS `i_o_p` # 最近职务
ON `i_u`.`id` = `i_o_p`.`userId`
INNER JOIN
`imhfer_city` AS `i_city`
ON `i_u`.city1Id = `i_city`.`id` #获取城市名称
INNER JOIN
`imhfer_country` AS `i_country`
ON `i_city`.`countryId` = `i_country`.`id` #获取国家名称
WHERE (".implode(" OR ",$clause).")
ORDER BY score DESC
LIMIT 40
";