python - 使用 sqlalchemy (ORM) 进行外连接

Question

我正在尝试使用 OUTER JOIN 执行 relationship() ，以便在有需要连接的内容时它会连接第二个表。我目前陷入了如何做到这一点的困境，但我似乎无法找出 options()、relationship() 和 outerjoin()< 的正确组合.

我有下表，如果存在包含应用程序 ID 和艺术家 ID(由函数提供)的行，我将尝试将 AppLike 加入到应用程序

很高兴提供任何其他信息，我已经有一个联接正在运行，如下所示，但总会有一行与该联接相匹配。

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

你绝对不需要options(joinedload('pieces'))，它已经在你的模型中定义了(lazy='joined')。连接条件是这里最棘手的部分，需要使用子查询来完成，因为我们也想在那里进行过滤。因此，最终的查询应该如下所示:

# 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()