title: Using contains_eager with a filter but without filtering out parents that have no matching children
body: <p>Reading the SQLAlchemy documentation:</p>
  
  <p><a href="http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#using-contains-eager-to-load-a-custom-filtered-collection-result" rel="nofollow noreferrer">http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#using-contains-eager-to-load-a-custom-filtered-collection-result</a></p>
  
  <blockquote>
    <p>The above query will load only User objects which contain at least Address object that contains the substring 'ed' in its email field.</p>
  </blockquote>
  
  <p>I am trying to do something similar, but I also want to retrieve all User objects which have no matching addresses at all.</p>
  
  <p>To explain myself better, I have modified this code which is one of the examples from the SQLAlchemy documentation:</p>
  
  <pre class="lang-py prettyprint-override"><code>from sqlalchemy.ext.declarative import as_declarative, declared_attr
  from sqlalchemy import create_engine, Integer, Column, \
                      String, and_ 
  from sqlalchemy.orm import Session, relationship, foreign, remote, backref, joinedload, contains_eager
  from sqlalchemy import event
  
  
  @as_declarative()
  class Base(object):
      """Base class which provides automated table name
      and surrogate primary key column.
  
      """
      @declared_attr
      def __tablename__(cls):
          return cls.__name__.lower()
      id = Column(Integer, primary_key=True)
  
  class Address(Base):
      """The Address class.
  
      This represents all address records in a
      single table.
  
      """
      street = Column(String)
      city = Column(String)
      zip = Column(String)
  
      discriminator = Column(String)
      """Refers to the type of parent."""
  
      parent_id = Column(Integer)
      """Refers to the primary key of the parent.
  
      This could refer to any table.
      """
  
      @property
      def parent(self):
          """Provides in-Python access to the "parent" by choosing
          the appropriate relationship.
  
          """
          return getattr(self, "parent_%s" % self.discriminator)
  
      def __repr__(self):
          return "%s(street=%r, city=%r, zip=%r)" % \
              (self.__class__.__name__, self.street,
              self.city, self.zip)
  
  class HasAddresses(object):
      """HasAddresses mixin, creates a relationship to
      the address_association table for each parent.
  
      """
  
  @event.listens_for(HasAddresses, "mapper_configured", propagate=True)
  def setup_listener(mapper, class_):
      name = class_.__name__
      discriminator = name.lower()
      class_.addresses = relationship(Address,
                          primaryjoin=and_(
                                          class_.id == foreign(remote(Address.parent_id)),
                                          Address.discriminator == discriminator
                                      ),
                          backref=backref(
                                  "parent_%s" % discriminator,
                                  primaryjoin=remote(class_.id) == foreign(Address.parent_id)
                                  )
                          )
      @event.listens_for(class_.addresses, "append")
      def append_address(target, value, initiator):
          value.discriminator = discriminator
  
  class Customer(HasAddresses, Base):
      name = Column(String)
  
  class Supplier(HasAddresses, Base):
      company_name = Column(String)
  
  engine = create_engine('sqlite://', echo=True)
  Base.metadata.create_all(engine)
  
  session = Session(engine)
  
  session.add_all([
      Customer(
          name='customer 1',
          addresses=[
              Address(
                      street='123 anywhere street',
                      city="New York",
                      zip="10110"),
              Address(
                      street='40 main street',
                      city="San Francisco",
                      zip="95732")
          ]
      ),
      Customer(
          name='customer 2',
          addresses=[
              Address(
                      street='88 hello yes',
                      city="Old Potato",
                      zip="10110"),
              Address(
                      street='123 banana street',
                      city="New Texas",
                      zip="23422"),
              Address(
                      street='40 dsfasadfmain street',
                      city="San Cocisfran",
                      zip="10110")
          ]
      ),
      Customer(
          name='customer 3',
          addresses=[
              Address(
                      street='123 orange street',
                      city="New Mew",
                      zip="23422"),
          ]
      ),
      Supplier(
          company_name="Ace Hammers",
          addresses=[
              Address(
                      street='2569 west elm',
                      city="Detroit",
                      zip="56785")
          ]
      ),
  ])
  
  session.commit()
  
  query = (
      session.query(Customer)
      .outerjoin(Customer.addresses)
      .filter(Address.zip == "10110")
      .options(contains_eager(Customer.addresses))
  )
  for customer in query.all():
      print(customer.name)
      print(str(customer.addresses))
  </code></pre>
  
  <p>This code correctly prints customers 1 and 2 and their addresses with a zip code of 10110, but I would also like to get customer 3, even if it has no address that matches at all.</p>
  
  <p>i.e.: I want to get a list of all customers, regardless of whatever their addresses might be, and for each one of the clients, I want their addresses to be populated, but only if they have a zip code of 10110.</p>
  
  <p>This would ideally appear as additional conditions to the JOIN, instead of a WHERE. The resulting query would probably look like this, I just don't know how to "express" it in SQLAlchemy terms:</p>
  
  <p><code>... LEFT OUTER JOIN address ON customer.id = address.parent_id AND address.discriminator = 'customer' AND address.zip = '10110'</code></p>