Number of related records in a many-to-many relationship

Question

I am trying to create a class that returns the number of members associated with a project. I tried:

# method of class Project @classmethod def member_count(cls, project_id): return Session.query(ProjectMember).\ filter(ProjectMember.project_id==project_id).count() 

A many-to-many relationship is defined as:

 class Member(Base): __tablename__ = 'member' id = Column(Integer, primary_key=True) login = Column(String(50), unique=True, nullable=False) project_list = relationship("ProjectMember", backref="member") class Project(Base): __tablename__ = 'project' id = Column(Integer, primary_key=True) name = Column(String(100), unique=True, nullable=False) class ProjectMember(Base): __tablename__ = 'project_member' project_id = Column(Integer, ForeignKey("project.id"), nullable=False, primary_key=True) member_id = Column(Integer, ForeignKey("member.id"), nullable=False, primary_key=True) project = relationship("Project", backref = "project_member") is_pm = Column(Boolean, default = True, nullable = False) UniqueConstraint('project_id', 'member_id') 

Thanks!

Answer 1

Use either a simple property as shown below, which will use the current instance session to get the number of children

 class Project(...): # ... @property def member_count_simple(self): return object_session(self).query(ProjectMember).with_parent(self).count() print(my_proj.member_count_simple) # @note: will issue an SQL statement 

or use the Hybrid Attribute :

 class Project(Base): # ... @hybrid_property def member_count(self): return object_session(self).query(ProjectMember).with_parent(self).count() @member_count.expression def _member_count_expression(cls): q = select([func.count(ProjectMember.project_id)]).\ where(ProjectMember.project_id == cls.id).\ label("member_count") return q 

... in this case, you can use this expression in your query ( filter and order_by , for example):

 qry = (session.query(Project, Project.member_count) .order_by(Project.member_count.desc()) ) # @note: it returns tuples (Project, member_count) for (proj, cnt_mem) in qry.all(): print(proj.name, cnt_mem)