SQLAlchemy Conditional Relationships

Question

I would like to have a one to many relation between two ORM objects and extend this with a second relation that links to the same 'many' object while applying a constraint.

The following example may elaborate:

class Users(SQLABase):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    addresses = relationship('Addresses', backref='user')


class Addresses(SQLABase):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    historic = (String(1))
    add1 = Column(String)
    user = Column(Integer, ForeignKey('users.id'))

I would like an attribute 'Users.valid_addresses' that relates to the same 'addresses' table filtering where Addresses.historic == 'N' like the following query:

Session.Query(Addresses).filter_by(historic = 'N').all()

I'm looking for the "SQLAlchemy way".

• Can I apply a condition to a relation?
• Am I expected to iterate over the results of the current relation?
• Should I create an additional 'valid_addresses' object based on an SQL view of the addresses table applying the condition?

I have the feeling this has already been answered but I'm failing to phrase the question correctly.

Answer 1

This is covered in the SQLAlchemy docs under "Specifying Alternate Join Conditions".

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    boston_addresses = relationship("Address",
                    primaryjoin="and_(User.id==Address.user_id, "
                        "Address.city=='Boston')")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))

    street = Column(String)
    city = Column(String)
    state = Column(String)
    zip = Column(String)