SQLAlchemy query returning count of ALL join rows, not grouping by joined row

Question

I'm building a CRUD application and trying to display a list of post "tags", with a number next to each of how many posts have used that tag, and ordered by the number of posts. I have one table for posts, one for tags, and one join table called posts_tags. When I execute the query I think should do the trick, it displays the count of all rows of the posts_tags table instead of just the count of rows associated with each tag. In the image below, the "test" tag has been used on 3 posts and "test 2" on 1 (which are the numbers that should show up next to them), but as you can see I get 4 instead:

display of incorrect post counts for tags

My tags table has a relationship with the posts_tags table, allowing me to use "Tag.tagged_post_ids" in the query:

`

class Tag(db.Model):
    """ Model for tags table """

    __tablename__ = "tags"

    id = db.Column(
        db.Integer,
        primary_key=True,
        autoincrement=True
    )

    tag = db.Column(
        db.String(30),
        nullable=False,
        unique=True
    )

    description = db.Column(
        db.Text,
        nullable=False
    )

    tagged_post_ids = db.relationship(
        "PostTag"
    )

`

Here's the SQLA query I wrote:

`

tags = db.session.query(Tag.tag, func.count(Tag.tagged_post_ids).label("count")).group_by(Tag.tag).order_by(func.count(Tag.tagged_post_ids)).all()

`

I have successfully built the query in SQL:

SELECT tags.tag, COUNT(posts_tags.post_id) FROM tags JOIN posts_tags ON posts_tags.tag_id = tags.id GROUP BY tags.tag ORDER BY COUNT(posts_tags.post_id) DESC;

My main issue is trying to translate this into SQLAlchemy. I feel like my query is a 1-to-1 for my SQL query, but it's not working! Any help would be greatly appreciated.

EDIT: Adding my Post model and PostTag (join) model:

class Post(db.Model):
    """ Model for posts table """

    __tablename__ = "posts"

    id = db.Column(
        db.Integer,
        primary_key=True,
        autoincrement=True
    )

    user_id = db.Column(
        db.Integer,
        db.ForeignKey("users.id")
    )

    title = db.Column(
        db.Text,
        nullable=False
    )

    content = db.Column(
        db.Text
    )

    url = db.Column(
        db.Text
    )

    img_url = db.Column(
        db.Text
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=db.func.now()
    )

    score = db.Column(
        db.Integer,
        nullable=False,
        default=0
    )

    tags = db.relationship(
        "Tag",
        secondary="posts_tags",
        backref="posts"
    )

    comments = db.relationship(
        "Comment",
        backref="post"
    )

    @property
    def tag_list(self):
        """ Builds comma separated list of tags for the post. """

        tag_list = []

        for tag in self.tags:
            tag_list.append(tag.tag)
        
        return tag_list

class PostTag(db.Model):
    """ Model for join table between posts and tags """

    __tablename__ = "posts_tags"

    post_id = db.Column(
        db.Integer,
        db.ForeignKey("posts.id"),
        primary_key=True
    )

    tag_id = db.Column(
        db.Integer,
        db.ForeignKey("tags.id"),
        primary_key=True
    )

Answer 1

If you are using backref you only need to define one side of the relationship. I actually don't know what happens when you use func.count on a relationship, I only use it on a column. Here are a couple options. An outer join is needed to catch the case when there are 0 posts with that tag otherwise with an inner join that tag will just be missing from the result. I also use func.coalesce to convert NULL to 0 in the first example.

class Tag(Base):
    """ Model for tags table """

    __tablename__ = "tags"

    id = Column(
        Integer,
        primary_key=True,
        autoincrement=True
    )

    tag = Column(
        String(30),
        nullable=False,
        unique=True
    )

# Redundant
#    tagged_post_ids = relationship(
#        "PostTag"
#    )


class Post(Base):
    """ Model for posts table """

    __tablename__ = "posts"

    id = Column(
        Integer,
        primary_key=True,
        autoincrement=True
    )


    title = Column(
        Text,
        nullable=False
    )

    tags = relationship(
        "Tag",
        secondary="posts_tags",
        backref="posts"
    )


    @property
    def tag_list(self):
        """ Builds comma separated list of tags for the post. """

        tag_list = []

        for tag in self.tags:
            tag_list.append(tag.tag)
        return tag_list

class PostTag(Base):
    """ Model for join table between posts and tags """

    __tablename__ = "posts_tags"

    post_id = Column(
        Integer,
        ForeignKey("posts.id"),
        primary_key=True
    )

    tag_id = Column(
        Integer,
        ForeignKey("tags.id"),
        primary_key=True
    )


metadata.create_all(engine)

with Session(engine) as session, session.begin():
    # With subquery
    tag_subq = select(
        PostTag.tag_id,
        func.count(PostTag.post_id).label("post_count")
    ).group_by(
        PostTag.tag_id
    ).order_by(
        func.count(PostTag.post_id)
    ).subquery()
    q = session.query(
        Tag.tag,
        func.coalesce(tag_subq.c.post_count, 0)
    ).outerjoin(
        tag_subq,
        Tag.id == tag_subq.c.tag_id
    ).order_by(
        func.coalesce(tag_subq.c.post_count, 0))
    for (tag_name, post_count) in q.all():
        print (tag_name, post_count)


    # With join
    q = session.query(
        Tag.tag,
        func.count(PostTag.post_id).label('post_count')
    ).outerjoin(
        PostTag,
        Tag.id == PostTag.tag_id
    ).group_by(
        Tag.id
    ).order_by(
        func.count(PostTag.post_id))
    for (tag_name, post_count) in q.all():
        print (tag_name, post_count)