按一对多的子行数对 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

我尝试这样做，但它只给了我一个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

请参阅"聚合函数在 ORDER BY 子句中可以做什么？"中的答案，以解释为什么你会得到一个Product。

您的原始方法最终离可行的解决方案并不远。您需要做的就是添加一个合适的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)

所选列由Product.id确定，因此您不会得到不确定的结果。