db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Recursive Select for Discussion Forum
Date Wed, 30 Nov 2005 18:42:09 GMT
Hi Michael,

You could streamline your recursive walk by using a temporary table and 
a database procedure. The temporary table would hold the ids you 
recursively harvest. It would be populated by your database procedure, 
which would walk up the levels of your hierarchy. When the procedure 
returned, you could then join the temporary table to your 
discussion_item table to get the details you needed. It's not an elegant 
solution, but by running the procedure inside the database, you would 
eliminate a lot of network handshaking.

Derby does not support hierarchical queries. You're welcome to log an 
enhancement request for this feature.


Michael McCutcheon wrote:

> I need to implement an online discussion forum, similar to JavaLobby.
> Similar kinds of discussion forums tend to use recursive selects, so 
> that in a single select, all of the items for a particular discussion 
> thread (and all sub threads, and their sub threads, etc) may be 
> retrieved.
> For a table, I'm thinking of something like this:
> create table discussion_item
> (
>  id integer not null,
>  parent_id integer not null,
>  creator_user_id varchar,
>  title varchar,
>  message varchar
> )
> There needs to be some kind of recursive functionality to select a 
> item, and it's parents, and it's parents, etc.
> Does Derby support any kind of recursive selecting?  I know that this 
> is supported in Oracle and other databases.
> If it does not, is it planned to have it in the future?
> If not, are there any ways of handling scenarios like this without 
> having to do a select for each item?
> Thanks for any input.
> Mike

View raw message