Grouping comments for the same pic in lastcom

[Pastinakel]

The idea

Instead of showing each and every comment in the lastcom view we group them under the pic they belong to and we show only the last comment for that pic. So instead of this list:

pic_a comment_1 date_now-1
pic_a comment_2 date_now-2
pic_b comment_3 date_now-3
pic_c comment_4 date_now-4
pic_b comment_5 date_now-5
pic_a comment_6 date_now-6

we get this list:

pic_a comment_1 date_now-1
pic_b comment_3 date_now-3
pic_c comment_4 date_now-4

This concept was inspired by the fact that some distinct newly uploaded pictures get a lot of new comments so the lastcom view will show a lot of comments on only one or two pics.

This issue has been brought up before at least once: http://forum.coppermine-gallery.net/index.php?topic=12263.0

The mod

Version: cpg148.
Note: this mod uses a query with a subquery (a derived table actually) so you need at least MySQL 4.1 to use it!!

functions.inc.php line 983: add a mod to aggregate comments on the same pic

Code Select Expand


// Mod start: group comments on the same pic
/*
$query = "SELECT COUNT(*) from {$CONFIG['TABLE_COMMENTS']}, {$CONFIG['TABLE_PICTURES']}  WHERE approved = 'YES' AND {$CONFIG['TABLE_COMMENTS']}.pid = {$CONFIG['TABLE_PICTURES']}.pid $TMP_SET $keyword)";
                $result = cpg_db_query($query);
                $nbEnr = mysql_fetch_array($result);
                $count = $nbEnr[0];
                mysql_free_result($result);

                $select_columns = '*'; //allows building any data into any thumbnail caption
                if($select_columns == '*'){
                  $select_columns = 'p.*, msg_id, author_id, msg_author, UNIX_TIMESTAMP(msg_date) as msg_date, msg_body, aid';
                } else {
                  $select_columns = str_replace('pid', 'c.pid', $select_columns).', msg_id, author_id, msg_author, UNIX_TIMESTAMP(msg_date) as msg_date, msg_body, aid';
                }

                $TMP_SET = str_replace($CONFIG['TABLE_PICTURES'],'p',$TMP_SET);
                $query = "SELECT $select_columns FROM {$CONFIG['TABLE_COMMENTS']} as c, {$CONFIG['TABLE_PICTURES']} as p WHERE approved = 'YES' AND c.pid = p.pid $TMP_SET $keyword) ORDER by msg_id DESC $limit";
                $result = cpg_db_query($query);
*/

                $QUERY_META_ALBUM_SET = str_replace( $CONFIG['TABLE_PICTURES'], "p" , $TMP_SET );

$query = "SELECT COUNT(DISTINCT(c.pid)) FROM `cpg148_comments` AS c, `cpg148_pictures` AS p
WHERE p.approved = 'YES' AND c.pid = p.pid $QUERY_META_ALBUM_SET $keyword);";

                $result = cpg_db_query($query);
                $nbEnr = mysql_fetch_array($result);
                $count = $nbEnr[0];
                mysql_free_result($result);


                $query = "SELECT p.*, c1.msg_id, c1.author_id, c1.msg_author, c2.msg_date, c1.msg_body, c1.pid
FROM {$CONFIG['TABLE_PICTURES']} as p,
  {$CONFIG['TABLE_COMMENTS']} as c1,
  (SELECT pid, MAX(UNIX_TIMESTAMP(msg_date)) as msg_date
    FROM {$CONFIG['TABLE_COMMENTS']}
    GROUP BY pid
    ORDER BY msg_date DESC) AS c2
WHERE UNIX_TIMESTAMP(c1.msg_date) = c2.msg_date
  AND p.approved = 'YES' AND c1.pid = p.pid
  $QUERY_META_ALBUM_SET $keyword)
GROUP BY p.pid
ORDER BY msg_date DESC
$limit;";
$result = cpg_db_query($query);

// Mod end: group comments on the same pic


The results

The first query (for the $count) takes about 0.0005 secs.
The second query (for the actual comment array) takes about 0.0030 secs.

It works great for me. I have not found any problems yet (but that does not say that much ) Please tell me if you have any seconds thoughts / problems / rants.... 

[Nibbler]

Thankyou for your contribution. It is not efficient enough to be used on large galleries, so only apply this mod on smaller galleries.

I have modifed your code to remove the hardcoded table names.

[Pastinakel]

Thankyou for your contribution. It is not efficient enough to be used on large galleries, so only apply this mod on smaller galleries.

I have modifed your code to remove the hardcoded table names.

Sorry about the hardcoded table names. A bit messy. 

Do you have any results on how this query performs on a huge gallery? This far I found that this query is about 4 times slower than the original (0,004 instead of 0,001 secs) but those numbers are so small that a sensible comparison is not sensible. I mean: (0,0044 / 0,0005 =) 8,8 and (0,0035 / 0,0014 =) 2,5 is a big difference.

Also note that there are a number of queries that are executed for a certain page (my coppermine frontpage involves 31 queries in 0.016 seconds) so one should consider the relative contribution of this query to the total page creation time. But, yes: this query is one of the slowest and the contribution to the total page creation time can be bigger if the execution increases further in huge galleries.

[Nibbler]

I tried your query instead of the method I currently use. I can't give you any numbers on it though; the query was taking so long I had to kill it so it never actually ran to completion. The gallery has 69K pics and 473K comments.

[Master of Orion]

Can You post the latest code? For 1.4.9. ?

[Master of Orion]

I have SQL 4.01 ... about this so not working.

This feature is very useful. Is Like forum.
Please write this code for another version SQL

[interarte]

Code Select Expand

SELECT cpg148_comments.* FROM cpg148_comments
LEFT JOIN cpg148_comments t2
ON (t2.pid = cpg148_comments.pid
AND t2.msg_date > cpg148_comments.msg_date)
WHERE t2.pid is null
ORDER BY msg_date DESC

watch: http://interarte.org/?content=comments
comments are grouped to pid in chronological order
no problem with too slow database query

[Master of Orion]

Can You post the latest code?

[François Keller]

Can You post the latest code?

what do you mean with latest ?

[dwo]

Hi.

I have the latest coppermine installed, SQL is >4.1, but I still get error message: "There was an error while processing a database query"

My functions.php looks like this:

Code Select Expand


                // Replacing the AND in ALBUM_SET with AND (
                if($META_ALBUM_SET){
                        $TMP_SET = "AND (" . substr($META_ALBUM_SET, 3);
                }else{
                        $TMP_SET = "AND (1";
                }

//mod comments


                $query_meta_album_set = str_replace( $CONFIG['TABLE_PICTURES'], "p" , $TMP_SET );

$query = "SELECT COUNT(DISTINCT(c.pid)) FROM `cpg148_comments` AS c, `cpg148_pictures` AS p
where p.approved = 'YES' and c.pid = p.pid $query_meta_album_set $keyword);";

                $result = cpg_db_query($query);
                $nbEnr = mysql_fetch_array($result);
                $count = $nbEnr[0];
                mysql_free_result($result);


                $query = "SELECT p.*, c1.msg_id, c1.author_id, c1.msg_author, c2.msg_date, c1.msg_body, c1.pid
from {$CONFIG['TABLE_PICTURES']} as p,
  {$CONFIG['TABLE_COMMENTS']} as c1,
  (SELECT pid, MAX(UNIX_TIMESTAMP(msg_date)) as msg_date
    from {$CONFIG['TABLE_COMMENTS']}
    group by pid
    order by msg_date DESC) AS c2
where unix_timestamp(c1.msg_date) = c2.msg_date
  and p.approved = 'YES' and c1.pid = p.pid
  $query_meta_album_set $keyword)
group by p.pid
order by msg_date DESC
$limit;";
$result = cpg_db_query($query);


//end mod comments


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

                if ($set_caption) build_caption($rowset,array('msg_body','msg_date'));

 

Is the code o.k.? Where else could be the error?

Thank you very much, Dietmar

[Nibbler]

Enable debug mode in config to get a better error message.

[dwo]

Hello.

Thank you so much.

Due to the error message I had a look in my database and then changed the code to

Code Select Expand

`cpg14x_comments` AS c, `cpg14x_pictures`

I exchanged the 8 with an x as my tables are called this way in the database.

regards, Dietmar

[dwo]

Now, I used the code of interarte, it works aswell. Thank you :-)

Code Select Expand

$query_meta_album_set = str_replace( $CONFIG['TABLE_PICTURES'], "p" , $TMP_SET );

$query = "SELECT cpg14x_comments.* FROM cpg14x_comments
LEFT JOIN cpg14x_comments t2
ON (t2.pid = cpg14x_comments.pid
AND t2.msg_date > cpg14x_comments.msg_date)
WHERE t2.pid is null
ORDER BY msg_date DESC;";

Please take care of my post above and change x to 8 or whatever your database uses.

[jerx]

Can anyone please give more information on the code of interarte? Where do you place the code and which original code do you need to replace?

[SaWey]

New version to group comments:

in functions.inc.php at around line 1000 replace with this

Code Select Expand


case 'lastcom': // Last comments
if ($META_ALBUM_SET && $CURRENT_CAT_NAME) {
                        $album_name = $album_name = $lang_meta_album_names['lastcom'].' - '. $CURRENT_CAT_NAME;
                } else {
                        $album_name = $lang_meta_album_names['lastcom'];
                }

                // Replacing the AND in ALBUM_SET with AND (
                if($META_ALBUM_SET){
                        $TMP_SET = "AND (" . substr($META_ALBUM_SET, 3);
                }else{
                        $TMP_SET = "AND (1";
                }

//mod comments
                $query_meta_album_set = str_replace( $CONFIG['TABLE_PICTURES'], "p" , $TMP_SET );

$query = "SELECT COUNT(DISTINCT(c.pid)) FROM `{$CONFIG['TABLE_COMMENTS']}` AS c, `{$CONFIG['TABLE_PICTURES']}` AS p
where p.approved = 'YES' and c.pid = p.pid $query_meta_album_set $keyword);";

                $result = cpg_db_query($query);
                $nbEnr = mysql_fetch_array($result);
                $count = $nbEnr[0];
                mysql_free_result($result);


                $query = "SELECT t1.*, p.* FROM {$CONFIG['TABLE_COMMENTS']} AS t1
LEFT JOIN {$CONFIG['TABLE_COMMENTS']} AS t2 ON (t2.pid = t1.pid AND t2.msg_date > t1.msg_date)
INNER JOIN {$CONFIG['TABLE_PICTURES']} AS p ON t1.pid = p.pid
WHERE t2.pid is null
ORDER BY msg_date DESC {$limit};";
$result = cpg_db_query($query);

                ///end mod last comments


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

                if ($set_caption) build_caption($rowset,array('msg_body','msg_date'));

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

                return $rowset;
                break;


[Criss]

New version to group comments:

in functions.inc.php at around line 1000 replace with this

Code Select Expand


case 'lastcom': // Last comments
if ($META_ALBUM_SET && $CURRENT_CAT_NAME) {
                        $album_name = $album_name = $lang_meta_album_names['lastcom'].' - '. $CURRENT_CAT_NAME;
                } else {
                        $album_name = $lang_meta_album_names['lastcom'];
                }

                // Replacing the AND in ALBUM_SET with AND (
                if($META_ALBUM_SET){
                        $TMP_SET = "AND (" . substr($META_ALBUM_SET, 3);
                }else{
                        $TMP_SET = "AND (1";
                }

//mod comments
                $query_meta_album_set = str_replace( $CONFIG['TABLE_PICTURES'], "p" , $TMP_SET );

$query = "SELECT COUNT(DISTINCT(c.pid)) FROM `{$CONFIG['TABLE_COMMENTS']}` AS c, `{$CONFIG['TABLE_PICTURES']}` AS p
where p.approved = 'YES' and c.pid = p.pid $query_meta_album_set $keyword);";

                $result = cpg_db_query($query);
                $nbEnr = mysql_fetch_array($result);
                $count = $nbEnr[0];
                mysql_free_result($result);


                $query = "SELECT t1.*, p.* FROM {$CONFIG['TABLE_COMMENTS']} AS t1
LEFT JOIN {$CONFIG['TABLE_COMMENTS']} AS t2 ON (t2.pid = t1.pid AND t2.msg_date > t1.msg_date)
INNER JOIN {$CONFIG['TABLE_PICTURES']} AS p ON t1.pid = p.pid
WHERE t2.pid is null
ORDER BY msg_date DESC {$limit};";
$result = cpg_db_query($query);

                ///end mod last comments


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

                if ($set_caption) build_caption($rowset,array('msg_body','msg_date'));

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

                return $rowset;
                break;


I applied the mod but every comment have the same date now: 12/31/69 at 19:33

How can I fix it? I'm using 1.4.20

[Fabricio Ferrero]

Does anybody has a CPG to see a live demo? Please..

Thanks,

[dwo]

SaWey did a great job for us. Again thanks!

http://www.still-dancing.com/lastcomments

400 pics, 3.500 comments, works very good. Should definitly be part of 1.5.

regards

ps. date is really not needed at this place, so we took it out.

[Fabricio Ferrero]

@dwo: Thanks! Sadly, the only way to have this for v1.5 it's that somebody become this hack to a plugin for v1.5 or adapts the mod. The freeze stage has already begun and can't be added.

@Pastinakel: Good idea!


@SaWey: Very nice work! Great hack!!! 

[qdinar]

so we took it out

i have found how to do it. just after modificated part: modify this:
if ($set_caption) build_caption($rowset,array('msg_body','msg_date'));
- remove 'msg_date' from function call (remove also comma).

how much this latest hack is better than previous hacks? was not previous hacks also fast?
why comment times are lost in this latest modification?