SQLAlchemy self-referential many to many with extra association data

Question

I have the following basic SQLAlchemy model with a many to many self reference:

class Organisation(Base):
    __tablename__ = 'organisation'

    name = Column(String(50))
    url = Column(String(128))
    associated = relationship(
                        'Organisation',
                        secondary="organisation_map",
                        primaryjoin="Organisation.id==organisation_map.c.organisation_id",
                        secondaryjoin="Organisation.id==organisation_map.c.assoc_organisation_id",
                        backref="account_organisation"

    )

The association table looks like this:

class OrganisationMap(Base):
    __tablename__ = 'organisation_map'

    organisation_id = Column(Integer, ForeignKey('organisation.id'))
    assoc_organisation_id = Column(Integer, ForeignKey('organisation.id'))
    is_customer = Column(Boolean, default=False)
    is_supplier = Column(Boolean, default=False)

The association table contains extra data, is_customer and is_supplier that I want to be able to reference from the model itself, eg:

class Organisation(Base):
    ...

    def get_suppliers(self):
        pass

    def get_customers(self):
        pass

At the moment, I have no way of getting such a list without first querying the association table, OrganisationMap, getting the ids of 'customers' or 'suppliers' and then querying the Organisation table with a list of ids.

Is this possible?

See [this question](http://stackoverflow.com/questions/7417906/sqlalchemy-manytomany-secondary-table-with-additional-fields). The self-referential part should only require nominal changes to the configuration. — univerio, Apr 11 '16 at 19:05

J_Zar · Answer 1 · 2016-04-11T07:42:19.337

It think you should be able to use the ORM events to insert and update your is_customer and is_supplier accordingly. I.e.:

@event.listens_for(OrganisationMap, 'after_insert')
@event.listens_for(OrganisationMap, 'after_update')
def update_organization_map(mapper, connection, target):
    # query your db to fill this 2 fields accordingly
    # you have the connection object 
    target.is_customer = query_to_fill_is_customer
    target.is_supplier = query_to_fill_is_supplier

I don't know if the events that I used in the example above are ok for you. Please read the SqlAlchemy ORM event page for the complete list:

http://docs.sqlalchemy.org/en/latest/orm/events.html

It is possible that events 'before_insert' and before_update' are fine for this purpouse.

I perhaps didn't explain very well. I can already *insert* and *update* ok by targeting `Organisation` followed by `OrganisationMap`. I want to be able get to *select* related objects based on the `is_customer` or `is_supplier` fields. For example `organisation_1.associated` will return a list of associated organisations. I need to do the equivalent for `organisation_1.suppliers` or `organisation_1.customers` — user162390, Apr 11 '16 at 09:22

SQLAlchemy self-referential many to many with extra association data

1 Answers1