Outer Join with sqlalchemy (ORM)

Question

I am trying to do a relationship() 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 options(), relationship() and outerjoin().

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)

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.

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

Answer 1

You definitely don't need options(joinedload('pieces')), it is already defined in your models (lazy='joined'). The join condition is the tricky part here and needs to be done using subquery, since we want to filter there as well. So, the final query should look something like this:

# We do the filtering on AppLike in the subquery and later join
# Application to it.
applike_subq = DBSession.query(AppLike).\
    filter(AppLike.artist_id == artistID).subquery()
query = DBSession.query(Application).\
    outerjoin(applike_subq, Application.vote).\
    filter(Application.approved == 0).all()