|
|
CREATE TABLE xar_comments (
xar_cid int(11) NOT NULL auto_increment,
xar_pid int(11) NOT NULL,
xar_modid int(11) default NULL,
xar_itemtype int(11) NOT NULL,
xar_objectid varchar(255) NOT NULL,
xar_date int(11) NOT NULL,
xar_author mediumint(9) NOT NULL default '1',
xar_title varchar(100) NOT NULL,
xar_hostname varchar(255) NOT NULL,
xar_text mediumtext,
xar_left int(11) NOT NULL,
xar_right int(11) NOT NULL,
xar_status tinyint(4) NOT NULL,
xar_anonpost tinyint(4) default NULL,
PRIMARY KEY (xar_cid),
KEY i_xar_comments_left (xar_left),
KEY i_xar_comments_right (xar_right),
KEY i_xar_comments_pid (xar_pid),
KEY i_xar_comments_modid (xar_modid),
KEY i_xar_comments_itemtype (xar_itemtype),
KEY i_xar_comments_objectid (xar_objectid),
KEY i_xar_comments_status (xar_status),
KEY i_xar_comments_author (xar_author)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table 'xar_comments'
--
INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (1, 0, 151, 1, '1', 1185465933, 1, 'ROOT NODE - PLACEHOLDER. DO NOT DELETE!', '', 'This is for internal use and works only as a place holder. PLEASE do NOT delete this comment as it could have detrimental effects on the consistency of the comments table.', 1, 14, 3, NULL);
INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (2, 1, 151, 1, '1', 1185465933, 3, 'Head 1', '127.0.0.1', 'test', 2, 7, 2, 0);
INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (3, 1, 151, 1, '1', 1185466867, 3, 'Head 2', '127.0.0.1', 'Test', 8, 11, 2, 0);
INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (4, 2, 151, 1, '1', 1185472409, 3, 'Re: Head 1', '127.0.0.1', 'test', 3, 6, 2, 0);
INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (5, 4, 151, 1, '1', 1185472434, 3, 'Re(1): Head 1', '127.0.0.1', 'test', 4, 5, 2, 0);
INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (6, 3, 151, 1, '1', 1185472468, 3, 'Re: Head 2', '127.0.0.1', 'test', 9, 10, 2, 0);
INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (7, 1, 151, 1, '1', 1185472479, 3, 'Head 3', '127.0.0.1', 'test', 12, 13, 2, 0);
INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (14, 13, 151, 1, '2', 1185550631, 3, 'test', '127.0.0.1', 'test', 2, 3, 2, 0);
INSERT INTO xar_comments (xar_cid, xar_pid, xar_modid, xar_itemtype, xar_objectid, xar_date, xar_author, xar_title, xar_hostname, xar_text, xar_left, xar_right, xar_status, xar_anonpost) VALUES (13, 0, 151, 1, '2', 1185550631, 1, 'ROOT NODE - PLACEHOLDER. DO NOT DELETE!', '', 'This is for internal use and works only as a place holder. PLEASE do NOT delete this comment as it could have detrimental effects on the consistency of the comments table.', 1, 4, 3, NULL);
SELECT node.xar_cid, node.xar_modid,node.xar_itemtype, node.xar_objectid,node.xar_title, (COUNT(parent.xar_cid) - (sub_tree.depth + 1)) AS depth
FROM xar_comments AS node,
xar_comments AS parent,
xar_comments AS sub_parent,
(
SELECT node.xar_cid, (COUNT(parent.xar_cid) - 1) AS depth
FROM xar_comments AS node,
xar_comments AS parent
WHERE node.xar_left BETWEEN parent.xar_left AND parent.xar_right
AND node.xar_cid = 1
AND parent.xar_modid = node.xar_modid
AND parent.xar_itemtype = node.xar_itemtype
AND parent.xar_objectid = node.xar_objectid
GROUP BY node.xar_cid
ORDER BY node.xar_left
)AS sub_tree
WHERE node.xar_left BETWEEN parent.xar_left AND parent.xar_right
AND node.xar_left BETWEEN sub_parent.xar_left AND sub_parent.xar_right
AND sub_parent.xar_cid = sub_tree.xar_cid
GROUP BY node.xar_cid
HAVING depth <= 1
|
Pastie << self Blog
