sqlalchemy-count-property.py

class UserProduct(Base):
    user_id = Column(BigInteger, ForeignKey('user.user_id'),
                     primary_key=True)
    product_id = Column(BigInteger, ForeignKey('product.product_id'),
                        primary_key=True)
                        
                        
class Product(Base):
    product_id = Column(BigInteger, primary_key=True)

    # this is what we need
    from sqlalchemy import func, select, and_
    from sqlalchemy.orm import column_property
    library_users_count = column_property(
        select([func.count(UserProduct.user_id)])\
        .where(and_(UserProduct.product_id == product_id,
                    UserProduct.wishlist == False))
    )

# In [1]: p = get_product(db,51)    

# SELECT (SELECT count(user_product.user_id) AS count_1
# FROM user_product
# WHERE user_product.product_id = product.product_id AND user_product.wishlist = false) AS anon_1, product.product_id AS product_product_id, product.user_id AS product_user_id, product.company_id AS product_company_id, product.name AS product_name, product.url AS product_url, product.tagline AS product_tagline, product.topic AS product_topic, product.age_from AS product_age_from, product.age_to AS product_age_to, product.author_name AS product_author_name, product.description AS product_description, product.sell_product AS product_sell_product, product.price AS product_price, product.grade_from_id AS product_grade_from_id, product.grade_to_id AS product_grade_to_id, product.pricing_model_id AS product_pricing_model_id, product.integration_compliance_id AS product_integration_compliance_id, product.target_market_id AS product_target_market_id, product.public AS product_public, product.completed AS product_completed, product.archived AS product_archived, product.avg_rating AS product_avg_rating, product.avg_ease_of_use AS product_avg_ease_of_use, product.avg_fit_to_standards AS product_avg_fit_to_standards