mike_g
March 18th, 2009, 05:29 PM
I'm modifying a gallery component. This query gets a list of last commented pictures. It shows a duplicate image thumbails for each comment and I want to only display each image once with the most recent comment.
So, I added the GROUP BY bit, but this does not always show the most recent comment, just one of the comments. The image ordering however is correct, as in it orders the pictures by when they were last commented, just the last comment for it shows up wrong. Can I fix this in SQL? If so how?
Cheers.
"SELECT a.*,cc.*, ca.*,u.username ".
"FROM #__joomgallery AS a, #__joomgallery_catg AS ca, #__joomgallery_comments AS cc ".
"LEFT JOIN #__users AS u on cc.userid = u.id ".
"WHERE a.id=cc.cmtpic AND a.catid=ca.cid AND a.published = '1' " . " AND a.approved='1' " . " AND cc.published = '1' " . " AND ca.published = '1' " . " AND cc.approved='1' " . " AND ca.access<=$gid ".
"GROUP BY cc.cmtpic ".
"ORDER BY cc.cmtdate DESC LIMIT $jg_toplist OFFSET $offset";
So, I added the GROUP BY bit, but this does not always show the most recent comment, just one of the comments. The image ordering however is correct, as in it orders the pictures by when they were last commented, just the last comment for it shows up wrong. Can I fix this in SQL? If so how?
Cheers.
"SELECT a.*,cc.*, ca.*,u.username ".
"FROM #__joomgallery AS a, #__joomgallery_catg AS ca, #__joomgallery_comments AS cc ".
"LEFT JOIN #__users AS u on cc.userid = u.id ".
"WHERE a.id=cc.cmtpic AND a.catid=ca.cid AND a.published = '1' " . " AND a.approved='1' " . " AND cc.published = '1' " . " AND ca.published = '1' " . " AND cc.approved='1' " . " AND ca.access<=$gid ".
"GROUP BY cc.cmtpic ".
"ORDER BY cc.cmtdate DESC LIMIT $jg_toplist OFFSET $offset";