Skip to main content

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


source https://stackoverflow.com/questions/76384590/sqlalchemy-count-of-records-in-the-relation-of-one-to-many

Comments