如何左外连接使用SQLAlchemy关联对象

Question

我有三个表格角色，页面和角色页面。我尝试在关联表中使用带有额外数据的关联类，如下面的链接https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html。这是我的模型课

class RolePage(db.Model):
    __tablename__ = 'role_pages'
    role_id = db.Column(db.Integer, db.ForeignKey('roles.role_id'), primary_key=True)
    page_id = db.Column(db.Integer, db.ForeignKey('pages.page_id'), primary_key=True)
    can_readed = db.Column(db.Boolean, default=False)
    can_created = db.Column(db.Boolean, default=False)
    can_updated = db.Column(db.Boolean, default=False)
    can_deleted = db.Column(db.Boolean, default=False)
    can_submited = db.Column(db.Boolean, default=False)
    can_approved = db.Column(db.Boolean, default=False)
    arole = db.relationship('Role', back_populates='pages')
    apage = db.relationship('Page', back_populates='roles')

    def __repr__(self):
        return f'<RolePage ({self.role_id}, {self.page_id}, {self.can_readed}, {self.can_created})>'


class Role(db.Model):
    __tablename__ = 'roles'
    role_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32), unique=True, nullable=True)
    pages = db.relationship('RolePage', back_populates='arole')

    def __repr__(self):
        return f'<Role {self.name}>'


class Page(db.Model):
    __tablename__ = 'pages'
    page_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32), unique=True, nullable=True)
    roles = db.relationship('RolePage', back_populates='apage')

    def __repr__(self):
        return f'<Page {self.name}>'

我的问题是，我想使用左连接进行查询，就像我在sql中进行的查询一样：

select * from pages p
left join role_pages rp on rp.page_id = p.page_id and rp.role_id = 2 
left join roles r on r.role_id = rp.role_id;

我尝试使用ORM查询left join表

Page.query\
    .outerjoin(RolePage, and_(RolePage.role_id==id))\
    .all()

但是left join不起作用。使用left join的最佳方式是什么？就像我在sqlalchemy中遇到的问题一样

谢谢

Answer 1

在阅读了SQLAlchemy和许多论坛的文档之后。我得到了答案。要查询useleft join，我们可以使用isouter=True或.outerjoin()。 在我的例子中，我使用flask sqlalchemy来选择列，我使用.with_entities()，因为我有一个同名的列，我使用.label()来创建别名。这是我的SQLALchemy代码：

pages = Page.query\
                .with_entities(\
                    Page.page_id,\
                    Page.name.label('page_name'),\
                    Role.role_id,\
                    Role.name.label('role_name'),\
                    RolePage.can_readed,\
                    RolePage.can_created,\
                    RolePage.can_updated,\
                    RolePage.can_deleted,\
                    RolePage.can_submited,\
                    RolePage.can_approved,\
                    )\
                .join(RolePage,\
                (Page.page_id==RolePage.page_id) &
                (RolePage.role_id==id), isouter=True)\
                .join(Role, Role.role_id==RolePage.role_id, isouter=True)

这就解决了我的问题

Answer 2

要使用ORM，请创建一个会话 请参阅该文件

https://docs.sqlalchemy.org/en/13/orm/tutorial.html

from sqlalchemy import and_

q = session.query(Page, RolePage, Role) \
        .outerjoin(RolePage, and_(RolePage.page_id == Page.page_id, Rolepage.role_id == 2)) \
        .outerjoin(Role, Role.role_id == RolePage.role_id)

for _page, _role_page, _role in q:
    print(_page, _role_page, _role)