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 = all 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.