forum.coppermine-gallery.net
No Support => Modifications/Add-Ons/Hacks => Mods: Comments => Topic started by: Pastinakel 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
// 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.... ;)
-
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.
-
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.
-
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.
-
Can You post the latest code? For 1.4.9. ?
-
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
-
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
-
Can You post the latest code?
-
Can You post the latest code?
what do you mean with latest ?
-
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:
// 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
-
Enable debug mode in config to get a better error message.
-
Hello.
Thank you so much.
Due to the error message I had a look in my database and then changed the code to `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
-
Now, I used the code of interarte, it works aswell. Thank you :-)
$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.
-
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?
-
New version to group comments:
in functions.inc.php at around line 1000 replace with this
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;
-
New version to group comments:
in functions.inc.php at around line 1000 replace with this
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
-
Does anybody has a CPG to see a live demo? Please..
Thanks,
-
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.
-
@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
-
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?
-
one more bug of last comments code that is in Reply #14 (http://forum.coppermine-gallery.net/index.php/topic,35240.msg282055.html#msg282055) : when [you](?) open category or album, last comments stay as it is on the frontpage, while last comments' title says "last comments - category of something" and built-in (default,original,standard) code showed here only comments of images that are in currently being viewed category or album.
-
Finally I found one problem with the code.
"last comments" shows all comments. This means the comments on private albums, too. It should hide them.
Is there any way to fix that?
Thank you very much.
-
Thank you and best regards :)
-
Hello, somebody knows how to adapt it for version 1.5.h. The problem stems from the fact that this version is used msg_id?
Thank you for your help!
-
It is definitely something that CPG 1.5 needs ;)
-
And I have implemented it.
In functions.inc.php after
if ($cat && $CURRENT_CAT_NAME) {
$album_name = cpg_fetch_icon('comment', 2) . $album_name = $lang_meta_album_names['lastcom'] . ' - ' . $CURRENT_CAT_NAME;
} else {
$album_name = cpg_fetch_icon('comment', 2) . $lang_meta_album_names['lastcom'];
}
The $query becomes
$query = "SELECT COUNT(DISTINCT(r.pid))
FROM {$CONFIG['TABLE_COMMENTS']} AS c
INNER JOIN {$CONFIG['TABLE_PICTURES']} AS r ON r.pid = c.pid
INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
$RESTRICTEDWHERE
AND r.approved = 'YES'
AND c.approval = 'YES'";
And after
$select_columns = implode(', ', $select_column_list);
the $query becomes
$query = "SELECT * FROM (SELECT $select_columns
FROM {$CONFIG['TABLE_COMMENTS']} AS c
INNER JOIN {$CONFIG['TABLE_PICTURES']} AS r ON r.pid = c.pid
INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
$RESTRICTEDWHERE
AND r.approved = 'YES'
AND c.approval = 'YES'
ORDER BY msg_id $DESC) AS c
GROUP BY c.pid
ORDER BY msg_id $DESC
$limit";
That's all. I am no coding expert but I've found out that it works and I'm happy to share it. :)
I take no responsibility if it doesn't work as expected.
-
Well, it doesn't. I will come back later if I find any other solution.
-
FYI: this meta album has been added to the "More Meta Albums" plugin (http://forum.coppermine-gallery.net/index.php/topic,63706.0.html) in version 1.7.