Sqlalchemy count of records in the relation of one to many

Help me figure out what I'm doing wrong?

I need to get the number of comments in 24 hours in the relation one-to-many table.

class Comment(Base):
    __tablename__ = 'comments'
    id = Column(Integer, primary_key=True)
    task_id = Column(Integer(), ForeignKey('tasks.id'), nullable=False)
    post_link = Column(String, nullable=False)
    date = Column(DateTime, default=datetime.datetime.utcnow())

    def __init__(self, task_id: int, post_link: str):
        super().__init__()
        self.task_id = task_id
        self.post_link = post_link

    def __repr__(self):
        return f'id - {self.id} | task_id - {self.task_id} | date - {self.date}'

class Task(Base):
    __tablename__ = 'tasks'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    comments = relationship('Comment', backref='tasks', lazy=True)

    def __init__(self, name: str):
        super().__init__()
        self.name = name

    def __repr__(self):
        return f'id - {self.id} | name - {self.name}'

I don't know if the query works correctly or if it even outputs the number of records.

Here's the request itself:

async def get_comments_for_day():
    start_day = datetime.utcnow() - timedelta(hours=23, minutes=50)
    async with get_async_session() as session:
        stmt = select(Comment.task_id, func.count(Comment.task_id).label('comments_found'))\
            .where(Comment.date >= start_day).subquery()
        main_stmt = select(Task, stmt.c.comments_found).outerjoin(stmt, Task.id == stmt.c.task_id)
        results = await session.execute(main_stmt)
        return results.scalars().all()

async def main():
    tasks = await get_comments_for_day()
    for task, comments_found in tasks:
        print(task.name, comments_found)

I get this error:

    for task, comments_found in tasks:
TypeError: cannot unpack non-iterable Task object

You are iterating over tasks, each task, but the thing is each task is a tuple of Task, count, not just a Task object.

Just change the print statement to reference the correct index

edit: lets try to directly specify the attributes in the query, such as id and name and also we will group_by() to group the results by task and then count the comments for each

async def get_comments_for_day():
    start_day = datetime.utcnow() - timedelta(hours=23, minutes=50)
    async with get_async_session() as session:
        stmt = (
            select(Task.id, Task.name, func.count(Comment.id).label('comments_found'))
            .select_from(Task)
            .join(Comment, Task.id == Comment.task_id, isouter=True)
            .where(Comment.date >= start_day)
            .group_by(Task.id, Task.name)
        )
        result = await session.execute(stmt)
        return result.scalars().all()

async def main():
    tasks = await get_comments_for_day()
    for task_id, task_name, comments_found in tasks:
        print(task_name, comments_found)

async def get_comments_for_day():
    start_day = datetime.utcnow() - timedelta(hours=24)
    async with get_async_session() as session:
        stmt = (
            select(func.count(Comment.id), Task)
            .select_from(Task)
            .join(Comment, Task.id == Comment.task_id, isouter=True)
            .where(Comment.date >= start_day)
            .group_by(Task.id, Task.name)
        )
        results = await session.execute(stmt)
        return results.all()

If you're interested, I found a solution to the problem, which was to write return results.all() instead of return results.execute().all().