public function getAutoComplete($text, $sub_id = null)
{
$tokens = JobTerms::tokenize($text . '%');
if (count($tokens) == 0) {
return array();
}
$token_join = JobTerms::token_join_query($tokens, 'e');
return XDB::fetchAllAssoc('SELECT e.jtid AS id, e.full_name AS field, COUNT(DISTINCT p.pid) AS nb
FROM profile_job_term_enum AS e
INNER JOIN profile_job_term_relation AS r ON (r.jtid_1 = e.jtid)
INNER JOIN profile_job_term AS p ON (r.jtid_2 = p.jtid)
' . $token_join . '
GROUP BY e.jtid
ORDER BY nb DESC, field
LIMIT ' . self::AUTOCOMPLETE_LIMIT);
}
#!/usr/bin/php5
<?php
require_once 'connect.db.inc.php';
$globals->debug = 0;
//do not store backtraces
$terms = XDB::iterator('SELECT `jtid`, `name` FROM `profile_job_term_enum`');
while ($term = $terms->next()) {
$tokens = array_unique(JobTerms::tokenize($term['name']));
if (!count($tokens)) {
continue;
}
$values = array();
foreach ($tokens as $t) {
$values[] = '(' . XDB::escape($t) . ',' . XDB::escape($term['jtid']) . ')';
}
XDB::execute('INSERT IGNORE INTO `profile_job_term_search` (`search`,`jtid`) VALUES ' . implode(',', $values));
}
/* vim:set et sw=4 sts=4 ts=4: */
/**
* Page for url "profile/jobterms" (function also used for "referent/autocomplete" @see
* handler_ref_autocomplete). Displays an "autocomplete" page (plain text with values
* separated by "|" chars) for jobterms to add in profile.
* @param $page the Platal page
* @param $type set to 'mentor' to display the number of mentors for each term and order
* by descending number of mentors.
*
* @param Env::v('q') the text that has been typed and to complete automatically
*/
function handler_jobterms($page, $type = 'nomentor')
{
pl_content_headers("text/plain");
$q = Env::v('term') . '%';
$tokens = JobTerms::tokenize($q);
if (count($tokens) == 0) {
exit;
}
sort($tokens);
$q_normalized = implode(' ', $tokens);
// Try to look in cached results.
$cached = false;
$cache = XDB::query('SELECT result
FROM search_autocomplete
WHERE name = {?} AND query = {?} AND generated > NOW() - INTERVAL 1 DAY', $type, $q_normalized);
if ($cache->numRows() > 0) {
$cached = true;
$data = explode("\n", $cache->fetchOneCell());
$list = array();
foreach ($data as $line) {
if ($line != '') {
$aux = explode("\t", $line);
if ($type == 'mentor') {
$item = array('field' => $aux[0], 'nb' => $aux[1], 'id' => $aux[2]);
$item['value'] = SearchModule::format_autocomplete($item);
} else {
$item = array('value' => $aux[0], 'id' => $aux[1]);
}
array_push($list, $item);
}
}
} else {
$joins = JobTerms::token_join_query($tokens, 'e');
if ($type == 'mentor') {
$count = ', COUNT(DISTINCT pid) AS nb';
$countjoin = ' INNER JOIN profile_job_term_relation AS r ON(r.jtid_1 = e.jtid) INNER JOIN profile_mentor_term AS m ON(r.jtid_2 = m.jtid)';
$countorder = 'nb DESC, ';
} else {
$count = $countjoin = $countorder = '';
}
$list = XDB::fetchAllAssoc('SELECT e.jtid AS id, e.full_name AS field' . $count . '
FROM profile_job_term_enum AS e ' . $joins . $countjoin . '
GROUP BY e.jtid
ORDER BY ' . $countorder . 'field
LIMIT ' . DirEnumeration::AUTOCOMPLETE_LIMIT);
$to_cache = '';
if ($type == 'mentor') {
foreach ($list as &$item) {
$to_cache .= $item['field'] . "\t" . $item['nb'] . "\t" . $item['id'] . "\n";
$item['value'] = SearchModule::format_autocomplete($item);
}
} else {
foreach ($list as &$item) {
$to_cache .= $item['field'] . "\t" . $item['id'] . "\n";
$item['value'] = $item['field'];
}
}
}
if (count($list) == DirEnumeration::AUTOCOMPLETE_LIMIT && $type == 'nomentor') {
$list[] = array('value' => '… parcourir les résultats dans un arbre …', 'field' => '', 'id' => -1);
}
if (!$cached) {
XDB::query('INSERT INTO search_autocomplete (name, query, result, generated)
VALUES ({?}, {?}, {?}, NOW())
ON DUPLICATE KEY UPDATE result = VALUES(result), generated = VALUES(generated)', $type, $q_normalized, $to_cache);
}
echo json_encode($list);
exit;
}
