bruno desthuilliers wrote:
> First point, you have a tree structure. There are a couple ways to
> handle trees in a relational model, each has pros and cons. The most
> obvious one is the recursive relationship (also known as 'adjacency
> list'):
>
> create table Person(
> id integer primary key auto increment,
> first_name varchar(50) NULL,
> last_name varchar(50) NULL,
> city varchar(50) NULL,
> ancestor_id integer NULL
> )
>
> where ancestor_id refers to the Person.id of the 'ancestor'.
>
> The problem with this solution is that SQL has no way to let you
> retrieve a whole 'branch' in one single query.
>
True of classical sql, but just to note as an interesting aside - modern
databases support "common table expressions". Maintaining other reps,
materialized path etc. as you mention, still probably better (higher
perf) for many use cases, just sayin'.
CTEs are of course expressed in sql's dire syntax, but anyway in e.g.
recent postgresql this will work:
with recursive x as
(select *, 1 as lev, '.' || id::text as path
from person where id=3
union all
select p.*, x.lev+1 as lev, x.path || '.' || p.id::text as path
from person p join x on p.ancestor_id = x.id)
select * from x;
-- there, clear as mud, person 3 and their descendants,
with generated (relative) path and level for each record. I think.
see
http://wiki.postgresql.org/wiki/CTEReadme#Examples
http://en.wikipedia.org/wiki/Common_table_expressions
ObDjango: No idea how you'd persuade the ORM to generate CTEs ;-)
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
