diff options
Diffstat (limited to 'include')
| -rw-r--r-- | include/functions_search.inc.php | 361 | ||||
| -rw-r--r-- | include/section_init.inc.php | 7 | ||||
| -rw-r--r-- | include/ws_functions.inc.php | 52 |
3 files changed, 238 insertions, 182 deletions
diff --git a/include/functions_search.inc.php b/include/functions_search.inc.php index 0c2e61214..c34d31463 100644 --- a/include/functions_search.inc.php +++ b/include/functions_search.inc.php @@ -253,150 +253,194 @@ SELECT DISTINCT(id) /** * returns the LIKE sql clause corresponding to the quick search query $q - * and the field $field. example q="john bill", field="file" will return - * file LIKE "%john%" OR file LIKE "%bill%". Special characters for MySql - * full text search (+,<,>) are omitted. + * and the field $field. example q='john bill', field='file' will return + * file LIKE '%john%' OR file LIKE '%bill%'. Special characters for MySql full + * text search (+,<,>,~) are omitted. The query can contain a phrase: + * 'Pierre "New York"' will return LIKE '%Pierre%' OR LIKE '%New York%'. * @param string q * @param string field * @return string */ function get_qsearch_like_clause($q, $field) { - $tokens = preg_split('/[\s,.;!\?]+/', $q); - for ($i=0; $i<count($tokens); $i++) + $q = stripslashes($q); + $tokens = array(); + $token_modifiers = array(); + $crt_token = ""; + $crt_token_modifier = ""; + $state = 0; + + for ($i=0; $i<strlen($q); $i++) { - $tokens[$i]=str_replace('*','%', $tokens[$i]); - if (preg_match('/^[+<>]/',$tokens[$i]) ) - $tokens[$i]=substr($tokens[$i], 1); - else if (substr($tokens[$i], 0, 1)=='-') + $ch = $q[$i]; + switch ($state) { - unset($tokens[$i]); - $i--; + case 0: + if ($ch=='"') + { + if (strlen($crt_token)) + { + $tokens[] = $crt_token; + $token_modifiers[] = $crt_token_modifier; + $crt_token = ""; + $crt_token_modifier = ""; + } + $state=1; + } + elseif ( $ch=='*' ) + { // wild card + $crt_token .= '%'; + } + elseif ( strcspn($ch, '+-><~')==0 ) + { //special full text modifier + if (strlen($crt_token)) + { + $tokens[] = $crt_token; + $token_modifiers[] = $crt_token_modifier; + $crt_token = ""; + $crt_token_modifier = ""; + } + $crt_token_modifier .= $ch; + } + elseif (preg_match('/[\s,.;!\?]+/', $ch)) + { // white space + if (strlen($crt_token)) + { + $tokens[] = $crt_token; + $token_modifiers[] = $crt_token_modifier; + $crt_token = ""; + $crt_token_modifier = ""; + } + } + else + { + $crt_token .= $ch; + } + break; + case 1: // qualified with quotes + switch ($ch) + { + case '"': + $tokens[] = $crt_token; + $token_modifiers[] = $crt_token_modifier; + $crt_token = ""; + $crt_token_modifier = ""; + $state=0; + break; + default: + $crt_token .= $ch; + } + break; } } + if (strlen($crt_token)) + { + $tokens[] = $crt_token; + $token_modifiers[] = $crt_token_modifier; + } - if (!empty($tokens)) + $clauses = array(); + for ($i=0; $i<count($tokens); $i++) { - $query = '('; - for ($i=0; $i<count($tokens); $i++) - { - if ($i>0) $query .= 'OR '; - $query .= ' '.$field.' LIKE "%'.$tokens[$i].'%" '; - } - $query .= ')'; - return $query; + $tokens[$i] = trim($tokens[$i], '%'); + if (strstr($token_modifiers[$i], '-')!==false) + continue; + if ( strlen($tokens[$i])==0) + continue; + $clauses[] = $field.' LIKE "%'.addslashes($tokens[$i]).'%"'; } - return null; + + return count($clauses) ? '('.implode(' OR ', $clauses).')' : null; } /** - * returns the search results (array of image ids) corresponding to a - * quick/query search. A quick/query search returns many items (search is - * not strict), but results are sorted by relevance. + * returns the search results corresponding to a quick/query search. + * A quick/query search returns many items (search is not strict), but results + * are sorted by relevance unless $page['super_order_by'] is set. Returns: + * array ( + * 'items' => array(85,68,79...) + * 'as_is' => 1 (indicates the caller that items are ordered and permissions checked + * 'qs' => array( + * 'matching_tags' => array(85,86) - matching tags + * 'matching_cats' => array(1,2,3) - matching categories + * 'matching_cats_no_images' =>array(99) - matching categories without images + * )) * * @param string q + * @param string images_where optional aditional restriction on images table * @return array */ -function get_quick_search_results($q) +function get_quick_search_results($q, $images_where='') { global $page; - $search_results = array(); + $search_results = + array( + 'items' => array(), + 'as_is' => 1, + 'qs' => array('q'=>stripslashes($q)), + ); $q = trim($q); if (empty($q)) { - $search_results['items'] = array(); return $search_results; } - // prepare the big join on images, comments and categories - $query = ' -SELECT - i.id, CAST( CONCAT_WS(" ", - IFNULL(i.name,""), - IFNULL(i.comment,""), - IFNULL(GROUP_CONCAT(DISTINCT co.content),""), - IFNULL(GROUP_CONCAT(DISTINCT c.dir),""), - IFNULL(GROUP_CONCAT(DISTINCT c.name),""), - IFNULL(GROUP_CONCAT(DISTINCT c.comment),"") ) AS CHAR) AS ft -FROM ( - ( - '.IMAGES_TABLE.' i LEFT JOIN '.COMMENTS_TABLE.' co on i.id=co.image_id - ) - INNER JOIN - '.IMAGE_CATEGORY_TABLE.' ic on ic.image_id=i.id - ) - INNER JOIN - '.CATEGORIES_TABLE.' c on c.id=ic.category_id -'.get_sql_condition_FandF - ( - array - ( - 'forbidden_categories' => 'category_id', - 'visible_categories' => 'category_id', - 'visible_images' => 'i.id' - ), - 'WHERE' - ).' -GROUP BY i.id'; + $q_like_field = '@@__db_field__@@'; //something never in a search + $q_like_clause = get_qsearch_like_clause($q, $q_like_field ); - $query = 'SELECT id, MATCH(ft) AGAINST( "'.$q.'" IN BOOLEAN MODE) AS q FROM ('.$query.') AS Y -WHERE MATCH(ft) AGAINST( "'.$q.'" IN BOOLEAN MODE)'; + + // Step 1 - first we find matches in #images table =========================== + $where_clauses='MATCH(i.name, i.comment) AGAINST( "'.$q.'" IN BOOLEAN MODE)'; + if (!empty($q_like_clause)) + { + $where_clauses .= ' + OR '. str_replace($q_like_field, 'file', $q_like_clause); + $where_clauses = '('.$where_clauses.')'; + } + $where_clauses = array($where_clauses); + if (!empty($images_where)) + { + $where_clauses[]='('.$images_where.')'; + } + $where_clauses[] .= get_sql_condition_FandF + ( + array( 'visible_images' => 'i.id' ), null, true + ); + $query = ' +SELECT i.id, + MATCH(i.name, i.comment) AGAINST( "'.$q.'" IN BOOLEAN MODE) AS weight + FROM '.IMAGES_TABLE.' i + WHERE '.implode("\n AND ", $where_clauses); $by_weights=array(); $result = pwg_query($query); while ($row = mysql_fetch_array($result)) { // weight is important when sorting images by relevance - if ($row['q']) + if ($row['weight']) { - $by_weights[(int)$row['id']] = 2*$row['q']; + $by_weights[(int)$row['id']] = 2*$row['weight']; } - } - - $permissions_checked = true; - // now search the file name separately (not done in full text because slower - // and the filename in pwg doesn't have spaces so full text is meaningless ) - $q_like_clause = get_qsearch_like_clause($q, 'file' ); - if (!empty($q_like_clause)) - { - $query = ' -SELECT id - FROM '.IMAGES_TABLE.' - WHERE '.$q_like_clause. - get_sql_condition_FandF - ( - array - ( - 'visible_images' => 'id' - ), - 'AND' - ); - $result = pwg_query($query); - while ($row = mysql_fetch_assoc($result)) - { // weight is important when sorting images by relevance - $id=(int)$row['id']; - @$by_weights[$id] += 2; - $permissions_checked = false; + else + {//full text does not match but file name match + $by_weights[(int)$row['id']] = 2; } } - // now search tag names corresponding to the query $q. we could have searched - // tags earlier during the big join, but for the sake of the performance and - // because tags have only a simple name we do it separately - $q_like_clause = get_qsearch_like_clause($q, 'CONVERT(name, CHAR)' ); + + // Step 2 - search tags corresponding to the query $q ======================== if (!empty($q_like_clause)) - { - // search also by url name (without accents) - $q_like_clause_url = get_qsearch_like_clause($q, 'url_name' ); + { // search name and url name (without accents) $query = ' SELECT id FROM '.TAGS_TABLE.' - WHERE '.$q_like_clause.' - OR '.$q_like_clause_url; + WHERE ('.str_replace($q_like_field, 'CONVERT(name, CHAR)', $q_like_clause).' + OR '.str_replace($q_like_field, 'url_name', $q_like_clause).')'; $tag_ids = array_from_query( $query, 'id'); if (!empty($tag_ids)) - { // we got some tags + { // we got some tags; get the images + $search_results['qs']['matching_tags']=$tag_ids; $query = ' -SELECT image_id, COUNT(tag_id) AS q +SELECT image_id, COUNT(tag_id) AS weight FROM '.IMAGE_TAG_TABLE.' WHERE tag_id IN ('.implode(',',$tag_ids).') GROUP BY image_id'; @@ -404,53 +448,95 @@ SELECT image_id, COUNT(tag_id) AS q while ($row = mysql_fetch_assoc($result)) { // weight is important when sorting images by relevance $image_id=(int)$row['image_id']; - @$by_weights[$image_id] += $row['q']; - $permissions_checked = false; + @$by_weights[$image_id] += $row['weight']; } } } - //at this point, found images might contain images not allowed for the user - if ( !$permissions_checked - and !empty($by_weights) - and !isset($page['super_order_by']) ) + + // Step 3 - search categories corresponding to the query $q ================== + global $user; + $query = ' +SELECT id, nb_images + FROM '.CATEGORIES_TABLE.' + INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.' ON id=cat_id + WHERE user_id='.$user['id'].' + AND MATCH(name, comment) AGAINST( "'.$q.'" IN BOOLEAN MODE)'. + get_sql_condition_FandF ( + array( 'visible_categories' => 'cat_id' ), "\n AND" + ); + $result = pwg_query($query); + while ($row = mysql_fetch_assoc($result)) + { // weight is important when sorting images by relevance + if ($row['nb_images']==0) + { + $search_results['qs']['matching_cats_no_images'][] = $row['id']; + } + else + { + $search_results['qs']['matching_cats'][] = $row['id']; + } + } + + if ( empty($by_weights) and empty($search_results['qs']['matching_cats']) ) { - // before returning the result "as is", make sure the user has the - // permissions for every item - global $conf; - $query = ' + return $search_results; + } + + // Step 4 - now we have $by_weights ( array image id => weight ) that need + // permission checks and/or matching categories to get images from + $where_clauses = array(); + if ( !empty($by_weights) ) + { + $where_clauses[]='i.id IN (' + . implode(',', array_keys($by_weights)) . ')'; + } + if ( !empty($search_results['qs']['matching_cats']) ) + { + $where_clauses[]='category_id IN ('. + implode(',',$search_results['qs']['matching_cats']).')'; + } + $where_clauses = array( '('.implode("\n OR ",$where_clauses).')' ); + if (!empty($images_where)) + { + $where_clauses[]='('.$images_where.')'; + } + $where_clauses[] = get_sql_condition_FandF( + array + ( + 'forbidden_categories' => 'category_id', + 'visible_categories' => 'category_id', + 'visible_images' => 'i.id' + ), + null,true + ); + + global $conf; + $query = ' SELECT DISTINCT(id) - FROM '.IMAGES_TABLE.' + FROM '.IMAGES_TABLE.' i INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id = ic.image_id - WHERE id IN ('.implode(',', array_keys($by_weights) ).') -'.get_sql_condition_FandF - ( - array - ( - 'forbidden_categories' => 'category_id', - 'visible_categories' => 'category_id', - 'visible_images' => 'id' - ), - 'AND' - ).' - '.$conf['order_by']; - $allowed_images = array_flip( array_from_query( $query, 'id') ); - $by_weights = array_intersect_key($by_weights, $allowed_images ); - $divisor = 4.0 * count($allowed_images); - // decrease weight from 0 to 0.25 corresponding to the order - foreach ($allowed_images as $id=>$rank ) - { - $by_weights[$id] -= $rank / $divisor; - } - $permissions_checked = true; + WHERE '.implode("\n AND ", $where_clauses)."\n". + $conf['order_by']; + + $allowed_images = array_from_query( $query, 'id'); + + if ( isset($page['super_order_by']) or empty($by_weights) ) + { + $search_results['items'] = $allowed_images; + return $search_results; } - arsort($by_weights, SORT_NUMERIC); - if ( $permissions_checked ) + + $allowed_images = array_flip( $allowed_images ); + $divisor = 5.0 * count($allowed_images); + foreach ($allowed_images as $id=>$rank ) { - $search_results['as_is']=1; + $weight = isset($by_weights[$id]) ? $by_weights[$id] : 1; + $weight -= $rank/$divisor; + $allowed_images[$id] = $weight; } - - $search_results['items'] = array_keys($by_weights); + arsort($allowed_images, SORT_NUMERIC); + $search_results['items'] = array_keys($allowed_images); return $search_results; } @@ -458,9 +544,10 @@ SELECT DISTINCT(id) * returns an array of 'items' corresponding to the search id * * @param int search id + * @param string images_where optional aditional restriction on images table * @return array */ -function get_search_results($search_id) +function get_search_results($search_id, $images_where='') { $search = get_search_array($search_id); if ( !isset($search['q']) ) @@ -470,7 +557,7 @@ function get_search_results($search_id) } else { - return get_quick_search_results($search['q']); + return get_quick_search_results($search['q'], $images_where); } } ?>
\ No newline at end of file diff --git a/include/section_init.inc.php b/include/section_init.inc.php index f5b301677..a43d5ea0d 100644 --- a/include/section_init.inc.php +++ b/include/section_init.inc.php @@ -329,6 +329,10 @@ SELECT DISTINCT(id) else { $page['items'] = $search_result['items']; + if ( isset($search_result['qs']) ) + {//save the details of the query search + $page['qsearch_details'] = $search_result['qs']; + } } $page = array_merge( @@ -519,7 +523,8 @@ SELECT id,file // add meta robots noindex, nofollow to avoid unnecesary robot crawls $page['meta_robots']=array(); -if ( isset($page['chronology_field']) or isset($page['flat']) +if ( isset($page['chronology_field']) + or ( isset($page['flat']) and isset($page['category']) ) or 'list'==$page['section'] or 'recent_pics'==$page['section'] ) { $page['meta_robots']=array('noindex'=>1, 'nofollow'=>1); diff --git a/include/ws_functions.inc.php b/include/ws_functions.inc.php index 808f45644..1adc48c39 100644 --- a/include/ws_functions.inc.php +++ b/include/ws_functions.inc.php @@ -759,55 +759,19 @@ function ws_images_search($params, &$service) include_once( PHPWG_ROOT_PATH .'include/functions_search.inc.php' ); include_once(PHPWG_ROOT_PATH.'include/functions_picture.inc.php'); - $where_clauses = ws_std_image_sql_filter( $params ); - $order_by = ws_std_image_sql_order($params); + $where_clauses = ws_std_image_sql_filter( $params, 'i.' ); + $order_by = ws_std_image_sql_order($params, 'i.'); - if ( !empty($where_clauses) and !empty($order_by) ) + if ( !empty($order_by) ) { + global $conf; + $conf['order_by'] = 'ORDER BY '.$order_by; $page['super_order_by']=1; // quick_search_result might be faster } - $search_result = get_quick_search_results($params['query']); - global $image_ids; //needed for sorting by rank (usort) - if ( ( !isset($search_result['as_is']) - or !empty($where_clauses) - or !empty($order_by) ) - and !empty($search_result['items']) ) - { - $where_clauses[] = 'id IN (' - .wordwrap(implode(', ', $search_result['items']), 80, "\n") - .')'; - $where_clauses[] = get_sql_condition_FandF( - array - ( - 'forbidden_categories' => 'category_id', - 'visible_categories' => 'category_id', - 'visible_images' => 'id' - ), - '', true - ); - $query = ' -SELECT DISTINCT id FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id=image_id - WHERE '.implode(' - AND ', $where_clauses); - if (!empty($order_by)) - { - $query .= ' - ORDER BY '.$order_by; - } - $image_ids = array_from_query($query, 'id'); - global $ranks; - $ranks = array_flip( $search_result['items'] ); - usort( - $image_ids, - create_function('$i1,$i2', 'global $ranks; return $ranks[$i1]-$ranks[$i2];') - ); - unset ($ranks); - } - else - { - $image_ids = $search_result['items']; - } + $search_result = get_quick_search_results($params['query'], + implode(',', $where_clauses) ); + $image_ids = $search_result['items']; $image_ids = array_slice($image_ids, $params['page']*$params['per_page'], |