CODEWITHSUNDEEP
pythonsqlalchemy
Jesse Aldridge
Jesse Aldridge

Reputation: 8149

SQLAlchemy: Further filter query with label

I have a query that yields an id, followed by a count. I want to get only those rows which have a count of N. I tried the code below but I get 'error: column "cert_count" does not exist'. I guess I'm using the label wrong?

cust_cert_counts = db.session.query(
    CustomerCertAssociation.customer_id,
    func.count(CustomerCertAssociation.certification_id).label('cert_count')).filter(
    CustomerCertAssociation.certification_id.in_(cert_ids)).group_by(
    CustomerCertAssociation.customer_id)
cust_cert_counts.filter('cert_count=2').all()

Upvotes: 4

Views: 5250

Answers (1)

univerio
univerio

Reputation: 20528

You can't filter on an aliased column like this:

SELECT 1 AS foo WHERE foo = 1;

You have to nest it in a subquery:

SELECT * FROM (SELECT 1 AS foo) bar WHERE bar.foo = 1;

For your particular example, you have to do:

subq = cust_cert_counts.subquery()
db.session.query(subq).filter(subq.c.cert_count == 2)

Upvotes: 5

Related Questions