From cde423fc86a8aaa6054179ca17e534f53df1a422 Mon Sep 17 00:00:00 2001 From: rvelices Date: Wed, 23 Jul 2008 00:56:22 +0000 Subject: - normalize behaviour of query search versus std search (now both return items already sorted and permission checked); also more optimized sql queries (in some cases) git-svn-id: http://piwigo.org/svn/trunk@2451 68402e56-0260-453c-a942-63ccdbb3a9ee --- include/config_default.inc.php | 1 - include/functions_search.inc.php | 83 +++++++++++++++++++++++++++++----------- include/section_init.inc.php | 27 +++---------- include/ws_functions.inc.php | 31 ++++++++------- 4 files changed, 82 insertions(+), 60 deletions(-) (limited to 'include') diff --git a/include/config_default.inc.php b/include/config_default.inc.php index e7a8c117d..019220b29 100644 --- a/include/config_default.inc.php +++ b/include/config_default.inc.php @@ -666,7 +666,6 @@ $conf['filter_pages'] = array 'nbm' => array('used' => false), 'popuphelp' => array('used' => false), 'profile' => array('used' => false), - 'web_service' => array('used' => false), 'ws' => array('used' => false), 'identification' => array('cancel' => true), 'install' => array('cancel' => true), diff --git a/include/functions_search.inc.php b/include/functions_search.inc.php index 7069bd5c9..a043f041c 100644 --- a/include/functions_search.inc.php +++ b/include/functions_search.inc.php @@ -194,9 +194,29 @@ function get_sql_search_clause($search) * @param array search * @return array */ -function get_regular_search_results($search) +function get_regular_search_results($search, $images_where) { + global $conf; + $forbidden = get_sql_condition_FandF( + array + ( + 'forbidden_categories' => 'category_id', + 'visible_categories' => 'category_id', + 'visible_images' => 'id' + ), + "\n AND" + ); + $items = array(); + $tag_items = array(); + + if (isset($search['fields']['tags'])) + { + $tag_items = get_image_ids_for_tags( + $search['fields']['tags']['words'], + $search['fields']['tags']['mode'] + ); + } $search_clause = get_sql_search_clause($search); @@ -204,26 +224,30 @@ function get_regular_search_results($search) { $query = ' SELECT DISTINCT(id) - FROM '.IMAGES_TABLE.' + FROM '.IMAGES_TABLE.' i INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id = ic.image_id - WHERE '.$search_clause.' -;'; + WHERE '.$search_clause; + if (!empty($images_where)) + { + $query .= "\n AND ".$images_where; + } + if (empty($tag_items) or $search['mode']=='AND') + { // directly use forbidden and order by + $query .= $forbidden.' + '.$conf['order_by']; + } $items = array_from_query($query, 'id'); } - if (isset($search['fields']['tags'])) + if ( !empty($tag_items) ) { - $tag_items = get_image_ids_for_tags( - $search['fields']['tags']['words'], - $search['fields']['tags']['mode'] - ); - + $need_permission_check = false; switch ($search['mode']) { case 'AND': - { if (empty($search_clause)) { + $need_permission_check = true; $items = $tag_items; } else @@ -231,17 +255,34 @@ SELECT DISTINCT(id) $items = array_intersect($items, $tag_items); } break; - } case 'OR': - { + $before_count = count($items); $items = array_unique( array_merge( $items, $tag_items ) ); + if ( $before_count < count($items) ) + { + $need_permission_check = true; + } break; + } + if ($need_permission_check and count($items) ) + { + $query = ' +SELECT DISTINCT(id) + FROM '.IMAGES_TABLE.' i + INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id = ic.image_id + WHERE id IN ('.implode(',', $items).') '.$forbidden; + if (!empty($images_where)) + { + $query .= "\n AND ".$images_where; } + $query .= ' + '.$conf['order_by']; + $items = array_from_query($query, 'id'); } } @@ -354,10 +395,9 @@ function get_qsearch_like_clause($q, $field) /** * 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: + * are sorted by relevance unless $super_order_by is true. 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 of matching tags * 'matching_cats' => array of matching categories @@ -365,16 +405,15 @@ function get_qsearch_like_clause($q, $field) * )) * * @param string q + * @param bool super_order_by * @param string images_where optional aditional restriction on images table * @return array */ -function get_quick_search_results($q, $images_where='') +function get_quick_search_results($q, $super_order_by, $images_where='') { - global $page; $search_results = array( 'items' => array(), - 'as_is' => 1, 'qs' => array('q'=>stripslashes($q)), ); $q = trim($q); @@ -518,7 +557,7 @@ SELECT DISTINCT(id) $allowed_images = array_from_query( $query, 'id'); - if ( isset($page['super_order_by']) or empty($by_weights) ) + if ( $super_order_by or empty($by_weights) ) { $search_results['items'] = $allowed_images; return $search_results; @@ -544,17 +583,17 @@ SELECT DISTINCT(id) * @param string images_where optional aditional restriction on images table * @return array */ -function get_search_results($search_id, $images_where='') +function get_search_results($search_id, $super_order_by, $images_where='') { $search = get_search_array($search_id); if ( !isset($search['q']) ) { - $result['items'] = get_regular_search_results($search); + $result['items'] = get_regular_search_results($search, $images_where); return $result; } else { - return get_quick_search_results($search['q'], $images_where); + return get_quick_search_results($search['q'], $super_order_by, $images_where); } } ?> \ No newline at end of file diff --git a/include/section_init.inc.php b/include/section_init.inc.php index d8b2f62c2..053607dfd 100644 --- a/include/section_init.inc.php +++ b/include/section_init.inc.php @@ -332,31 +332,16 @@ SELECT DISTINCT image_id { include_once( PHPWG_ROOT_PATH .'include/functions_search.inc.php' ); - $search_result = get_search_results($page['search']); - if ( !empty($search_result['items']) and !isset($search_result['as_is']) ) - { - $query = ' -SELECT DISTINCT(id) - FROM '.IMAGES_TABLE.' - INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id = ic.image_id - WHERE id IN ('.implode(',', $search_result['items']).') - '.$forbidden.' - '.$conf['order_by'].' -;'; - $page['items'] = array_from_query($query, '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']; - } + $search_result = get_search_results($page['search'], @$page['super_order_by'] ); + if ( isset($search_result['qs']) ) + {//save the details of the query search + $page['qsearch_details'] = $search_result['qs']; } $page = array_merge( $page, array( + 'items' => $search_result['items'], 'title' => '' .l10n('search_result').'', ) @@ -378,7 +363,7 @@ SELECT image_id ( array ( - 'visible_images' => 'image_id' + 'visible_images' => 'id' ), 'AND' ).' diff --git a/include/ws_functions.inc.php b/include/ws_functions.inc.php index f8d242e2c..7e9c8228a 100644 --- a/include/ws_functions.inc.php +++ b/include/ws_functions.inc.php @@ -823,29 +823,32 @@ function ws_images_search($params, &$service) $where_clauses = ws_std_image_sql_filter( $params, 'i.' ); $order_by = ws_std_image_sql_order($params, 'i.'); + $super_order_by = false; if ( !empty($order_by) ) { global $conf; $conf['order_by'] = 'ORDER BY '.$order_by; - $page['super_order_by']=1; // quick_search_result might be faster + $super_order_by=true; // quick_search_result might be faster } $search_result = get_quick_search_results($params['query'], - implode(',', $where_clauses) ); - $image_ids = $search_result['items']; + $super_order_by, + implode(',', $where_clauses) + ); - $image_ids = array_slice($image_ids, - $params['page']*$params['per_page'], - $params['per_page'] ); + $image_ids = array_slice( + $search_result['items'], + $params['page']*$params['per_page'], + $params['per_page'] + ); if ( count($image_ids) ) { $query = ' SELECT * FROM '.IMAGES_TABLE.' - WHERE id IN (' - .wordwrap(implode(', ', $image_ids), 80, "\n") - .')'; + WHERE id IN ('.implode(',', $image_ids).')'; + $image_ids = array_flip($image_ids); $result = pwg_query($query); while ($row = mysql_fetch_assoc($result)) { @@ -862,14 +865,10 @@ SELECT * FROM '.IMAGES_TABLE.' $image[$k] = $row[$k]; } $image = array_merge( $image, ws_std_get_urls($row) ); - array_push($images, $image); + $images[$image_ids[$image['id']]] = $image; } - - $image_ids = array_flip($image_ids); - usort( - $images, - create_function('$i1,$i2', 'global $image_ids; return $image_ids[$i1["id"]]-$image_ids[$i2["id"]];') - ); + ksort($images, SORT_NUMERIC); + $images = array_values($images); } -- cgit v1.2.3