Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
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
  • Loading branch information
rvelices committed Jan 17, 2011
1 parent b5046a4 commit e123585
Show file tree
Hide file tree
Showing 4 changed files with 67 additions and 132 deletions.
26 changes: 1 addition & 25 deletions include/functions_search.inc.php
Expand Up @@ -231,23 +231,18 @@ function get_regular_search_results($search, $images_where)
{
$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
Expand All @@ -263,27 +258,8 @@ function get_regular_search_results($search, $images_where)
$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;
Expand Down
73 changes: 33 additions & 40 deletions include/functions_tag.inc.php
Expand Up @@ -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');
}

/**
Expand Down
15 changes: 0 additions & 15 deletions include/section_init.inc.php
Expand Up @@ -321,21 +321,6 @@

$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(
Expand Down
85 changes: 33 additions & 52 deletions include/ws_functions.inc.php
Expand Up @@ -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]))
Expand Down Expand Up @@ -1664,6 +1643,8 @@ function ws_tags_getImages($params, &$service)
);
array_push($images, $image);
}
usort($images, 'rank_compare');
unset($rank_of);
}

return array( 'images' =>
Expand Down Expand Up @@ -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');
}
Expand Down

0 comments on commit e123585

Please sign in to comment.