title: Can you add conditions to the on clause of a relationship join in SQLAlchemy?
body: <p>Say I have the following two SQLAlchemy ORM classes:</p>
  
  <pre><code>import sqlalchemy as sa
  
  class Address(Base):
      __tablename__ = 'DimAddress'
  
      AddressKey = sa.Column(sa.Integer, primary_key=True)
      # ... columns ...
  
  class DealerOrganisation(Base):
      __tablename__ = 'DimDealerOrganisation'
  
      DealerOrganisationKey = sa.Column(sa.Integer, primary_key=True)
      # ... columns ...
      DealerOrganizationAddressKey = sa.Column(sa.Integer, sa.ForeignKey('DimAddress.AddressKey'), nullable=False)
      # ... columns ...
  
      address = relationship('Address')
  </code></pre>
  
  <p>I can get dealer organizations and their address, if present, as follows:</p>
  
  <pre><code>session = Session()
  query = session.query(DealerOrganisation).outerjoin(DealerOrganisation.address).options(contains_eager(DealerOrganisation.address))
  </code></pre>
  
  <p>This gives me SQL approximately like this:</p>
  
  <pre><code>SELECT *
  FROM DimDealerOrganisation
  LEFT JOIN DimAddress ON AddressKey = DealerOrganizationAddressKey
  </code></pre>
  
  <p>But what if I want to do an ORM query for only a subset of related objects:</p>
  
  <pre><code>SELECT *
  FROM DimDealerOrganisation
  LEFT JOIN DimAddress ON AddressKey = DealerOrganizationAddressKey AND ZipCode = '90210'
  </code></pre>
  
  <p>That is, I want all the dealers, but I only want their address if the zip code is 90210. As far as I can tell, join() and outerjoin() let you specify either a relationship or an explicit condition, but not both. In this contrived example I could use an explicit condition and get back rows instead of ORM objects, but it would be unwieldy in a real query involving multiple tables and one-to-many relations. I want to add additional conditions to the on clause but still have it populate the <code>address</code> attribute of the returned DealerOrganisation objects. Is this possible?</p>