postgresql 子查询sqlalchemy中的Limit和order_by

xwmevbvl 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(258)

我试图在fastapi端点中检索一个pydantic模型的列表，该列表包含Subcategory sqlalchemy模型的一些属性，以及一个与Commerce sqlalchemy模型匹配的ShowCommerceToSubcategory pydantic模型的列表。我希望能够对内部Commerce查询的结果进行限制和排序。
到目前为止，我在我的Category模型中有一个class方法，它有一个非常简单的查询，它接受一个特定的category id并检索所有相关的子category：

@classmethod
def get_subcategory_commerce(cls, id: int, db: Session, number_of_commerces: int = 5):
    subcategories = db.query(Subcategory)\
        .filter(Subcategory.main_category_id == id)\
        .all()

我的响应模型看起来像这样：

class ShowSubcategoryCommerce(SQLModel):
    id: int
    main_category_id: int
    name: str
    description: str | None
    icon: str | None
    commerces: list[ShowCommerceToSubcategory]

    class Config:
        orm_mode = True

当我使用上面描述的类方法时，我得到了一些预期的结果。然而，我希望能够将ShowCommerceToSubcategory模型的结果限制为5，并通过我的Commerce sqlalchemy模型所具有的属性以后代顺序显示它们，该属性称为重要性。
我尝试过这个查询来实现这个目标，但是它失败了，并且没有显示相应子类别中的所有商业：

subq = db.query(Commerce)\
    .join(Commerce.subcategories)\
    .order_by(Subcategory.id, Commerce.importance.desc())\
    .limit(number_of_commerces)\
    .subquery()

subcategories = db.query(Subcategory)\
    .filter(Subcategory.main_category_id == id)\
    .outerjoin(subq, Subcategory.commerces)\
    .options(contains_eager(Subcategory.commerces, alias=subq))\
    .order_by(Subcategory.id, subq.c.importance.desc())\
    .all()

请注意，Commerce和Subcategory表/模型之间存在多对多关系

postgresql

来源：https://stackoverflow.com/questions/76503314/limit-and-order-by-in-subquery-sqlalchemy

1条答案

按热度按时间

von4xj4u1#

您遇到的问题可能是由于SQL在子查询中处理LIMIT的方式造成的。在子查询中指定LIMIT时，它限制返回的Commerce对象的总数，而不是每个子类别的Commerce对象的数量。因此，如果您有多个Subcategory对象，则当前查询可能会为一个Subcategory返回所有Commerce对象，而不为其他对象返回任何对象。
要获得想要的结果，您需要使用窗口函数对每个子类别中的Commerce对象进行排名，然后根据排名进行筛选。在SQLAlchemy中，您可以使用func.row_number()来执行此操作。
下面是一个粗略的例子，说明如何调整查询：

from sqlalchemy import func, desc

# Create a window function that ranks Commerce objects within each Subcategory
window = func.row_number().over(
    partition_by=Subcategory.id, order_by=Commerce.importance.desc()
).label("rn")

# Create a subquery that includes the rank
subq = (
    db.query(Commerce, window)
    .join(Commerce.subcategories)
    .filter(Subcategory.main_category_id == id)
    .subquery()
)

# Query for Subcategory objects and join with the subquery, filtering based on the rank
subcategories = (
    db.query(Subcategory)
    .outerjoin(subq, Subcategory.commerces)
    .options(contains_eager(Subcategory.commerces, alias=subq))
    .filter(subq.c.rn <= number_of_commerces)
    .order_by(Subcategory.id, subq.c.importance.desc())
    .all()
)

这段代码根据importance对每个Subcategory中的Commerce对象进行排名，然后过滤掉不属于顶部number_of_commerces的Commerce对象。这样，您应该为每个Subcategory获取顶部的number_of_commerces、Commerce对象。
您可能需要调整代码以适应您的模型定义/关系，因为我没有您的模型的完整上下文。

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql 子查询sqlalchemy中的Limit和order_by

1条答案

相关问题

热门标签

最新问答