Convert PostgreSQL COUNT … FILTER query to SQL Alchemy

Question

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

Answer 1

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)]