sqlalchemy中 .join() 和 .options() 方法的使用

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, joinedload, selectinload
from sqlalchemy.future import select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    role_id = Column(Integer, ForeignKey('roles.id'))
    role = relationship('Role')
    work_id = Column(Integer, ForeignKey('works.id'))
    works = relationship('Works')


class Role(Base):
    __tablename__ = 'roles'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))


class Works(Base):
    __tablename__ = 'works'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))


async def main():
    engine = create_async_engine('sqlite+aiosqlite:///example.db', echo=True)
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    async with AsyncSession(engine) as session:
        async with session.begin():
            # 1. options 与 join 顺序可以随便加，可以加最后面或者select后面，SQLAlchemy 会自动解析
            # 2. 使用 options 加载外键属性后，可以通过`.外键属性`获取到外键对象，使用 join 加载外键模型不可以获取，会报错
            # 3. 使用 options 或 join 加载后都可以通过 where 查询外键，但是只使用 options 加载后查询会报异常，会出现笛卡尔积
            # 建议：
            # 1. 只加载外键属性时，使用 options 加载
            # 2. 只需要用到外键关联查询时，使用 join 加载
            # 3. 需要加载外键属性的同时又需要外键关联查询，则 options 和 join 同时使用
            sql = select(User).where(Role.name == 'a').options(joinedload(User.role), joinedload(User.works)).join(Role)
            queryset = await session.execute(sql)
            datas = queryset.scalars().unique().all()
            for user in datas:
                print("user: {}".format(user.name))
                print("user.role: {}".format(user.role))
                print("user.works: {}".format(user.works))


if __name__ == "__main__":
    import asyncio
    asyncio.run(main())

只使用 options 查询的 SQL：
SELECT
    users.id,
    users.name,
    users.role_id,
    users.work_id,
    roles_1.id AS id_1,
    roles_1.name AS name_1,
    works_1.id AS id_2,
    works_1.name AS name_2 
FROM
    roles,
    users
    LEFT OUTER JOIN roles AS roles_1 ON roles_1.id = users.role_id
    LEFT OUTER JOIN works AS works_1 ON works_1.id = users.work_id 
WHERE
    roles.name = 'a'
    
会报警告:
sys:1: SAWarning: SELECT statement has a cartesian product between FROM element(s) "roles" and FROM element "users". 
Apply join condition(s) between each element to resolve.

会将users的所有数据都查询出来

使用 options 加 join 查询的SQL：
SELECT
    users.id,
    users.name,
    users.role_id,
    users.work_id,
    roles_1.id AS id_1,
    roles_1.name AS name_1,
    works_1.id AS id_2,
    works_1.name AS name_2 
FROM
    users
    JOIN roles ON roles.id = users.role_id
    LEFT OUTER JOIN roles AS roles_1 ON roles_1.id = users.role_id
    LEFT OUTER JOIN works AS works_1 ON works_1.id = users.work_id 
WHERE
    roles.name = 'a'
    
不会报警告
只会查询出来 roles.name = 'a' 的数据

sqlalchemy中 .join()  和 .options() 有什么区别？

在SQLAlchemy中，.join()和.options()都是用于指定查询的关联对象的方法，但是它们的作用和用法略有不同。
.join()是用于指定查询的关联对象的JOIN条件的方法。它可以在查询中指定关联对象之间的JOIN条件，例如通过指定外键关系或关联表达式等。
.join()方法返回一个新的查询对象，该对象包含了指定的JOIN条件，并可以被进一步的修改和过滤。

.options()是用于指定查询的关联对象的选项的方法。它可以在查询中指定关联对象的加载选项，例如通过指定joinedload或selectinload选项来实现预加载或延迟加载等。
.options()方法返回一个新的查询对象，该对象包含了指定的加载选项，并可以被进一步的修改和过滤。

因此，.join()和.options()的区别在于：
1. .join()用于指定JOIN条件，而.options()用于指定加载选项。
2. .join()返回一个新的查询对象，该对象包含了指定的JOIN条件，而.options()返回一个新的查询对象，该对象包含了指定的加载选项。
3. .join()可以用于复杂的JOIN操作，而.options()主要用于指定加载选项。

join 的使用

左连接
query = session.query(User, Role).join(Role, isouter=True).all()

右连接
query = session.query(User, Role).join(Role, isouter=True, full=True).all()

内连接
query = session.query(User, Role).join(Role, innerjoin=True).all()