Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1] 2   Go Down

Author Topic: Grouping comments for the same pic in lastcom  (Read 38891 times)

0 Members and 1 Guest are viewing this topic.

Pastinakel

  • Contributor
  • Coppermine newbie
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 17
Grouping comments for the same pic in lastcom
« on: August 22, 2006, 01:17:36 pm »

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]
// 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....  ;)
« Last Edit: August 24, 2006, 05:04:32 am by GauGau »
Logged

Nibbler

  • Guest
Re: Grouping comments for the same pic in lastcom
« Reply #1 on: August 22, 2006, 01:46:46 pm »

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.
Logged

Pastinakel

  • Contributor
  • Coppermine newbie
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 17
Re: Grouping comments for the same pic in lastcom
« Reply #2 on: August 23, 2006, 02:53:31 pm »

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.
Logged

Nibbler

  • Guest
Re: Grouping comments for the same pic in lastcom
« Reply #3 on: August 23, 2006, 03:08:55 pm »

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.
Logged

Master of Orion

  • Coppermine newbie
  • Offline Offline
  • Gender: Male
  • Posts: 10
    • Wonderful China
Re: Grouping comments for the same pic in lastcom
« Reply #4 on: November 01, 2006, 10:47:27 am »

Can You post the latest code? For 1.4.9. ?

Master of Orion

  • Coppermine newbie
  • Offline Offline
  • Gender: Male
  • Posts: 10
    • Wonderful China
Re: Grouping comments for the same pic in lastcom
« Reply #5 on: November 01, 2006, 05:57:20 pm »

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

  • Coppermine newbie
  • Offline Offline
  • Posts: 1
Re: Grouping comments for the same pic in lastcom
« Reply #6 on: September 02, 2007, 10:48:04 pm »

Code: [Select]
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
 
Logged

Master of Orion

  • Coppermine newbie
  • Offline Offline
  • Gender: Male
  • Posts: 10
    • Wonderful China
Re: Grouping comments for the same pic in lastcom
« Reply #7 on: November 06, 2007, 12:42:05 pm »

Can You post the latest code?

François Keller

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: fr
  • Offline Offline
  • Gender: Male
  • Posts: 9097
  • aka Frantz
    • Ma galerie
Re: Grouping comments for the same pic in lastcom
« Reply #8 on: November 06, 2007, 01:22:21 pm »

Can You post the latest code?
what do you mean with latest ?
Logged
Avez vous lu la DOC ? la FAQ ? et cherché sur le forum avant de poster ?
Did you read the DOC ? the FAQ ? and search the board before posting ?
Mon Blog

dwo

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 143
Re: Grouping comments for the same pic in lastcom
« Reply #9 on: May 21, 2008, 11:03:52 am »

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]

                // 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
Logged

Nibbler

  • Guest
Re: Grouping comments for the same pic in lastcom
« Reply #10 on: May 21, 2008, 11:38:29 am »

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

dwo

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 143
Re: Grouping comments for the same pic in lastcom
« Reply #11 on: May 21, 2008, 11:54:34 am »

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]
`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
Logged

dwo

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 143
Re: Grouping comments for the same pic in lastcom
« Reply #12 on: May 21, 2008, 12:02:53 pm »

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

Code: [Select]
$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.
Logged

jerx

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 85
Re: Grouping comments for the same pic in lastcom
« Reply #13 on: November 17, 2008, 09:15:44 am »

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?
Logged

SaWey

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 1119
    • SaWey.be
Re: Grouping comments for the same pic in lastcom
« Reply #14 on: January 12, 2009, 04:44:21 pm »

New version to group comments:

in functions.inc.php at around line 1000 replace with this
Code: [Select]
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;
Logged

Criss

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 36
Re: Grouping comments for the same pic in lastcom
« Reply #15 on: February 10, 2009, 06:41:10 pm »

New version to group comments:

in functions.inc.php at around line 1000 replace with this
Code: [Select]
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
Logged

Fabricio Ferrero

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Male
  • Posts: 1997
  • From San Juan, Argentina, to the World!
    • http://fabricioferrero.com/
Re: Grouping comments for the same pic in lastcom
« Reply #16 on: February 10, 2009, 06:54:59 pm »

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

Thanks,
Logged
Read Docs and Search the Forum before posting. - Soporte en español
--*--
Fabricio Ferrero's Website

Catching up! :)

dwo

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 143
Re: Grouping comments for the same pic in lastcom
« Reply #17 on: February 11, 2009, 04:22:09 pm »

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.
Logged

Fabricio Ferrero

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Male
  • Posts: 1997
  • From San Juan, Argentina, to the World!
    • http://fabricioferrero.com/
Re: Grouping comments for the same pic in lastcom
« Reply #18 on: February 11, 2009, 05:02:46 pm »

@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!!!  :D
Logged
Read Docs and Search the Forum before posting. - Soporte en español
--*--
Fabricio Ferrero's Website

Catching up! :)

qdinar

  • Coppermine newbie
  • Offline Offline
  • Posts: 15
Re: Grouping comments for the same pic in lastcom
« Reply #19 on: April 24, 2009, 08:45:00 pm »

Quote
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?
Logged
Pages: [1] 2   Go Up
 

Page created in 0.03 seconds with 21 queries.