family tree

[jawarumnur]

Hello,

I'd like to build something like a family tree where "descendants",
"mother" and "father" are properties (of each "person-object") defined
by a mapper. When these properties are accessed, one (in case of
"mother" or "father") new "person-object" schould be loaded, or a list
of "person-objects" (in case of "descendants").
Each row in the table presents one persons, who "knows" its
"mother_id" and its "father_id".

That is not really a normal tree structure because a "person-object"
may not only have many "descendants" but also has more than one
ancester ("mother" and "father").
Is there a way to build a mapper that can map these relationships?

thanks for help,
jawa

[Alexandre Conrad]

Hi,

> That is not really a normal tree structure because a "person-object"
> may not only have many "descendants" but also has more than one
> ancester ("mother" and "father").
> Is there a way to build a mapper that can map these relationships?

You might want to have a look at "Adjacency List Relationships":

http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_selfreferential

Regards,
--
Alexandre CONRAD

[jawarumnur]

> You might want to have a look at "Adjacency List Relationships":
>

> http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relatio...

Thanks for your reply.
But that is what I already looked at and tried to use, but it didn't
work.
I think the problem is, that there are two columns ("mother" and
"father") with a foreign-key that reference the same table.
sqlalchemie doesn't seem to know, which one to use for which property
(well, thats quite consequential, as I didn't tell it, which foreign-
key belongs to which property...).

I will specify my question a little: is there a way to allocate
properties -manually- to certain foreign-key-columns?

I hope now my problem has become a little clearer

greetz,
jawa

[jawarumnur]

Ok, I just read about the relation option "foreign_keys"... I think,
that might be the 'magic' of the whole thing^^

sry, for bothering you,
jawa

[Arnar Birgisson]

On 9/27/07, jawarumnur <mascha_...@yahoo.de> wrote:
> I think the problem is, that there are two columns ("mother" and
> "father") with a foreign-key that reference the same table.
> sqlalchemie doesn't seem to know, which one to use for which property
> (well, thats quite consequential, as I didn't tell it, which foreign-
> key belongs to which property...).

SQLAlchemy can definitely do this, i.e. have two foreign keys
referencing the same table. I've done something similar.

I'm a little rusty and this is non-tested written-directly-in-email:

persons = Table("persons", meta,
Column("person_id", Integer, primary_key=True),
Column("name", Unicode(128), nullable=False),
Column("is_male", Boolean, nullable=False),
Column("mother_id", Integer, ForeignKey("persons.person_id")),
Column("father_id", Integer, ForeignKey("persons.person_id"))
)

class Person(object):

get_children(self):
if self.is_male:
return self.children_father
else:
return self.children_mother

add_child(self, child):
if self.is_male:
child.father = self
else:
child.mother = self

mapper(Person, persons, properties={
'mother': relation(Person,
primaryjoin=persons.c.person_id==persons.c.mother_id,
backref='children_mother'),
'father': relation(Person,
primaryjoin=persons.c.person_id==persons.c.father_id,
backref='children_father')
})

Arnar

[jawarumnur]

thanks, I'll try that, if the relation option "foreign_keys" didn't do
it.

I'd like to get "normal" properties with "normal" getter and setter or
append methods. I use sqlalchemy because I don't want to write all
these methods by myself ;)

greetz,
jawa

[Arnar Birgisson]

On 9/27/07, jawarumnur <mascha_...@yahoo.de> wrote:

> thanks, I'll try that, if the relation option "foreign_keys" didn't do
> it.

I think you don't need foreign_keys. The docs for foreign_keys states
that it should be used in conjuction with primaryjoin only if SA can
not guess the FK from the join condition alone. In this case we're
only joining on defined ForeignKey(..) fields so SA should have no
problem figuring it out.

> I'd like to get "normal" properties with "normal" getter and setter or
> append methods. I use sqlalchemy because I don't want to write all
> these methods by myself ;)

What do you mean "normal"? You have normal getters and setters for the
properties father and mother. If you want a magic property for
"children" that you can, say, append stuff to - I don't think SA will
help you much since the relationship to the child depends strictly on
if you are to be it's father or mother (which depends on the parent's
gender). SA doesn't do that kind of logic.

Besides, it's quite simple to do by hand. If you want a proper
collection for "children" that you can append to, a small helper class
will solve that issue.

Arnar

[jawarumnur]

mh, you're right.
I'm not yet very familiar with sqlalchemy since this is my first try.
I thought about a WHERE clause additional to the join condition to
differentiate the genders but probably that won't work either.
I'll try to use python properties then, I think.

thanks,
jawa

[Arnar Birgisson]

On 9/27/07, jawarumnur <mascha_...@yahoo.de> wrote:

You can probably get að "children" property by using a join condition
that "or"-s together the join on father_id and mother_id, but it will
be read-only at best. I.e. you won't be able to just "append" person
objects on it.

Arnar