请问为什么我的标题列在group_by中提到,但为什么我会得到标题错误。以下是我的查询
products = session
.query(User) \
.join(User.products) \
.join(Product.productitems) \
.values( Product.title.label('title'),(func.count(ProductItem.id)).label('total')) \
.group_by(Product.id,Product.title) \
.order_by(Product.created_date)
模型类
class User(UserMixin , Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
title = Column(CHAR(3), nullable = True)
class Product(Base):
__tablename__ = 'products'
id = Column(Integer, primary_key =True)
title = Column(String(250), nullable = False)
..
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship('User',backref=backref("products", cascade="all, delete-orphan"),lazy='joined')
class ProductItem(Base):
__tablename__ ='product_items'
id = Column(Integer , primary_key = True)
...
product_id = Column(Integer, ForeignKey('products.id'))
product = relationship('Product',backref=backref("productitems", cascade="all, delete-orphan"),lazy='joined' )
从控制台,我可以看到查询返回
SELECT
products.title AS title
,COUNT(product_items.id) AS total
FROM
users
JOIN products ON users.id = products.user_id
JOIN product_items ON products.id = product_items.product_id
然后在group_by上出现错误而中断。请问它要什么?任何帮助,将不胜感激。
最佳答案
SQLalchemy要求values
是链中的最后一件事,因为它不返回查询继续。您需要做的可能是(未经测试的)。
products = session.query(User) \
.join(User.products) \
.join(Product.productitems) \
.group_by(Product.id, Product.title, Product.created_date) \
.order_by(Product.created_date) \
.values( Product.id.label('id'), \
Product.title.label('title'), \
(func.count(ProductItem.id)).label('total'))
关于python - 列 "Column"必须出现在GROUP BY子句中-SQLAlchemy,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/35412072/