python - 按一对多的子行数对 Flask-SQLAlchemy 模型查询进行排序？

Question

我有一个产品型号:

class Product(db.Model):
    __tablename__ = 'product'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    offers = db.relationship('Offer', back_populates='product', lazy=True)

和报价模型:

class Offer(db.Model):
    __tablename__ = 'offer'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    product = db.relationship('Product', back_populates='offers')

我想知道如何根据每个产品的优惠数量订购产品列表。我希望能够做这样的事情:

sort = request.args.get('sort') # asc / desc
products = Product.query.order_by(len(Product.offers).sort).pagination([...])

我尝试这样做，但它只返回一个产品:

query = db.session.query(Product).join(Product.offers)
if order == 'desc':
    query = query.order_by(desc(func.count(Product.offers)))
else:
    query = query.order_by(func.count(Product.offers))
query = query.filter(Product.has_offers == True)
products = query.paginate(page=page, per_page=50)

我将使用它通过单击表头对 HTML 表格进行排序。我也在整理其他资料:

sort_by = dict(id=Product.id, mp_id=Product.marketplace_id,
                   updated=Product.last_seen, name=Product.name)
if sort_id in sort_by.keys():
    sort = sort_by[sort_id]
    sort = sort.desc() if order == 'desc' else sort.asc()
    query = query.order_by(sort)
query = query.filter(Product.has_offers == True)
products = query.paginate(page=page, per_page=50)

Answer 1

I tried doing this but it only gives me one Product:

您必须使用 MySQL 5.6 或更早版本，因为较新的版本不会接受该查询并引发错误:

ERROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query

请参阅 "What can an aggregate function do in the ORDER BY clause?" 中的答案了解为什么您会得到一个产品作为结果。

您最初的方法最终距离可行的解决方案并不遥远。您需要做的就是添加一个合适的 GROUP BY 子句，该子句在查询中生成确定性结果，使其正常工作:

order_op = desc if order == 'desc' else asc

query = db.session.query(Product).\
    join(Product.offers).\
    filter(Product.has_offers).\
    group_by(Product.id).\
    order_by(order_op(func.count()))

products = query.paginate(page=page, per_page=50)

选定的列是 determined by Product.id，这样您就不会得到不确定的结果。