Re: [Sqlalchemy-users] Two foreign keys to the same table

Michael Bayer Sun, 24 Sep 2006 12:52:29 -0700

On Sep 23, 2006, at 11:00 PM, Rúben Leote Mendes wrote:

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/MultiFkRelations

I started from there and added some code (full source in attach).

First the table definitions:

info_table = Table('infos', meta,
   Column('pk', Integer, primary_key=True),
   Column('info', String))

rel_table = Table('rels', meta,
Column('pk', Integer, primary_key=True),
Column('info_fk_one', Integer, ForeignKey(info_table.c.pk), nullable=False),
Column('info_fk_two', Integer, ForeignKey(info_table.c.pk), nullable=False),
Column('rel_data', String))

As you can see this table has two foreign keys to the infos table.
In my example program the info table has people's names in it, and the
rels table has relationships between them, like "Uncle" or "Father".

Now let's see the mappers (the classes were defined but not shown because
their are trivial):

properties = { 'relation_direct' : relation(Relationship, primaryjoin=(info_table.c.pk == rel_table.c.info_fk_one)),
   'relation_inverse' : relation(Relationship, primaryjoin=(info_table.c.pk == rel_table.c.info_fk_two))}

Relationship.mapper = mapper(Relationship, rel_table)

Information.mapper = mapper(Information, info_table, properties = properties)

Information.mapper.add_property('relations',
relation(Information,
   primaryjoin=(info_table.c.pk == rel_table.c.info_fk_one),
   secondaryjoin=(rel_table.c.info_fk_two == info_table.c.pk),
   secondary=rel_table))

This mappers seem to be working but I'm not sure their are correct.
Note that Information has a relation to itself.

im skeptical of this mapping, particularly with regards to write operations, since the "rel_table" is semantically ambiguous. is it an association table, or is it the table that stores "Relationship" entities ? by using it for both at the same time the save process may very well produce conflicting operations. the "secondary" table is assumed to usually be a table that only contains foriegn keys to the two related tables.

it seems here that it would be useful if the mapping of Information to itself could have some kind of "read-only" flag, since its not really a complete mapping of the "rel_table". it might be the case that during a flush operation, things just work out anyway, so maybe its good enough in that regard.

you could also try the "relations" relation mapping using just a single "primaryjoin" across the two tables. *or*, not even have it and just use a property accessor, this is the cleaner way to do it:

def _relations(self):

foreach x in self.relation_direct:

foreach y in x.relation_inverse:

yield y

def _add_relation(self, info):

info.relation_inverse.append(self)

self.relation_direct.append(info)

relations = property(_relations, _add_relation)

If I create 4 Information objects (i1, i2, i3 and i4) there are at least 3
ways to specify relations between i1 and the others:

1. rel1 = Relationship(i1, i2, "Uncle")
session.save(rel1)

2. rel2 = Relationship(i1, i3, "Uncle")
i1.relation_direct.append(rel2)

3. i1.relations.append(i4)

The third one has just created a Relationship object automatically but
rel_data is not defined. If I could somehow receive this object I could
easily specify the relationship data between i1 and i4. Is this possible?
I could do something like: rel_obj.rel_data = "Father"

normally when you want an assocaition with extra data you use the Assocaition Object pattern, which has an example at http://www.sqlalchemy.org/docs/datamapping.myt#datamapping_association. but since you already have the Relation objects associated with your Information, the approach I outlined above is the most direct.

My second question is if it is possible to do a query on Information based
on rel_data (attribute of Relationship), like a query that returns all of
John's nephews (rel_data = "Uncle")?

I think the SQL output of the ORM should be something like:

SELECT infos2.pk, infos2.info FROM infos
JOIN rels ON infos.pk = rels.info_fk_one
JOIN infos AS infos2 ON rels.info_fk_two = infos2.pk
WHERE infos.pk = ? AND rels.rel_data = ?

info2 = info_table.alias('info2')

session.query(Information).select(

info_table.select(

and_(info2.c.pk==17,rel_table.c.rel_data=='somedata'),

from_obj=[info_table.join(rel_table, info_table.c.pk==rel_table.c.info_fk_one).join(info2, info2.c.pk==rel_table.c.info_fk_two)]

)

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV

_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users

Re: [Sqlalchemy-users] Two foreign keys to the same table

Reply via email to