thumbnail in 'latest albums'

[ralfs]

Hello,

What rule does the 'Latest albums' page follow concerning the usage of thumbnail pictures? I haven't found one so far, it seems to me that Coppermine always takes the most improper picture to keep the users confusion level at a maximum    :-\

Or the other way round: isn't it possible to take the same thumbnail as defined in the album properties? If not, does anybody know a mod ?
Don't both 'index.php?cat=xyz' and 'thumbnails.php?album=lastalb' use the 'thumb' field from the 'albums' table in the SQL database?

Thanks for enlighting me,

Ralf

[ralfs]

Hi again,

I am a bit wondering why nobody stumbled over that question so far, but anyway. The key is the sql query and I have solved it with the following change:

In include/functions.inc.php find the section

Code Select Expand

case 'lastalb'
and replace

Code Select Expand


$query = "SELECT *,{$CONFIG['TABLE_ALBUMS']}.title AS title,{$CONFIG['TABLE_ALBUMS']}.aid AS aid FROM {$CONFIG['TABLE_PICTURES']},{$CONFIG['TABLE_ALBUMS']} WHERE {$CONFIG['TABLE_PICTURES']}.aid = {$CONFIG['TABLE_ALBUMS']}.aid AND approved = 'YES' $META_ALBUM_SET GROUP  BY {$CONFIG['TABLE_PICTURES']}.aid ORDER BY {$CONFIG['TABLE_PICTURES']}.ctime DESC $limit";

with

Code Select Expand


$query = "SELECT *,{$CONFIG['TABLE_ALBUMS']}.title AS title,{$CONFIG['TABLE_ALBUMS']}.aid AS aid FROM {$CONFIG['TABLE_PICTURES']},{$CONFIG['TABLE_ALBUMS']} WHERE {$CONFIG['TABLE_ALBUMS']}.thumb = {$CONFIG['TABLE_PICTURES']}.pid AND {$CONFIG['TABLE_PICTURES']}.aid = {$CONFIG['TABLE_ALBUMS']}.aid AND approved = 'YES' $META_ALBUM_SET GROUP  BY {$CONFIG['TABLE_PICTURES']}.aid ORDER BY {$CONFIG['TABLE_PICTURES']}.ctime DESC $limit";


What you get:
1. Always the right (predefined) thumbnails for your albums
2. Only albums shown which have a defined thumbnail.

The second point may be an advantage, because you have another way to remove albums from the 'latest' list
It also may be a disadvantage, because you always have to define an album thumb.

For me it works perfect, perhaps also for you.

Regards,
RalfS

[Sami]

That was known issue
Album thumbnail for "lastalb" meta-album is always the first thumbnail

[ralfs]

That was known issue
Album thumbnail for "lastalb" meta-album is always the first thumbnail

No longer for me 

[Joachim Müller]

For reference: http://forum.coppermine-gallery.net/index.php?topic=27162.0

[Pastinakel]

This issue has been brought up before in the cpg1.3.x Support forum: http://forum.coppermine-gallery.net/index.php?topic=15726.0

I use the solution given there, modified to fit cpg1.4.x:

Add the following code to function display_thumbnails, just before the $pic_title=... line

Code Select Expand


if ($album == 'lastalb'){
    $res = cpg_db_query("SELECT p.* FROM {$CONFIG['TABLE_ALBUMS']} AS a,{$CONFIG['TABLE_PICTURES']} AS p WHERE p.pid = a.thumb AND a.aid = {$row['aid']}");
    if (mysql_num_rows($res)){
        $row2 = mysql_fetch_assoc($res);
        $row = array_merge($row, $row2);
    }
}


That way you get the right thumbnail and the right caption (ie title statement on the image).

Is it possible to use one of the solutions to show the right thumb for an albums in lastalb for the stable version of Coppermine? I think it makes more sense than just displaying the first thumb that MySQL burps up.

[Pastinakel]

There is another possibility: show the thumb from the last picture that was added to the album (the pic that made the album show up in lastalb). This is possible using a subselect and those are supported from MySQL 4.1 onwards. The reqs for Coppermine says MySQL (3.23.23 or better; 4.x recommended); however so this is not always compatible with the MySQL server Coppermine can run with.

The second query for case 'lastalb' in function get_pic_data in include/functions.inc.php changes from

Code Select Expand

$query = "SELECT *,{$CONFIG['TABLE_ALBUMS']}.title AS title,{$CONFIG['TABLE_ALBUMS']}.aid AS aid FROM {$CONFIG['TABLE_PICTURES']},{$CONFIG['TABLE_ALBUMS']} WHERE {$CONFIG['TABLE_PICTURES']}.aid = {$CONFIG['TABLE_ALBUMS']}.aid AND approved = 'YES' $META_ALBUM_SET GROUP BY {$CONFIG['TABLE_PICTURES']}.aid ORDER BY {$CONFIG['TABLE_PICTURES']}.ctime DESC $limit";

to

Code Select Expand

$query = "SELECT *,{$CONFIG['TABLE_ALBUMS']}.title AS title,{$CONFIG['TABLE_ALBUMS']}.aid AS aid FROM {$CONFIG['TABLE_PICTURES']} AS tp1,{$CONFIG['TABLE_ALBUMS']} WHERE tp1.aid = {$CONFIG['TABLE_ALBUMS']}.aid AND approved = 'YES' AND ctime=(SELECT MAX(tp2.ctime) FROM {$CONFIG['TABLE_PICTURES']} AS tp2 WHERE tp1.aid = tp2.aid) $META_ALBUM_SET GROUP BY tp1.aid $limit";

The other solution is to use a second query again (see post above) to show the thumb of the last added picture for that album:

Add the following code to function display_thumbnails, just before the $pic_title=... line in include/functions.inc.php

Code Select Expand


if ($album == 'lastalb'){
    $res = cpg_db_query("SELECT * FROM {$CONFIG['TABLE_PICTURES']} WHERE aid = {$row['aid']} ORDER BY ctime DESC LIMIT 1");
    if (mysql_num_rows($res)){
        $row2 = mysql_fetch_assoc($res);
        $row = array_merge($row, $row2);
    }
}


I tested both on the stable SVN version checked out today.

[Paver]

The subselect mentioned by Pastinakel in the first solution was attempted by me in this thread: http://forum.coppermine-gallery.net/index.php?topic=31857.0

It caused a serious stability issue with large galleries that caused a one-day lifetime for version 1.4.7.  (Version 1.4.8 was released a day later to reverse this subselect query "fix".)

I haven't looked into why this happened.  Please note this possible problem before applying the first solution above and test thoroughly.  Report any positive or negative results here.

[Pastinakel]

The subselect mentioned by Pastinakel in the first solution was attempted by me in this thread: http://forum.coppermine-gallery.net/index.php?topic=31857.0

It caused a serious stability issue with large galleries that caused a one-day lifetime for version 1.4.7.  (Version 1.4.8 was released a day later to reverse this subselect query "fix".)

I haven't looked into why this happened.  Please note this possible problem before applying the first solution above and test thoroughly.  Report any positive or negative results here.

Can you give me an indication of what you mean by a "large gallery"? How many pictures do I have to add to my test gallery before I can try to bring it to it's knees with evil subqueries? At this moment I have stuffed my test gallery with about 200 pictures and the query I'm using now takes about 0,020 sec max (testing in PHPMyAdmin).

I will post that new query and test results later here.

[Paver]

It worked fine on my testbed which had a handful of images.  Then I applied it to my main gallery which has nearly 7000 photos in 225 albums.  This caused MySQL to hang up (reproducibly).  In my case, this was MySQL 4.1 on a Windows server.  This bug report indicates a problem on a Linux server as well.  I really haven't done any more testing besides seeing that the new query reliably caused this hang-up.

The subquery seemed innocent enough.  (Note that I didn't look carefully to see if your subquery is identical to mine.)  I can imagine it taking a little time to do the full query but hanging up was unexpected.

If you want to quickly add a lot of photos to your Coppermine gallery, you can use the plugin MassImport.

[Pastinakel]

Edit: the query I show here is not the right one (it does not sort out the right albums). I have no time now to look up the right one but I will post it here shortly.

Last weekend I came up with a new approach: try to let the MySQL server come up with the right thumnail for the lastalb view. The definition for "right thumbnail for lastalb view" is in my opinion:

- If there is a thumb defined for that album: that's the right thumb
- If not: the thumb of the picture that was last added to the album is the right thumb.

The code I use to accomplish this is as follows:

Code Select Expand


case 'lastalb': // Last albums to which uploads
if ($META_ALBUM_SET && $CURRENT_CAT_NAME) {
$album_name = $lang_meta_album_names['lastalb'].' - '. $CURRENT_CAT_NAME;
} else {
$album_name = $lang_meta_album_names['lastalb'];
}


$META_ALBUM_SET = str_replace( "aid", $CONFIG['TABLE_PICTURES'].".aid" , $META_ALBUM_SET );

$query = "SELECT a.*, p.*,a.title AS title, lctime as ctime
FROM {$CONFIG['TABLE_ALBUMS']} AS a
JOIN (
SELECT aid AS laid, MAX(`ctime`) AS lctime, approved
FROM {$CONFIG['TABLE_PICTURES']}
WHERE approved = 'yes' $META_ALBUM_SET
GROUP BY aid
$limit
) as l
ON l.laid = a.aid
LEFT OUTER JOIN {$CONFIG['TABLE_PICTURES']} AS p
ON IF(a.thumb,p.pid = a.thumb,p.ctime = lctime)
ORDER BY lctime DESC
$limit";

$result = cpg_db_query($query);
$count = mysql_num_rows($result);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);

if ($set_caption) build_caption($rowset,array('ctime'));

$rowset = CPGPluginAPI::filter('thumb_caption_lastalb',$rowset);

return $rowset;
break;


As you see: an inner join on a derived table (subquery in FROM clause) and a conditional outer join. See for derived tables http://dev.mysql.com/doc/refman/4.1/en/unnamed-views.html The reason I have chosen for a derived table is that I read somewhere this derived table is build once for the total query, not for every record (I am no MySQL guru so I'm not completely sure about that).

One other thing: I do not know where the $count var is for so I don't know if I'm assigning the right value in my code.

So far for the code. The results:

I have stuffed my test gallery with 1631 files and 25 albums. I could not find any more pictures so I started adding duplicates.

The test: under full server stress (apache is processing another heavy PHP function) 3 queries are run in sequence. The (micro)time it takes to execute the query and getting the values in PHP is recorded and after repeating those 3 queries a hundred times a mean query time is calculated.

Query 0 is the original query used for filling the $count value:

SELECT count(cpg148_albums.aid) FROM cpg148_pictures,cpg148_albums WHERE cpg148_pictures.aid = cpg148_albums.aid AND approved = 'YES' GROUP BY cpg148_pictures.aid

Query 1 is the original query used for filling the $result array:

SELECT *,cpg148_albums.title AS title,cpg148_albums.aid AS aid FROM cpg148_pictures,cpg148_albums WHERE cpg148_pictures.aid = cpg148_albums.aid AND approved = 'YES' GROUP BY cpg148_pictures.aid ORDER BY cpg148_pictures.ctime DESC LIMIT 0 ,8

Query 2 is my above mentioned query:

SELECT a.*, p.*,a.title AS title, lctime as ctime FROM cpg148_albums AS a JOIN ( SELECT aid AS laid, MAX(`ctime`) AS lctime, approved FROM cpg148_pictures WHERE approved = 'yes' GROUP BY aid LIMIT 0 ,8 ) as l ON l.laid = a.aid LEFT OUTER JOIN cpg148_pictures AS p ON IF(a.thumb,p.pid = a.thumb,p.ctime = lctime) ORDER BY lctime DESC LIMIT 0 ,8

These are the results under stress:
Query 0 mean execution time: 0.019844 sec
Query 1 mean execution time: 0.074887 sec
Query 2 mean execution time: 0.131075 sec

These are the results under no stress at all:
Query 0 mean execution time: 0.003916 sec
Query 1 mean execution time: 0.026260 sec
Query 2 mean execution time: 0.051477 sec

As you see: my code roughly doubles the time the query takes, but hey: that's an extray query in the derived table <b>and</b> an extra join. And, most important: the right thumbnail 

I'm looking forward to seeing results from people that can run that query 2 (the one I came up with) an a really huge gallery. You can use PHPMyAdmin for that, you don't have to mess with CP's code (like I did).

...And I'm also really looking forward to hearing from you what you think about this idea.

[Pastinakel]

Bummer. After pondering about this lastalb stuff for almost two weeks I must admit that what the devs have come up with is the best you can do with this query. There are queries that do exactly what I wanted to achieve in the previous post but those queries take about 3 secs and that's way too long.

Of course you can query every thumb in the lastalb array for the right thumb but you'll have to query every thumb seperately. Not an option for big galleries.

[Paver]

On thing to consider is that this might be the perfect place for a static cache table.  This has been discussed for other stats with not much code actually written (as far as I can remember).

A 3-second query would be acceptable for an hourly cache run or for a forced update (after an addition or number of additions).

[Pastinakel]

Sorry, this lastalb stuff keeps haunting me 

This is what I have now:

functions.inc.php line 1277: add a mod for the right album thumb

// Mod Start: lastalbum: show the predifined thumb or last updated image as thumb
//            $query = "SELECT *,{$CONFIG['TABLE_ALBUMS']}.title AS title,{$CONFIG['TABLE_ALBUMS']}.aid AS aid FROM {$CONFIG['TABLE_PICTURES']},{$CONFIG['TABLE_ALBUMS']} WHERE {$CONFIG['TABLE_PICTURES']}.aid = {$CONFIG['TABLE_ALBUMS']}.aid AND approved = 'YES' $META_ALBUM_SET GROUP BY {$CONFIG['TABLE_PICTURES']}.aid ORDER BY {$CONFIG['TABLE_PICTURES']}.ctime DESC $limit";

                $QUERY_META_ALBUM_SET = str_replace( $CONFIG['TABLE_PICTURES'], "p1" , $META_ALBUM_SET );
            $query = "SELECT p1.pid, p1.aid, p1.filepath, p1.filename, p1.filesize, p1.total_filesize, p1.pwidth,
      p1.pheight, p1.hits, p1.mtime, p2.ctime, p1.owner_id, p1.owner_name, p1.pic_rating, p1.votes, p1.keywords,
      p1.approved, p1.galleryicon, p1.user1, p1.user2, p1.user3, p1.user4, p1.url_prefix, p1.pic_raw_ip,
      p1.pic_hdr_ip, p1.lasthit_ip, p1.position, a1.*
FROM {$CONFIG['TABLE_PICTURES']} AS p1,
  (SELECT aid, MAX(ctime) AS ctime FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' GROUP BY aid
    ORDER BY ctime DESC) AS p2,
  {$CONFIG['TABLE_ALBUMS']} AS a1
WHERE p2.aid = p1.aid AND p1.approved = 'YES' AND p1.aid = a1.aid
  AND IF(a1.thumb,p1.pid = a1.thumb,p1.ctime = p2.ctime)
  $QUERY_META_ALBUM_SET
GROUP BY p1.aid
ORDER BY ctime DESC $limit;";
// Mod End: lastalbum: show the predifined thumb or last updated image as thumb

This query takes about 0.0135 sec on my test install with 25 albums and 1754 pictures.

The second line in the code block above is the original query, you can leave it out if you want (but a backup of the original functions.inc.php would be a smart idea).

On more thing: the original query leaves out albums that have more than one pictures with the highest ctime. This mod query includes those albums too.

Your comments please....

EDIT 8-21-2006: removed $limit from temporary table:

    ORDER BY ctime DESC $limit) AS p2,
corrected to:
    ORDER BY ctime DESC) AS p2,

[Pastinakel]

Come on people, no one is interested in running this query on his / her test server?

I think it is a great improvement to have sensible thumbnails in lastalb.

(sorry about this kick but I am very eager to hear some feedback in this) 

[treret]

Pastinakel

where exactly put the code in functions.inc.php after line 1277, after what code?

Do you have tested or implemented it?

Thanks