sqlalchemy - 在 sqlalchemy ORM 中加载外连接结果

Question

我需要在这个结构中加载结果：

{

    addresses: [,…]
    birth_date: "YYYY-MM-DD"
    created_at: "2020-09-29T15:27:30.789498"
    disabled: false
    email: "[e-mail]"
    email_confirmed: false
    gender: "m"
    id: 11
    items: [,…]
    mobile: [phone]
    mobile_confirmed: true
    name_first: "Sami"
    name_last: "xx"
    newsletter: true
    orders: [,…]
    preferred_language: "ar"
    updated_at: "2020-11-13T15:21:57.301272"
    
}

但我越来越空orders了items。他们的列没有加载。

class Users(Base):
    __tablename__ = "users"

    id =                  Column(Integer, primary_key=True, index=True)
    name_first =          Column(String, nullable=False)
    name_last =           Column(String, nullable=True)
    mobile =              Column(Integer, unique=True, index=True, nullable=False)
    mobile_otp=           Column(Integer, nullable=False)
    mobile_confirmed =    Column(Boolean, default=False)
    email =               Column(String, unique=True, index=True, nullable=True)
    email_confirmed =     Column(Boolean, default=False)
    password =            Column(String, nullable=False)
    password_reset_otp =  Column(Integer, nullable=True)
    gender=               Column(String, nullable=False)
    birth_date=           Column(DateTime, nullable=False)
    preferred_language =  Column(String, nullable=False)
    newsletter =          Column(Boolean, default=False)
    disabled =            Column(Boolean, default=False)
    created_at=           Column(DateTime, nullable=False)
    updated_at=           Column(DateTime, nullable=False)
    addresses = relationship('Addresses', uselist=True, back_populates="user", lazy='noload')
    orders = relationship('Orders', uselist=True, backref=backref("user"), lazy='noload')
    items = relationship('Items', uselist=True, backref=backref("user"), lazy='noload')
    staff = relationship("Staff", uselist=False, back_populates="user", lazy='raise')


class Orders(Base):
    __tablename__="orders"
    id=             Column(Integer, primary_key=True, index=True)
    user_id=        Column(Integer, ForeignKey('users.id'))
    address_id=     Column(Integer, ForeignKey('addresses.id'))

    delivery_at=    Column(DateTime)
    staff_id=      Column(Integer, ForeignKey('staff.id'), nullable=True)
    store_id=      Column(Integer, ForeignKey('stores.id'), nullable=True)

    created_at=     Column(DateTime)
    updated_at=     Column(DateTime)
    active    =     Column(Boolean, default=True)

    items = relationship("Items", uselist=True, back_populates="order", lazy='raise')
    lifecycle = relationship("OrderLifeCycles")
    
class Items(Base):
    __tablename__="items"
    id=             Column(Integer, primary_key=True, index=True)
    user_id=        Column(Integer, ForeignKey('users.id'))
    order_id =      Column(Integer, ForeignKey('orders.id'), nullable=True)
    size_id  =      Column(Integer, ForeignKey('sizes.id'))
    price    =      Column(Float, nullable=True)
    qty      =      Column(Integer)

    order= relationship("Orders", uselist=False, back_populates="items", lazy='raise')
    size= relationship("Sizes", uselist=False, lazy='raise')

SQLAlchemy 查询：

def get_user_by_mobile(db: Session, mobile: str, items: bool, orders:bool):
    orders = db.query(tables.Orders).filter(tables.Orders.active==True).subquery("orders")
    items = db.query(tables.Items).options(joinedload(tables.Items.size).joinedload(tables.Sizes.product)).filter(tables.Items.order_id==None).subquery("items")
    query = db.query(tables.Users).outerjoin(items, tables.Users.id == items.c.user_id).outerjoin(orders, tables.Users.id == orders.c.user_id)

    query = query.options(joinedload(tables.Users.addresses)).filter(tables.Users.mobile == mobile)

    return query.first()

如何在顶部的 json 结构示例中将子查询结果列表作为属性加载？类似于加载地址的方式。

查询语句中不包含子查询的列名：

SELECT          users.id                           AS users_id, 
                users.name_first                   AS users_name_first, 
                users.name_last                    AS users_name_last, 
                users.mobile                       AS users_mobile, 
                users.mobile_otp                   AS users_mobile_otp, 
                users.mobile_confirmed             AS users_mobile_confirmed, 
                users.email                        AS users_email, 
                users.email_confirmed              AS users_email_confirmed, 
                users.password                     AS users_password, 
                users.password_reset_otp           AS users_password_reset_otp, 
                users.gender                       AS users_gender, 
                users.birth_date                   AS users_birth_date, 
                users.preferred_language           AS users_preferred_language, 
                users.newsletter                   AS users_newsletter, 
                users.disabled                     AS users_disabled, 
                users.created_at                   AS users_created_at, 
                users.updated_at                   AS users_updated_at, 
                addresses_1.id                     AS addresses_1_id, 
                addresses_1.label                  AS addresses_1_label, 
                St_asewkb(addresses_1.coordinates) AS addresses_1_coordinates, 
                addresses_1.building_number        AS addresses_1_building_number, 
                addresses_1.flat_number            AS addresses_1_flat_number, 
                addresses_1.disabled               AS addresses_1_disabled, 
                addresses_1.created_at             AS addresses_1_created_at, 
                addresses_1.updated_at             AS addresses_1_updated_at, 
                addresses_1.user_id                AS addresses_1_user_id 
FROM            users 
left outer join 
                ( 
                       SELECT items.id       AS id, 
                              items.user_id  AS user_id, 
                              items.order_id AS order_id, 
                              items.size_id  AS size_id, 
                              items.price    AS price, 
                              items.qty      AS qty 
                       FROM   items 
                       WHERE  items.order_id IS NULL) AS anon_1 
ON              users.id = anon_1.user_id 
left outer join 
                ( 
                       SELECT orders.id          AS id, 
                              orders.user_id     AS user_id, 
                              orders.address_id  AS address_id, 
                              orders.delivery_at AS delivery_at, 
                              orders.staff_id    AS staff_id, 
                              orders.store_id    AS store_id, 
                              orders.created_at  AS created_at, 
                              orders.updated_at  AS updated_at, 
                              orders.active      AS active 
                       FROM   orders 
                       WHERE  orders.active = TRUE) AS anon_2 
ON              users.id = anon_2.user_id 
left outer join addresses AS addresses_1 
ON              users.id = addresses_1.user_id 
WHERE           users.mobile = %(mobile_1)s

标签： sqlalchemy