Help to translate SQL query into ORM syntax

[kosta]

Hello everyone!

I've some trouble to construct right query by ORM, can someone help to construct appropriate subquery.

class Lesson(BaseMixin, TimeCreatedMixin, TimeUpdateMixin, Base):

users = relationship('User', secondary='lessonlist')

title = Column(String(128))

content = Column(Text)

order = Column(Integer, nullable=False, unique=True)

class LessonList(TimeCreatedMixin, TimeUpdateMixin, Base):

__tablename__ = 'lessonlist'

user_id = Column(UUID(as_uuid=True), ForeignKey('user.id'), primary_key=True)

lesson_id = Column(UUID(as_uuid=True), ForeignKey('lesson.id'), primary_key=True)

I need to get specific lesson model and count all passed lessons of specific user. My sql query looks like:

SELECT l.id, l."order", (SELECT COUNT(user_id) FROM (SELECT user_id FROM lessonlist WHERE user_id = %user_id) AS c)

FROM lesson l

WHERE l.id = %lesson_id;

Thanks in advance!

[kosta]

Looks like that my sql statement was not exactly correct..

I've solved my issue, but I'm still not sure that solved one correctly.

sq = session.query(db.LessonList.user_id, func.count('*').label('cnt')).group_by(db.LessonList.user_id).subquery()

session.query(db.Lesson, sq.c.cnt).filter_by(id=specific_lesson_id).outerjoin(sq, sq.c.user_id==some_user_id)