- feature 832: Folders with no images sql optimization

- recent pics are sorted by date descending, and only then by conf['order_by']
- updated prototype.js to version 1.6

git-svn-id: http://piwigo.org/svn/trunk@2424 68402e56-0260-453c-a942-63ccdbb3a9ee

2 files changed, 55 insertions, 40 deletions

diff --git a/include/category_cats.inc.php b/include/category_cats.inc.php
index e7f9a168a..96b1aee58 100644
--- a/include/category_cats.inc.php
+++ b/include/category_cats.inc.php
@@ -88,54 +88,60 @@ while ($row = mysql_fetch_assoc($result))
   }
   else if ($conf['allow_random_representative'])
   {// searching a random representant among elements in sub-categories
-    $query = '
+    if ($row['count_images']>0)
+    {
+      $query = '
 SELECT image_id
   FROM '.CATEGORIES_TABLE.' AS c INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic
     ON ic.category_id = c.id';
-    $query.= '
+      $query.= '
   WHERE (c.id='.$row['id'].' OR uppercats LIKE \''.$row['uppercats'].',%\')'
-  .get_sql_condition_FandF
-  (
-    array
-      (
-        'forbidden_categories' => 'c.id',
-        'visible_categories' => 'c.id',
-        'visible_images' => 'image_id'
-      ),
-    "\n  AND"
-  ).'
+    .get_sql_condition_FandF
+    (
+      array
+        (
+          'forbidden_categories' => 'c.id',
+          'visible_categories' => 'c.id',
+          'visible_images' => 'image_id'
+        ),
+      "\n  AND"
+    ).'
   ORDER BY RAND()
   LIMIT 0,1
 ;';
-    $subresult = pwg_query($query);
-    if (mysql_num_rows($subresult) > 0)
-    {
-      list($image_id) = mysql_fetch_row($subresult);
+      $subresult = pwg_query($query);
+      if (mysql_num_rows($subresult) > 0)
+      {
+        list($image_id) = mysql_fetch_row($subresult);
+      }
     }
   }
   else
   { // searching a random representant among representant of sub-categories
-    $query = '
-SELECT representative_picture_id
-  FROM '.CATEGORIES_TABLE.' INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.'
-  ON id = cat_id and user_id = '.$user['id'].'
-  WHERE uppercats LIKE \''.$row['uppercats'].',%\'
-    AND representative_picture_id IS NOT NULL'
-  .get_sql_condition_FandF
-  (
-    array
-      (
-        'visible_categories' => 'id',
-      ),
-    "\n  AND"
-  ).'
-  ORDER BY RAND()
-  LIMIT 0,1
-;';
-    $subresult = pwg_query($query);
-    if (mysql_num_rows($subresult) > 0)
+    if ($row['count_categories']>0 and $row['count_images']>0)
     {
-      list($image_id) = mysql_fetch_row($subresult);
+      $query = '
+  SELECT representative_picture_id
+    FROM '.CATEGORIES_TABLE.' INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.'
+    ON id = cat_id and user_id = '.$user['id'].'
+    WHERE uppercats LIKE \''.$row['uppercats'].',%\'
+      AND representative_picture_id IS NOT NULL'
+    .get_sql_condition_FandF
+    (
+      array
+        (
+          'visible_categories' => 'id',
+        ),
+      "\n  AND"
+    ).'
+    ORDER BY RAND()
+    LIMIT 0,1
+  ;';
+      $subresult = pwg_query($query);
+      if (mysql_num_rows($subresult) > 0)
+      {
+        list($image_id) = mysql_fetch_row($subresult);
+      }
     }
   }
 
diff --git a/include/section_init.inc.php b/include/section_init.inc.php
index ffd6cac1a..305025ff3 100644
--- a/include/section_init.inc.php
+++ b/include/section_init.inc.php
@@ -235,9 +235,9 @@ if ('categories' == $page['section'])
       if ( isset($page['category']) )
       { // get all allowed sub-categories
         $query = '
-SELECT id 
+SELECT id
   FROM '.CATEGORIES_TABLE.'
-  WHERE 
+  WHERE
     uppercats LIKE "'.$page['category']['uppercats'].',%" '
     .get_sql_condition_FandF(
       array
@@ -394,6 +394,15 @@ SELECT image_id
 // +-----------------------------------------------------------------------+
   else if ($page['section'] == 'recent_pics')
   {
+    if ( !isset($page['super_order_by']) )
+    {
+      $conf['order_by'] = str_replace(
+        'ORDER BY ',
+        'ORDER BY date_available DESC,',
+        $conf['order_by']
+        );
+    }
+
     $query = '
 SELECT DISTINCT(id)
   FROM '.IMAGES_TABLE.'
@@ -523,7 +532,7 @@ SELECT id,file
   FROM '.IMAGES_TABLE .'
   WHERE file LIKE "' . $page['image_file'] . '.%" ESCAPE "|"';
     if ( count($page['items']) < 500)
-    {// for very large item sets do not add IN - because slow 
+    {// for very large item sets do not add IN - because slow
       $query .= '
   AND id IN ('.implode(',',$page['items']).')
   LIMIT 0,1';
@@ -533,7 +542,7 @@ SELECT id,file
     {
       case 0: break;
       case 1:
-        list($page['image_id'], $page['image_file']) = mysql_fetch_row($result); 
+        list($page['image_id'], $page['image_file']) = mysql_fetch_row($result);
         break;
       default: // more than 1 file name match
         while ($row = mysql_fetch_row($result) )