Kenneth Downs wrote:
The bit of SQL you need to make it child's play is the "WITH RECURSE"
feature, which is sadly not widely supported:
SELECT record_id, text_of_message
FROM messages chd
JOIN messages par ON chd.record_id_par = par.record_id WITH RECURSE
WHERE chd.record_id = $x
The Oracle alternative is CONNECT BY/START WITH. PostgreSQL has a
similar package in contribs/tablefunc.
Here's a PostgreSQL stored procedure I used to dump a pre-order twalk
trace of a tree-structured message base.
------------------------------------------------------------------------------
-- TRACE_MESSAGE
--
-- Return the ordered thread below msgnum
--
------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trace_message (REFCURSOR, TEXT, INTEGER,
INTEGER) RETURNS REFCURSOR AS $$
DECLARE
v_curmsg ALIAS FOR $2;
v_who_id ALIAS FOR $3;
v_trace_depth ALIAS FOR $4;
BEGIN
OPEN $1 FOR
SELECT
TREE.*,
who.user_name AS author,
message.subject,
message.author_id,
message.forum_id,
message.is_invisible,
message.is_one_liner,
message.is_forum_header
FROM
connectby('tree', 'message_id', 'parent_id', 'sibling_order',
v_curmsg, v_trace_depth) AS TREE (message_id INT, parent_id INT, level
INT, sibling_order INT)
LEFT JOIN message ON message.message_id = TREE.message_id
LEFT JOIN who ON who.who_id = message.author_id
WHERE
-- various froo-froo filters
ORDER BY TREE.sibling_order;
RETURN $1;
END;
$$ LANGUAGE 'plpgsql';
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
