Queries to get all ancestors/descendents of a tree in a db?

I have a table (id, parent_id, data) where parent_id points to another row in same table (or is null).

Is there a standard way to query (1) all the ancestors of a certain id and (2) all the descendants of a certain id?

I'm also doing this in DBIx::Class, so if there's a most convenient way to do it with that module (or some other), I'd love to hear about that as well.

EDIT: clarify - all parents = all ancestors, all children = a开发者_如何学Cll descendants.

This highly depends on the flavor of SQL you are using.

In Oracle, you can use the START WITH id = yourid CONNECT BY PRIOR id = parent_id construct. In PostgreSQL, you can use a function connectby('tablename', 'id', 'parent_id', 'id', value, 0).

In many cases, it makes sense to represent trees differently, by defining a column which will hold, for every node, a complete path from the root element to this node.

There are plenty examples of this technique to be found on the Internet, the most recent one I saw, which also deals with DBIx::Class, can be found here: http://blogs.perl.org/users/ovid/2010/05/threaded-forum-sql.html

It looks like we're going to go with DBIx::Class::Tree::AdjacencyList at the moment. It does almost everything I was looking for (no ancestors resultset, unfortunately - but we can work around that by approaching the questions we need to ask from the other direction).

However, @Grrrr's answer got me thinking, and we may add a separate table + module (id, record_type, record_ancestors) that would attach to our models that have a parent_id column and provide an ancestors resultset (basically by doing a search_rs where the id is in the split of the relevant ancestors row by w/e delimiter we pick). That's a fair bit of work just to get such a result set, so we'll probably only go there if we find questions where it's really impractical to ask "is this a child of parent x" and really need "is this a parent of child x"?

EDIT: or maybe we'll use DBIx::Class::Tree::Mobius - though it looks like viewing the table raw would be incomprehensible.