Changeset 8726

Timestamp:

Jan 17, 2011, 10:16:42 PM (13 years ago)

Author:

rvelices

Message:

bug 2105 : Browsing tags is slow if tags contains many photos

Location:

trunk/include

Files:

• functions_search.inc.php (modified) (4 diffs)
• functions_tag.inc.php (modified) (1 diff)
• section_init.inc.php (modified) (1 diff)
• ws_functions.inc.php (modified) (3 diffs)

trunk/include/functions_search.inc.php

@@ -232 +232 @@
       $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');
   }
@@ -242 +239 @@
   if ( !empty($tag_items) )
   {
-    $need_permission_check = false;
     switch ($search['mode'])
     {
@@ -248 +244 @@
         if (empty($search_clause))
         {
-          $need_permission_check = true;
           $items = $tag_items;
         }
@@ -264 +259 @@
             )
           );
-        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');
     }
   }

trunk/include/functions_tag.inc.php

@@ -161 +161 @@
  * @param array tag ids
  * @param string mode
- * @return array
- */
-function get_image_ids_for_tags($tag_ids, $mode = 'AND')
-{
-  switch ($mode)
-  {
-    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');
-      }
-
-      // 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');
-    }
-  }
+ * @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', $extra_images_where_sql='', $order_by='')
+{
+  global $conf;
+  if (empty($tag_ids))
+  {
+    return array();
+  }
+
+  $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');
 }
 

trunk/include/section_init.inc.php

@@ -322 +322 @@
     $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,

trunk/include/ws_functions.inc.php

@@ -1559 +1559 @@
 
 
-  $image_ids = 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) );
+
+
+  $image_ids = array_slice($image_ids, (int)($params['per_page']*$params['page']), (int)$params['per_page'] );
+  
   $image_tag_map = array();
-
-  if ( !empty($tag_ids) )
+  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 )
       {
@@ -1665 +1644 @@
       array_push($images, $image);
     }
+    usort($images, 'rank_compare');
+    unset($rank_of);
   }
 
@@ -2545 +2526 @@
   global $template;
   
-  if (!is_admin() || is_adviser() )
+  if (!is_admin())
   {
     return new PwgError(401, 'Access denied');