On Thu, Oct 20, 2016 at 12:11 PM, Lele Gaifax <l...@metapensiero.it> wrote: > Hi all, > > I'd like to have an Entity able to be "attached" to N different others, > avoiding the need of N intermediate/secondary tables. > > I imagine something like the following:: > > class Address(Base): > __tablename__ = 'addresses' > id = Column(Integer, primary_key=True) > attached_kind = Column(String) > attached_to = Column(Integer) > > street = Column(String) > city = Column(String) > state = Column(String) > zip = Column(String) > > class Person(Base): > __tablename__ = 'persons' > id = Column(Integer, primary_key=True) > name = Column(String) > addresses = relationship( > Address, > uselist=True, > primaryjoin="and_(Address.attached_to==Person.id, > Address.attached_kind='Person')") > > class Home(Base): > __tablename__ = 'homes' > id = Column(Integer, primary_key=True) > name = Column(String) > > addresses = relationship( > Address, > uselist=True, > primaryjoin="and_(Address.attached_to==Home.id, > Address.attached_kind='Home')") > > This is really a variant of > http://docs.sqlalchemy.org/en/rel_1_1/orm/join_conditions.html#specifying-alternate-join-conditions > except that the Address entity carries a tuple of (remote_object_kind, > remote_object_id) that uniquely identify the related object. > > The above partially works, but as explained in the doc, SA does not populate > the "attached_*" fields when I do something like:: > > newaddr = Address(street='somewhere') > person.addresses.append(newaddr) > > so I have to do this instead:: > > newaddr = Address(street='somewhere', attached_kind='Person', > attached_id=person.id) > > Is there any trick hidden somewhere, or do I need a add_address() method on > each entity?
There are a few examples of different ways of doing this at http://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.generic_associations. I think your example is more or less the same as the one at http://docs.sqlalchemy.org/en/latest/_modules/examples/generic_associations/generic_fk.html. As the comment at the top of that file says, the database schema isn't ideal because there is no database-level foreign key constraint on the Address.attached_to column, so ensuring database consistency is more difficult. If that isn't a concern in your application, you can probably carry on with what you've got. Otherwise, you might want to look at one of the alternative suggestions. Hope that helps, Simon -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
