Count across multiple association tables

description.md

Solution

I needed to use the as_scalar() method on the subquery, which turns the SELECT expression into a regular column:

a_q = db.session.query(func.count(tag_relationships['Article'].c.post_id)).select_from(tag_relationships['Article']).filter(tag_relationships['Article'].c.tag_id == Tag.id).subquery()
r_q = db.session.query(func.count(tag_relationships['Review'].c.post_id)).select_from(tag_relationships['Review']).filter(tag_relationships['Review'].c.tag_id == Tag.id).subquery()
db.session.query(Tag, a_q.as_scalar(), r_q.as_scalar()).group_by(Tag.id).order_by(Tag.handle).limit(10).all()

Problem Description

I use a mixin to add a many-to-many relationship between Tag objects (all stored in a single table) and any number of classes representing distinct types of posts.

I want to be able to query the count of each tag, grouped by the type of post. The raw SQL query in desired query.sql provides the desired output. However, no matter how I massage SQLAlchemy ORM, I can't seem to generate that SQL.

I have tried subqueries like so:

a_q = db.session.query(func.count(tag_relationships['Article'].c.post_id)).select_from(tag_relationships['Article']).filter(tag_relationships['Article'].c.tag_id == Tag.id).subquery()
r_q = db.session.query(func.count(tag_relationships['Review'].c.post_id)).select_from(tag_relationships['Review']).filter(tag_relationships['Review'].c.tag_id == Tag.id).subquery()
db.session.query(Tag, a_q, r_q).group_by(Tag.id).order_by(Tag.handle).limit(10).all()

which outputs:

SELECT tags.id AS tags_id, tags.handle AS tags_handle, tags.label AS tags_label, anon_1.count_1 AS anon_1_count_1, anon_2.count_2 AS anon_2_count_2 
FROM tags, (SELECT count(tag_associations.post_id) AS count_1 
FROM tag_associations, tags 
WHERE tag_associations.tag_id = tags.id) AS anon_1, (SELECT count(shelf_associations.post_id) AS count_2 
FROM shelf_associations, tags 
WHERE shelf_associations.tag_id = tags.id) AS anon_2 GROUP BY tags.id ORDER BY tags.handle

and does not result in the correct frequencies (I get tuples, 1 for each tag, with the same number repeated across every tuple).

desired query.sql

This query works:

SELECT tags.id AS tags_id, tags.handle AS tags_handle, tags.label AS tags_label, 
    (SELECT(count(*)) FROM tag_associations WHERE tag_associations.tag_id == tags.id) AS count_articles,
    (SELECT(count(*)) FROM shelf_associations WHERE shelf_associations.tag_id == tags.id) AS count_reviews
FROM tags GROUP BY tags.id ORDER BY tags.handle;

Sample output:

tags_id | tags_handle	 | tags_label	  | count_articles	| count_reviews
-----------------------------------------------------------------------
486	    | 19th-century | 19th century	|              0	|            31
291	    | mathematics  | mathematics	|              3	|             9

models.py

# Article and Review are classes that inherit from TagMixin, below, 
# which automatically creates a relationship using a dedicated 
# associations table.

tag_relationships = {
    'Article': db.Table(
        'tag_associations',
        db.Column('tag_id', db.Integer, db.ForeignKey('tags.id')),
        db.Column('post_id', db.Integer, db.ForeignKey('articles.id')),
    ),
    'Review': db.Table(
        'shelf_associations',
        db.Column('tag_id', db.Integer, db.ForeignKey('tags.id')),
        db.Column('post_id', db.Integer, db.ForeignKey('reviews.id')),
    ),
}


class TagMixin:
    """Any posts that are taggable."""
    @declared_attr
    def _tags(cls):
        return db.relationship(
            'Tag',
            secondary=tag_relationships[cls.__name__],
            backref=db.backref(cls.__tablename__, lazy='dynamic'),
        )
 

class Tag(db.Model):
    """Flexible categories for articles."""

    __tablename__ = 'tags'

    id = db.Column(db.Integer, primary_key=True)
    handle = db.Column(db.String(100), unique=True)
    label = db.Column(db.String(100))