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)
On Monday, August 12, 2019 at 10:17:00 PM UTC+3, kosta wrote:
>
> 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!
>
>
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/94993a51-657b-4ec6-8b17-aa7df3961863%40googlegroups.com.
