带相关数据列表的KeyedTuple

Question

我有几个简单的模型看起来像这样：

class StoreImage(Base):
    imagepath = Column(Text, nullable=False)
    store_id = Column(Integer, ForeignKey('store.id'), nullable=False)
    store = relationship('Store')

class Store(Base):
    status = Column(Enum('published', 'verify', 'no_position',
                         name='store_status'),
                    nullable=False, default='verify')
    type = Column(Enum('physical', 'web', name='store_type'),
                  nullable=False, default='physical')
    name = Column(Text, nullable=False)
    street = Column(Text)
    postal_code = Column(Text)
    city = Column(Text)
    country = Column(Text)
    phone_number = Column(Text)
    email = Column(Text)
    website = Column(Text)
    location = Column(Geometry(geometry_type='POINT', srid=4326))
    is_flagship = Column(Boolean, default=False)
    images = relationship(StoreImage)

现在我要做的是这样的查询：

q = Store.query.filter(Store.is_flagship == True).with_entities(
    Store.id,
    Store.status,
    Store.slug,
    Store.type,
    Store.name,
    Store.street,
    Store.postal_code,
    Store.city,
    Store.country,
    Store.phone_number,
    Store.email,
    Store.website,
    func.ST_AsGeoJSON(Store.location).label('location'),
    Store.images,
)

查询可以工作，但是Store.images只为每一行返回True。如何使它返回StoreImage实例/KeyedTuples的列表？你知道吗

我想这样做主要是因为我还没有找到其他方法使Store.query以GeoJSON格式返回位置。你知道吗

编辑：对我来说，一个解决方案是只从查询返回Store实例，然后以某种方式添加locationGeoJSON，或者在声明的模型上，或者以其他可能的方式。但我不知道怎么做。你知道吗

Answer 1

您当前的查询不仅返回错误的值，而且实际上返回错误的行数，因为它将执行两个表的笛卡尔乘积。
我也不会覆盖列名location。所以我将在下面的代码中使用geo_location。你知道吗

您是对的，为了预加载图像，您必须查询整个Store实例。例如，如下查询：

q = (session.query(Store)
        .outerjoin(Store.images) # load images
        .options(contains_eager(Store.images)) # tell SA that we hav loaded them so that it will not perform another query
        .filter(Store.is_flagship == True)
    ).all()

要将两者结合起来，可以执行以下操作：

q = (session.query(Store, func.ST_AsGeoJSON(Store.location).label('geo_location'))
        .outerjoin(Store.images) # load images
        .options(contains_eager(Store.images)) # tell SA that we hav loaded them so that it will not perform another query
        .filter(Store.is_flagship == True)
    ).all()

# patch values in the instances of Store:
for store, geo_location in q:
    store.geo_location = geo_location

编辑-1:或者尝试使用^{}

class Store(...):
    # ...
    location_json = column_property(func.ST_AsGeoJSON(location).label('location_json'))

    q = (session.query(Store).label('geo_location'))
            .outerjoin(Store.images) # load images
            .options(contains_eager(Store.images)) # tell SA that we hav loaded them so that it will not perform another query
            .filter(Store.is_flagship == True)
        ).all()
    for store in q:
        print(q.location_json)
        print(len(q.images))