aboutsummaryrefslogtreecommitdiffstats
path: root/include
diff options
context:
space:
mode:
authorrvelices <rv-github@modusoptimus.com>2011-01-17 21:16:42 +0000
committerrvelices <rv-github@modusoptimus.com>2011-01-17 21:16:42 +0000
commite123585dde33f8ec3be13e788eea6fc10041920f (patch)
treea6857b12bdc8e83c060c5166ebf07fe932f8c745 /include
parentb5046a4f949435ffbf068884f97cce300bf5ee90 (diff)
bug 2105 : Browsing tags is slow if tags contains many photos
git-svn-id: http://piwigo.org/svn/trunk@8726 68402e56-0260-453c-a942-63ccdbb3a9ee
Diffstat (limited to 'include')
-rw-r--r--include/functions_search.inc.php26
-rw-r--r--include/functions_tag.inc.php73
-rw-r--r--include/section_init.inc.php15
-rw-r--r--include/ws_functions.inc.php85
4 files changed, 67 insertions, 132 deletions
diff --git a/include/functions_search.inc.php b/include/functions_search.inc.php
index a8b005517..08cd675a2 100644
--- a/include/functions_search.inc.php
+++ b/include/functions_search.inc.php
@@ -231,23 +231,18 @@ SELECT DISTINCT(id)
{
$query .= "\n AND ".$images_where;
}
- if (empty($tag_items) or $search['mode']=='AND')
- { // directly use forbidden and order by
- $query .= $forbidden.'
+ $query .= $forbidden.'
'.$conf['order_by'];
- }
$items = array_from_query($query, 'id');
}
if ( !empty($tag_items) )
{
- $need_permission_check = false;
switch ($search['mode'])
{
case 'AND':
if (empty($search_clause))
{
- $need_permission_check = true;
$items = $tag_items;
}
else
@@ -263,27 +258,8 @@ SELECT DISTINCT(id)
$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');
- }
}
return $items;
diff --git a/include/functions_tag.inc.php b/include/functions_tag.inc.php
index 0f5859333..4228219d4 100644
--- a/include/functions_tag.inc.php
+++ b/include/functions_tag.inc.php
@@ -160,52 +160,45 @@ function add_level_to_tags($tags)
*
* @param array tag ids
* @param string mode
+ * @param string extra_images_where_sql - optionally apply a sql where filter to retrieved images
+ * @param string order_by - optionally overwrite default photo order
* @return array
*/
-function get_image_ids_for_tags($tag_ids, $mode = 'AND')
+function get_image_ids_for_tags($tag_ids, $mode='AND', $extra_images_where_sql='', $order_by='')
{
- switch ($mode)
+ global $conf;
+ if (empty($tag_ids))
{
- case 'AND':
- {
- // strategy is to list images associated to each tag
- $tag_images = array();
-
- foreach ($tag_ids as $tag_id)
- {
- $query = '
-SELECT image_id
- FROM '.IMAGE_TAG_TABLE.'
- WHERE tag_id = '.$tag_id.'
-;';
- $tag_images[$tag_id] = array_from_query($query, 'image_id');
- }
+ return array();
+ }
- // then we calculate the intersection, the images that are associated to
- // every tags
- $items = array_shift($tag_images);
- foreach ($tag_images as $images)
- {
- $items = array_intersect($items, $images);
- }
- return $items;
- break;
- }
- case 'OR':
- {
- $query = '
-SELECT DISTINCT image_id
- FROM '.IMAGE_TAG_TABLE.'
- WHERE tag_id IN ('.implode(',', $tag_ids).')
-;';
- return array_from_query($query, 'image_id');
- break;
- }
- default:
- {
- die('get_image_ids_for_tags: unknown mode, only AND & OR are supported');
- }
+ $query = 'SELECT id
+ FROM '.IMAGES_TABLE.' i
+ INNER JOIN '.IMAGE_CATEGORY_TABLE.' ic ON id=ic.image_id
+ INNER JOIN '.IMAGE_TAG_TABLE.' it ON id=it.image_id
+ WHERE tag_id IN ('.implode(',', $tag_ids).')'
+ .get_sql_condition_FandF
+ (
+ array
+ (
+ 'forbidden_categories' => 'category_id',
+ 'visible_categories' => 'category_id',
+ 'visible_images' => 'id'
+ ),
+ "\n AND"
+ )
+ .(empty($extra_images_where_sql) ? '' : " \nAND (".$extra_images_where_sql.')')
+ .'
+ GROUP BY id';
+
+ if ($mode=='AND' and count($tag_ids)>1)
+ {
+ $query .= '
+ HAVING COUNT(DISTINCT tag_id)='.count($tag_ids);
}
+ $query .= "\n".(empty($order_by) ? $conf['order_by'] : $order_by);
+
+ return array_from_query($query, 'id');
}
/**
diff --git a/include/section_init.inc.php b/include/section_init.inc.php
index 02b1f83ab..7f0cbf96d 100644
--- a/include/section_init.inc.php
+++ b/include/section_init.inc.php
@@ -321,21 +321,6 @@ else
$items = get_image_ids_for_tags($page['tag_ids']);
- // permissions depends on category, so to only keep images that are
- // reachable to the connected user, we need to check category
- // associations
- if (!empty($items) )
- {
- $query = '
-SELECT DISTINCT image_id
- FROM '.IMAGE_CATEGORY_TABLE.' INNER JOIN '.IMAGES_TABLE.' ON image_id=id
- WHERE image_id IN ('.implode(',', $items).')
- '.$forbidden.
- $conf['order_by'].'
-;';
- $items = array_from_query($query, 'image_id');
- }
-
$page = array_merge(
$page,
array(
diff --git a/include/ws_functions.inc.php b/include/ws_functions.inc.php
index 56194f32a..96f59682c 100644
--- a/include/ws_functions.inc.php
+++ b/include/ws_functions.inc.php
@@ -1558,69 +1558,48 @@ function ws_tags_getImages($params, &$service)
$tag_ids = array_keys($tags_by_id);
- $image_ids = array();
- $image_tag_map = array();
+ $where_clauses = ws_std_image_sql_filter($params);
+ if (!empty($where_clauses))
+ {
+ $where_clauses = implode( ' AND ', $where_clauses);
+ }
+ $image_ids = get_image_ids_for_tags(
+ $tag_ids,
+ $params['tag_mode_and'] ? 'AND' : 'OR',
+ $where_clauses,
+ ws_std_image_sql_order($params) );
- if ( !empty($tag_ids) )
+
+ $image_ids = array_slice($image_ids, (int)($params['per_page']*$params['page']), (int)$params['per_page'] );
+
+ $image_tag_map = array();
+ if ( !empty($image_ids) and !$params['tag_mode_and'] )
{ // build list of image ids with associated tags per image
- if ($params['tag_mode_and'])
- {
- $image_ids = get_image_ids_for_tags( $tag_ids );
- }
- else
- {
- $query = '
+ $query = '
SELECT image_id, GROUP_CONCAT(tag_id) AS tag_ids
FROM '.IMAGE_TAG_TABLE.'
- WHERE tag_id IN ('.implode(',',$tag_ids).')
+ WHERE tag_id IN ('.implode(',',$tag_ids).') AND image_id IN ('.implode(',',$image_ids).')
GROUP BY image_id';
- $result = pwg_query($query);
- while ( $row=pwg_db_fetch_assoc($result) )
- {
- $row['image_id'] = (int)$row['image_id'];
- array_push( $image_ids, $row['image_id'] );
- $image_tag_map[ $row['image_id'] ] = explode(',', $row['tag_ids']);
- }
+ $result = pwg_query($query);
+ while ( $row=pwg_db_fetch_assoc($result) )
+ {
+ $row['image_id'] = (int)$row['image_id'];
+ array_push( $image_ids, $row['image_id'] );
+ $image_tag_map[ $row['image_id'] ] = explode(',', $row['tag_ids']);
}
}
$images = array();
- if ( !empty($image_ids))
- {
- $where_clauses = ws_std_image_sql_filter($params);
- $where_clauses[] = get_sql_condition_FandF(
- array
- (
- 'forbidden_categories' => 'category_id',
- 'visible_categories' => 'category_id',
- 'visible_images' => 'i.id'
- ),
- '', true
- );
- $where_clauses[] = 'id IN ('.implode(',',$image_ids).')';
-
- $order_by = ws_std_image_sql_order($params);
- if (empty($order_by))
- {
- $order_by = $conf['order_by'];
- }
- else
- {
- $order_by = 'ORDER BY '.$order_by;
- }
-
- $query = '
-SELECT DISTINCT i.* FROM '.IMAGES_TABLE.' i
- INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON i.id=image_id
- WHERE '. implode('
- AND ', $where_clauses).'
-'.$order_by.'
-LIMIT '.(int)$params['per_page'].' OFFSET '.(int)($params['per_page']*$params['page']);
-
- $result = pwg_query($query);
+ if (!empty($image_ids))
+ {
+ $rank_of = array_flip($image_ids);
+ $result = pwg_query('
+SELECT * FROM '.IMAGES_TABLE.'
+ WHERE id IN ('.implode(',',$image_ids).')');
while ($row = pwg_db_fetch_assoc($result))
{
$image = array();
+ $image['rank'] = $rank_of[ $row['id'] ];
foreach ( array('id', 'width', 'height', 'hit') as $k )
{
if (isset($row[$k]))
@@ -1664,6 +1643,8 @@ LIMIT '.(int)$params['per_page'].' OFFSET '.(int)($params['per_page']*$params['p
);
array_push($images, $image);
}
+ usort($images, 'rank_compare');
+ unset($rank_of);
}
return array( 'images' =>
@@ -2544,7 +2525,7 @@ function ws_themes_performAction($params, &$service)
{
global $template;
- if (!is_admin() || is_adviser() )
+ if (!is_admin())
{
return new PwgError(401, 'Access denied');
}