SQLAlchemy: a combined backref on multiple join paths

Question

Say we have a 'People' table that contains 'HomeAddressID' and 'WorkAddressID' columns.

We are defining a multiple join paths relationship to table 'Addresses' like this:

HomeAddress = relationship('Addresses', foreign-keys=[HomeAddressID])
WorkAddress = relationship('Addresses', foreign-keys=[WorkAddressID])

Now I want my class 'Addresses' to have an attribute 'People' that will list all people who are related to this address, regardless whether its home or work.

How can I do this?

Answer 1

The easiest generalized way to do this would be to have a mapping table for person<->address mapping with columns "person id, address id, type", otherwise things start to get a bit heavy on the DB side.

Answer 2

Untested but you can add a backref:

HomeAddress = relationship('Addresses', foreign_keys=[HomeAddressID],
backref='home_address')

WorkAddress = relationship('Addresses', foreign_keys=[WorkAddressID], backref='work_address')

Then you can do:

Addresses.home_address.all() or Addresses.work_address.all()