Album views reset in CPG 1.5

[Ludo]

After upgrading to 1.5, I notice that Album views both in "Last updated albums" and "New albums" meta-albums seem to be reset to 0
http://vanrokken.altervista.org/thumbnails.php?album=newalb&cat=0
http://vanrokken.altervista.org/thumbnails.php?album=lastalb&cat=0
while in 1.4 they displayed the same count that's now showed only in "Most viewed albums"
http://vanrokken.altervista.org/thumbnails.php?album=mostviewalb&cat=0
(I'm not sure if this meta album now comes with "More meta albums" plugin or it's still a tweak from mine to that plugin )

I guess the reason is that album views are counted separately from file views since 1.5  only, but for the sake of consistency with the past I'd like to keep the good old file views displayed together with album ones. Is it possibile, hopefully without touching core files?

[Αndré]

I guess the reason is that album views are counted separately from file views since 1.5  only

Correct.

I'd like to keep the good old file views displayed together with album ones. Is it possibile, hopefully without touching core files?

Open include/functions.inc.php, find

Code Select Expand

build_caption($rowset, array('ctime'), 'albums');
and replace with

Code Select Expand

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

That will display the view count of the currently visible file. Not sure how it was displayed in cpg1.4.x. To avoid touching core files, you could also adjust the album view counter in your database (e.g. count all file views and use that value as album hits value).

[Ludo]

Open include/functions.inc.php, find

Code Select Expand

build_caption($rowset, array('ctime'), 'albums');
and replace with

Code Select Expand

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

That will display the view count of the currently visible file.

Thanks for your patience, but this hardly produces any effect, just like the same syntax used in "more meta albums" plugin codebase for the "New albums" meta-album.
It seems only to add a couple of units to albums views, just like it takes in account only visits past 1.5 upgrade.
I'm afraid there's some bug in the queries, file views counter shouldn't exclude from the count visits before upgrade that are regularly stored in DB...

[Αndré]

So what exactly should be displayed as number of album views?

[Ludo]

I'm afraid there's some bug in the queries, file views counter shouldn't exclude from the count visits before upgrade that are regularly stored in DB...

Unfortunately I was right
In a nutshell, the issue is that this query in functions.php ("lastalb" meta-album, line 1860)

Code Select Expand

SELECT r.aid, a.thumb, a.keyword, a.alb_hits, a.title, MAX(ctime) AS ctime
                FROM coppermine_pictures AS r
                INNER JOIN coppermine_albums AS a ON a.aid = r.aid
                WHERE (1)
                AND approved = 'YES'
                GROUP BY r.aid
                ORDER BY ctime DESC
                 LIMIT 0 ,16
can't ever retrieve total album files views, even if we apply the hack you suggested, unless we select them too, e.g.

Code Select Expand

SELECT r.aid, a.thumb, a.keyword, a.alb_hits, SUM(r.hits)+a.alb_hits AS hits, a.title, MAX(ctime) AS ctime
                FROM coppermine_pictures AS r
                INNER JOIN coppermine_albums AS a ON a.aid = r.aid
                WHERE (1)
                AND approved = 'YES'
                GROUP BY r.aid
                ORDER BY ctime DESC
                 LIMIT 0 ,16
This way you can pick album views only or total files views by switching between "albums" and "files" mode when calling build_caption as you suggested above, I believe it'sworth a plugin (maybe tweaking "more meta albums" one).

The same for "newalb" meta album added by that plugin: this query (codebase.php, line 318)

Code Select Expand

SELECT * FROM coppermine_pictures AS p
                INNER JOIN coppermine_albums AS r ON r.aid = p.aid
                WHERE (1)
                AND approved = 'YES'
                AND p.pid = r.thumb
                ORDER BY ctime DESC
                 LIMIT 0 ,16
retrieves only views of the album thumb, to get total album files views we need to tweak it like that:

Code Select Expand

SELECT *, (SUM(p.hits)+SUM(r.alb_hits)) AS hits FROM coppermine_pictures AS p
                INNER JOIN coppermine_albums AS r ON r.aid = p.aid
                WHERE (1)
                AND approved = 'YES'
                GROUP BY p.aid
                ORDER BY ctime DESC
                 LIMIT 0 ,16

[Αndré]

I believe it's worth a plugin (maybe tweaking "more meta albums" one)

I'd be happy to add some more meta albums to my plugin. The 'mostviewalb' meta album seems to be your own tweak, but if I remember correctly there was something like a "more meta albums for cpg1.4.x mod" thread containing (among others) that meta album. Unfortunately I wasn't able to find that thread.

[Ludo]

I didn't remember you were that plugin author   
What about adding my "Most viewed albums" meta-album to it at least? I can post you my code...

[Ludo]

if I remember correctly there was something like a "more meta albums for cpg1.4.x mod" thread containing (among others) that meta album.

It was a mod by me, I don't think someone pluginized it: if he did, i would have installed that plugin instead of keep modding core files

[Αndré]

I thought of adding something like that:
- mostviewalb (which sorts by the album views)
- mostviewalbf (which counts the sum of files views and sorts according to that value)


To have a meta album that uses the sum file views for all 'album' meta album, I suggest to add the following meta albums:
- newalbf
- lastalbf


Please post your code, that saves me some coding effort

It was a mod by me, I don't think someone pluginized it

I'm not sure, but AFAIK additional meta albums for cpg1.4.x were available somewhere in this board. Maybe it was a part of Stramm's modpack. There were a lot of other new meta albums, too.

[Ludo]

Code Select Expand

SELECT *, (SUM(p.hits)+SUM(r.alb_hits)) AS hits FROM coppermine_pictures AS p

Yesterday I made a mistake, it's

Code Select Expand

SELECT *, (SUM(p.hits)+r.alb_hits) AS hits FROM coppermine_pictures AS p

[Ludo]

I thought of adding something like that:
- mostviewalb (which sorts by the album views)
- mostviewalbf (which counts the sum of files views and sorts according to that value)

Smart idea, mine then is 'mostviewalb'

To have a meta album that uses the sum file views for all 'album' meta album, I suggest to add the following meta albums:
- newalbf
- lastalbf

I'm going to, then I will post all the code I'd add
Am I better to post in the plugin topic (if there is one )?

[Ludo]

Smart idea, mine then is 'mostviewalb'

Sorry, 'mostviewalbf'

[Αndré]

Am I better to post in the plugin topic (if there is one )?

The plugin announcement thread can be found here: http://forum.coppermine-gallery.net/index.php/topic,63706.0.html

But as we already discussed (and thus theoretically developed) the additions here, please post it in this thread. When I release the new version, I'll refer to this thread.

[Ludo]

So far I added "mostviewalb", "mostviewalbf" and "newalbf" meta-alnums; I'm not sure I will ever need "lastalbf", so for now I post only those mods I made to codebase.php.

In function mma_album_types, I added

Code Select Expand

    $album_types['albums'][] = 'newalbf';
    $album_types['albums'][] = 'mostviewalb';
    $album_types['albums'][] = 'mostviewalbf';

In function mma_get_pic_pos, after

Code Select Expand

case 'newalb': // New albums
I added

Code Select Expand

        case 'newalbf':
            $query = "SELECT ctime FROM {$CONFIG['TABLE_PICTURES']} WHERE pid = $pid";
            $result = cpg_db_query($query);
            $ctime = mysql_result($result, 0);
            mysql_free_result($result);

            $query = "SELECT COUNT(*) FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND p.pid = r.thumb
                AND ctime > $ctime
                OR ctime = $ctime AND pid < $pid";
            $result = cpg_db_query($query);
            $rowset = cpg_db_fetch_rowset($result);
            mysql_free_result($result);

            build_caption($rowset, array('ctime'));
            break;
           
        case 'mostviewalb': // Most viewed albums
        case 'mostviewalbf':
            $query = "SELECT COUNT(*) FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND pid < $pid
                GROUP BY p.aid";
            $result = cpg_db_query($query);
            $rowset = cpg_db_fetch_rowset($result);
            mysql_free_result($result);

            build_caption($rowset, array('hits'));
            break;


In function mma_meta_album, before

Code Select Expand

case 'mostcom':
I added

Code Select Expand

        case 'newalbf': // New albums (album + files views shown)
            $album_name = cpg_fetch_icon('last_created', 2)." ".$lang_plugin_more_meta_albums['newalb_title'];
            if ($CURRENT_CAT_NAME) {
                $album_name .= " - $CURRENT_CAT_NAME";
            }

            $query = "SELECT pid FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND p.pid = r.thumb";
            $result = cpg_db_query($query);
            $count = mysql_num_rows($result);
            mysql_free_result($result);

            $query = "SELECT *, (SUM(p.hits)+r.alb_hits) AS hits FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                GROUP BY p.aid
                ORDER BY ctime DESC
                {$meta['limit']}";
            $result = cpg_db_query($query);
            $rowset = cpg_db_fetch_rowset($result);
            mysql_free_result($result);

            build_caption($rowset, array('ctime'));
            break;
           
        case 'mostviewalb': // Most viewed albums
            $album_name = cpg_fetch_icon('most_viewed', 2)." ".$lang_plugin_more_meta_albums['mostviewalb_title'];
            if ($CURRENT_CAT_NAME) {
                $album_name .= " - $CURRENT_CAT_NAME";
            }

            $query = "SELECT r.aid FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND r.alb_hits > 0
                GROUP BY p.aid";
            $result = cpg_db_query($query);
            $count = mysql_num_rows($result);
            mysql_free_result($result);

            $query = "SELECT * FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND r.alb_hits > 0
                GROUP BY r.aid
                ORDER BY r.alb_hits DESC
                {$meta['limit']}";
            $result = cpg_db_query($query);
            $rowset = cpg_db_fetch_rowset($result);
            mysql_free_result($result);

            build_caption($rowset, array('alb_hits'), 'albums');
            break;
           
        case 'mostviewalbf': // Most viewed albums
            $album_name = cpg_fetch_icon('most_viewed', 2)." ".$lang_plugin_more_meta_albums['mostviewalbf_title'];
            if ($CURRENT_CAT_NAME) {
                $album_name .= " - $CURRENT_CAT_NAME";
            }

            $query = "SELECT r.aid FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                GROUP BY p.aid";
            $result = cpg_db_query($query);
            $count = mysql_num_rows($result);
            mysql_free_result($result);

            $query = "SELECT *, (SUM(p.hits)+r.alb_hits) AS hits FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND hits > 0
                GROUP BY r.aid
                ORDER BY hits DESC
                {$meta['limit']}";
            $result = cpg_db_query($query);
            $rowset = cpg_db_fetch_rowset($result);
            mysql_free_result($result);

            build_caption($rowset, array('hits'));
            break;


I worked on plugin revision 7997, I don't know if something has changed in plugin structure meantime.

[Αndré]

I worked on plugin revision 7997, I don't know if something has changed in plugin structure meantime.

There have been some updates since that revision: http://coppermine.svn.sourceforge.net/viewvc/coppermine/branches/cpg1.5.x/plugins/more_meta_albums/codebase.php?view=log


Thank you for your contribution. I'll check and add them probably tomorrow.

[Ludo]

Time of the last visit to an album isn't stored in DB, is it? So, it's impossibile to add a "last visited albums" meta album...