title: Outer Join with sqlalchemy (ORM)
body: <p>I am trying to do a <code>relationship()</code> with an OUTER JOIN so that it joins the second table if there is something to join it with.  I am currently stuck on how to do this though, I cannot seem to figure out the right combination of <code>options()</code>, <code>relationship()</code> and <code>outerjoin()</code>.</p>
  
  <p>I have the following tables and I am trying to join AppLike to Application if a row exists with the Application ID AND the artistID (which is provided by the function)</p>
  
  <p>Happy to provide any additional information, I already have one of my joins working as you can see below, but there will always be a row to match for that one.</p>
  
  <pre><code>from sqlalchemy import Column
  from . import Base
  from . import DBSession
  from sqlalchemy.dialects.mysql import (
      INTEGER,
      VARCHAR,
      TEXT,
      TINYINT,
      )
  from sqlalchemy.sql import and_
  from sqlalchemy import ForeignKey
  from sqlalchemy.orm import relationship, joinedload
  import time
  
  # 0 = new
  # 1 = Denied
  # 2 = Accepted
  def getNewApplications(artistID):
      query =  DBSession.query(Application).\
          options(joinedload('pieces')).\
          options(joinedload('vote')).\
          filter(AppLike.artist_id==artistID).\
          filter(Application.approved==0)
          #join(AppPiece, Application.app_id==AppPiece.app_id).\
  
          #outerjoin(AppLike, and_(Application.app_id==AppLike.app_id,
          #    AppLike.artist_id==artistID)).\
  
      import pdb; pdb.set_trace()
      return query.all()    
  
  class Application(Base):
      """ The SQLAlchemy declarative model class for a FileFavorite object. """
      __tablename__ = 'applications'
      __table_args__ = {
          'mysql_engine': 'InnoDB',
          'mysql_charset': 'utf8'
      }
  
      app_id = Column(INTEGER(11), autoincrement=True, primary_key=True, nullable=False)
      name = Column(VARCHAR(64), nullable=False)
      nickname = Column(VARCHAR(64), nullable=False)
      email = Column(VARCHAR(255), nullable=False)
      description = Column(TEXT(), nullable=False)
      profile_link = Column(VARCHAR(128), nullable=False)
      location = Column(VARCHAR(64), nullable=False)
      approved = Column(TINYINT(4), nullable=False)
      pieces = relationship("AppPiece", lazy='joined')
      vote = relationship("AppLike", lazy='joined')
  
      def __init__(self, name, nickname, email, desc, profileLink,
                   location, approved):
          self.name = name
          self.nickname = nickname
          self.email = email
          self.description = desc
          self.profile_link = profileLink
          self.location = location
          self.approved = approved
  
  class AppPiece(Base):
      """ The SQLAlchemy declarative model class for a FileFavorite object. """
      __tablename__ = 'app_pieces'
      __table_args__ = {
          'mysql_engine': 'InnoDB',
          'mysql_charset': 'utf8'
      }
  
      app_piece_id = Column(INTEGER(11), autoincrement=True, primary_key=True, nullable=False)
      app_id = Column(INTEGER(11), ForeignKey('applications.app_id'))
      link = Column(VARCHAR(128), nullable=False)
  
      def __init__(self, appID, link):
          self.app_id = appID
          self.link = link
  
  class AppLike(Base):
      """ The SQLAlchemy declarative model class for a FileFavorite object. """
      __tablename__ = 'app_likes'
      __table_args__ = {
          'mysql_engine': 'InnoDB',
          'mysql_charset': 'utf8'
      }
  
      app_id = Column(INTEGER(11), ForeignKey('applications.app_id'))
      artist_id = Column(INTEGER(11), primary_key=True, nullable=False)
      vote = Column(TINYINT(4), nullable=False)
  
      def __init__(self, appID, artistID, vote):
          self.app_id = appID
          self.artist_id = artistID
          self.vote = vote
  </code></pre>