Flask SqlAlchemy get number of records from one-to-many relation?

Question

SQLite

There are two databases, one projects the other responses to them:

class Projects(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    publication_date = db.Column(db.DateTime, default=datetime.now(), onupdate=datetime.now())
    term = db.Column(db.Integer)
    title = db.Column(db.String(200))
    task = db.Column(db.Text)
    state = db.Column(db.Boolean)
    scope = db.Column(db.Integer, db.ForeignKey('spec.id'))
    status = db.Column(db.Integer, db.ForeignKey('order_status.id'), default=1)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    scope_admin = db.relationship('Spec', foreign_keys=[scope])
    status_admin = db.relationship('OrderStatus', foreign_keys=[status])

    def __repr__(self):
        return str(self.id)


class Bid(db.Model):
    id = db.Column(db.Integer, primary_key=True, unique=True, index=True)
    days = db.Column(db.Integer)
    message = db.Column(db.Text)
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    time = db.Column(db.DateTime)
    user = db.relationship('User', backref=db.backref('user_current', uselist=False))
    project = db.relationship('Projects', backref=db.backref('project_current', uselist=False))

    def __init__(self, days, message, project_id, user_id):
        self.days = days
        self.message = message
        self.project_id = project_id
        self.user_id = user_id
        self.time = datetime.now()

I'm getting my projects like this:

projects = Projects.query.order_by(Projects.id.desc()).paginate(page, POST_PER_PAGE, False)

Display:

{% for p in projects.items %}
    <div class="project_intro">
        <h2><a href="/project/{{ p.id }}">{{ p.title }}</a></h2>
        <span class="cat_date">{{ p.scope_admin }} | {{ p.publication_date.strftime('%d.%m.%y') }} в {{ p.publication_date.strftime('%H:%M') }} </span>
        <span class="time">{{ p.term }}</span>
        <span class="cat">{{ p.scope_admin }}</span>
        <span class="status {% if p.status == 2-%} in_work {%- endif -%}
                            {%- if p.status == 3 -%} work_end {%- endif -%}">{{ p.status_admin }}</span>
        <span class="freelansers">1 (+1)</span>
    </div>

    {% endfor %}

How can I get the number of responses?

I don’t see it expedient to pull the database cycle every time, I can’t think of another way out, maybe I need to make some kind of tricky request?

Answer 1

Decided by myself, in the simplest and most logical way

progects_id = []
    projects = Projects.query.order_by(Projects.id.desc()).paginate(page, POST_PER_PAGE, False)
    for i in projects.items:
        progects_id.append(i.id)
    bd = db.session.query(Bid).filter(Bid.project_id.in_(progects_id)).all()
    bids_num = dict()
    for l in progects_id:
        num = 0
        for k in bd:
            if k.project_id == l:
                num += 1
        bids_num[l] = num

Answer 2

subq = Session.query(Bid.project_id, func.count(Bid.id).label("count").group_by(Bid.project_id).subquery()
projects = Session.query(Project, func.coalesce(subq .c.count, 0)).outerjoin(subq, subq.c.project_id == Project.id)
This is the variant without relationships. As with them - xs, because I do not use it.
PS Sorry, I don't know how to put down tags in the mobile version :(