I’m new to SQLAlchemy, and I would like to convert this PostgreSQL query:
SELECT product.* , COUNT(feedback.like) FILTER (WHERE feedback.like = '1') AS like , COUNT(feedback.like) FILTER (WHERE feedback.like = '-1') AS unlike FROM feedback, product WHERE product.id = feedback.product_id GROUP BY product.id ORDER BY product.id;
I have already tried this:
products = db.session.query( Product, func.count(Feedback.like > 0).label('like'), func.count(Feedback.like < 0).label('unlike') ).filter(Product.guide_name_id==id) .filter(Product.id == Feedback.product_id) .group_by(Product.id) .order_by(Product.id) .all()
Thank you in advance for your help
Advertisement
Answer
Thanks to @IljaEverilä’s comment, here is a more direct answer:
class Product(Base): __tablename__ = "product" id = Column(Integer, primary_key=True) name = Column(String(50), nullable=False) def __repr__(self): return f"<Product(name='{self.name}')>" class Feedback(Base): __tablename__ = "feedback" id = Column(Integer, primary_key=True) product_id = Column(Integer, ForeignKey(Product.id)) like = Column(Integer) product = relationship(Product) Base.metadata.create_all(engine) with Session(engine) as session: # set up test data widget = Product(name="widget") session.add_all( [ widget, Feedback(product=widget, like=1), Feedback(product=widget, like=1), Feedback(product=widget, like=-1), Product(name="gadget"), ] ) # run the query query = ( select( Product, func.count(Feedback.like) .filter(Feedback.like == 1) .label("likes"), func.count(Feedback.like) .filter(Feedback.like == -1) .label("dislikes"), ) .select_from(Product) .outerjoin(Feedback) .group_by(Product) ) results = session.execute(query).fetchall() print(results) # [(<Product(name='gadget')>, 0, 0), (<Product(name='widget')>, 2, 1)]
(Original answer)
I’m not sure if SQLAlchemy’s postgresql
dialect specifically handles COUNT … FILTER
, but you can accomplish the same thing using SUM
and CASE
:
from sqlalchemy import __version__ as sa_version, case, Column, ForeignKey, func, Integer, String from sqlalchemy.orm import Session print(sa_version) # 1.4.0b2 class Product(Base): __tablename__ = "product" id = Column(Integer, primary_key=True) name = Column(String(50), nullable=False) class Feedback(Base): __tablename__ = "feedback" id = Column(Integer, primary_key=True) product_id = Column(Integer, ForeignKey(Product.id)) like = Column(Integer) product = relationship(Product) Base.metadata.create_all(engine) with Session(engine, future=True) as session: widget = Product(name="widget") session.add_all( [ widget, Feedback(product=widget, like=1), Feedback(product=widget, like=1), Feedback(product=widget, like=-1), Product(name="gadget"), ] ) results = ( session.query( Product.name, func.sum(case((Feedback.like > 0, 1), else_=0)).label( "likes" ), func.sum(case((Feedback.like < 0, 1), else_=0)).label( "dislikes" ), ) .select_from(Product) .outerjoin(Feedback) .group_by(Product) .all() ) print(results) # [('widget', 2, 1), ('gadget', 0, 0)]