Random images originate from only a few albums

[André Müller]

Dear all,

I just realised that by calling random images (e.g. http://www.fotowald.de/thumbnails-random.html fully SFW!) the first few images are always called from just a couple of albums (old ones, added early after installation of my first CPG). How does the random function work? Is there a way to make it "more" random?

Thanks for hints regarding this issue!

Cheers,
André

http://www.fotowald.de/ runing CPG 1.5.24

[Αndré]

We use MySQL's RAND() function to get random images from the database. I just noticed in your gallery that your pictures are sorted by pid ascending and found the corresponding line in the code:

Code (include/functions.inc.php) Select Expand

sort($pidlist);

I don't know why/when it has been changed that way, as (if I remember correctly) it was "real" random at least in cpg1.4.x.

[André Müller]

Hi André

OK, I see! I tried to load one (two) lines of random images along with the breadcrumbs and it looks much more random (still sorted within the set of five or ten images though). So it is mainly a problem when you call a large number of random images (as in the random meta album) but is negligible when used as a preview on the gallery main page.
As I don't really need the random images meta album, but just the subset of one or two rows, I guess this is not a big issue. But good to keep that in mind.

Thanks again,
André

[Αndré]

I just had a closer look at the "random" meta album and it was already coded that way in cpg1.5.1 (alpha). However, it doesn't make sense to me, as it uses three queries to get some random images. Maybe it has been introduced for performance reasons, as there was some (inactive) code in cpg1.4.x which tried to optimize the query:

// if we have more than 1000 pictures, we limit the number of picture returned by the SELECT statement as ORDER BY RAND() is time consuming

As a comparison, I add the cpg1.4.x and cpg1.5.x code without commented out code parts.

Code (cpg1.4.x) Select Expand

        case 'random': // Random pictures
                if ($META_ALBUM_SET && $CURRENT_CAT_NAME) {
                        $album_name = $lang_meta_album_names['random'].' - '. $CURRENT_CAT_NAME;
                } else {
                        $album_name = $lang_meta_album_names['random'];
                }

                $select_columns = '*';

                $limit_random = $limit2 ? 'LIMIT '.$limit2 : '';
                $query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' $META_ALBUM_SET ORDER BY RAND() $limit_random";
                $result = cpg_db_query($query);

                $rowset = array();
                while($row = mysql_fetch_array($result)){
                        $rowset[-$row['pid']] = $row;
                }
                mysql_free_result($result);

                if ($set_caption) build_caption($rowset);

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

                return $rowset;
                break;

Code (cpg1.5.x) Select Expand

    case 'random': // Random files

        if ($cat && $CURRENT_CAT_NAME) {
            $album_name = cpg_fetch_icon('random', 2) . $lang_meta_album_names['random'] . ' - ' . $CURRENT_CAT_NAME;
        } else {
            $album_name = cpg_fetch_icon('random', 2) . $lang_meta_album_names['random'];
        }

        $query = "SELECT COUNT(*)
                FROM {$CONFIG['TABLE_PICTURES']} AS r
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'";

        $result = cpg_db_query($query);

        list($count) = mysql_fetch_row($result);
        mysql_free_result($result);

        $query = "SELECT pid
                FROM {$CONFIG['TABLE_PICTURES']} AS r
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                ORDER BY RAND()
                $limit";

        $result = cpg_db_query($query);

        $pidlist = array();

        while ( ($row = mysql_fetch_assoc($result)) ) {
            $pidlist[] = $row['pid'];
        }
        mysql_free_result($result);

        sort($pidlist);

        $select_columns = implode(', ', $select_column_list);

        $query = "SELECT $select_columns
                FROM {$CONFIG['TABLE_PICTURES']} AS r
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
                WHERE pid IN (" . implode(', ', $pidlist) . ")";

        $rowset = array();

        // Fire the query if at least one pid is in pidlist array
        if (count($pidlist)) {

            $result = cpg_db_query($query);

            while ( ($row = mysql_fetch_assoc($result)) ) {
                $rowset[-$row['pid']] = $row;
            }

            mysql_free_result($result);
        }

        if ($set_caption) {
            build_caption($rowset);
        }

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

        return $rowset;
        break;

I'll try to optimize this.

[Αndré]

I just tested the "random" meta album with a quite large gallery (~ 84k files) and those three queries are definitely added for performance reasons. However, we still can shuffle the output.

Here's my proposal for the "random" meta album. Please replace the whole code block in include/functions.inc.php (see my last post) with the following one and report if it works as expected:

Code Select Expand

    case 'random': // Random files

        if ($cat && $CURRENT_CAT_NAME) {
            $album_name = cpg_fetch_icon('random', 2) . $lang_meta_album_names['random'] . ' - ' . $CURRENT_CAT_NAME;
        } else {
            $album_name = cpg_fetch_icon('random', 2) . $lang_meta_album_names['random'];
        }

        $query = "SELECT COUNT(*)
                FROM {$CONFIG['TABLE_PICTURES']} AS r
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'";

        $result = cpg_db_query($query);

        list($count) = mysql_fetch_row($result);
        mysql_free_result($result);

        $query = "SELECT pid
                FROM {$CONFIG['TABLE_PICTURES']} AS r
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                ORDER BY RAND()
                $limit";

        $result = cpg_db_query($query);

        $pidlist = array();
        while ($row = mysql_fetch_assoc($result)) {
            $pidlist[] = $row['pid'];
        }
        mysql_free_result($result);

        $select_columns = implode(', ', $select_column_list);

        $query = "SELECT $select_columns
                FROM {$CONFIG['TABLE_PICTURES']} AS r
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
                WHERE pid IN (" . implode(', ', $pidlist) . ")";

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

        shuffle($rowset);

        if ($set_caption) {
            build_caption($rowset);
        }

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

        return $rowset;
        break;

[André Müller]

Hi André,

thanks for that. I changed the code and it works as expected I guess. After a couple of refreshes one sees that the meta-album is much more random.
I do not experience any impact on the performance (pretty much instantly with my gallery with < 3000 images).

http://www.fotowald.de/thumbnails-random.html

Schönen Feiertag and kind regards,
André

[Αndré]

Code committed in SVN revision 8615.

I do not experience any impact on the performance

As I haven't touched that parts I just removed some useless code and shuffled the thumbnails.

Schönen Feiertag

Unfortunately today is no holiday in Berlin.

[Αndré]

Added fix for empty "random" meta album in SVN revision 8616.

[E. William]

Awesome mod