[Zope] Help: Recursive querying

[Rob Page]

> it is me again. I am finally at a point where I try to write my first
> application in Zope. My goal is to write a threaded Discussion Board.
> I did one before in Python using CGIs.
> Here is the supporting table in PostGreSQL:
> 
> id	int4
> pid	int4
> date	datetime
> title	varchar(40)
> body	text


First, I'd avoid using id as a column name in Zope -- Zope uses id and
can get cranky when conflicts arise..  Let's assume you change it to:

obj_id int4
parent_id int4
date datetime
title varchar(40)
body text

What I'm about to suggest came to me by way of Anthony Baxter -- any
cleverness it his.  Any bugs are mine.

Try:

SQL Method  sqlSelectChildren:
---
    select * from posts
    where
    <!--#if "parent_id == 0"-->
      parent_id is NULL
    <!--#else-->
      parent_id = <!--#sqlvar parent_id type=int-->
    <!--#/if-->
---

Now the tree. This DTML gives a tree, with each node in the tree a 
link to URL1/main_view?obj_id=<the object's id>

---
    <!--#with "_.namespace(parent_id = 0)"-->
      <!--#tree id=parent_id
branches_expr="sqlSelectChildren(parent_id=parent_id)"-->
	<a href="<!--#var URL1-->/ViewPost?parent_id=<!--#var
parent_id-->">
	     <!--#var title-->
	</a>
      <!--#/tree-->
    <!--#/with-->