Hi all. I need to create a block (with views module) that contain last
nodes that have new comments.
The view that I have created return me this SQL:
SELECT DISTINCT(node.nid) AS nid, term_data.name AS term_data_name,
term_data.vid AS term_data_vid, term_data.tid AS term_data_tid,
node.title AS node_title, node.created AS node_created,
node_comment_statistics.comment_count AS
node_comment_statistics_comment_count, comments.timestamp AS
comments_timestamp FROM node node LEFT JOIN term_node term_node ON
node.vid = term_node.vid LEFT JOIN term_data term_data ON term_node.tid
= term_data.tid INNER JOIN node_comment_statistics
node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT
JOIN comments comments ON node.nid = comments.nid WHERE (node.type in
('articolo_corto', 'articolo_lungo', 'articolo_medio', 'dossier',
'story')) AND (node.status <> 0) AND (term_data.vid in ('1'))
ORDER BY comments_timestamp DESC
But there is a problem: results are not unique.
Which is the correct SQL? "Group by nid" and "group by node_title"
return me a non correct ordered list.
Tnx.
M.
--
Michel 'ZioBudda' Morelli michel@ziobuddalabs.net
Sviluppo applicazioni CMS DRUPAL e web dinamiche (LAMP+Ajax)
Telefono: 0200619074
Telefono Cell: +39-3939890025 -- Fax: +39-0291390660
http://www.ziobudda.net ICQ: 58351764
http://www.ziobuddalabs.it Skype: zio_budda
http://www.ziodrupal.net MSN: michel@ziobuddalabs.it
JABBER: michel@ziobuddalabs.it