CODEWITHSUNDEEP
pythonflask-sqlalchemy
The Welsh Dragon
The Welsh Dragon

Reputation: 529

Flask Sqlalchemy ORM how to add a property to return the count of child

This has been driving me crazy and I'm sure it is more straight forward than I'm making it. But so far, no end of searching and trying various combinations, has failed to get me anywhere.

I'm using Flask and SQLAlchemy and I'm new to both.. So, if i have two classes/tables defined like this

class Child(db.Modal):
    __tablename__ = 'children'
    id = db.Column(db.Integer, primary_key=True)
    kidsname = db.Column(db.String(20))
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'), nullable=False)

class Parent(db.Model):
    __tablename__ = 'parent'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    children = db.relationship('Child', backref='parent', lazy=True)

What I want is to be able to add a property to the parent class to return the number of children e.g.

def child_count(self):
    return ????

I would like to be able to do something like this when an instance of parent is passed though to my html template..

<p>Parent: {{ parent.name }}  # of children: {{parent.child_count}}</p>

Any help or pointer in the right direction would be greatly appreciated..

Upvotes: 2

Views: 1577

Answers (1)

Mikhail Sidorov
Mikhail Sidorov

Reputation: 1434

You can use column_property https://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html

from sqlalchemy.orm import column_property
from sqlalchemy import select, func


class Parent(db.Model):
    __tablename__ = 'parent'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    children = db.relationship('Child', backref='parent', lazy=True)
    child_count = column_property(select([func.count(children.id)]).\
        where(children.parent_id==id))

Also, you can find a solution to your problem with the hybrid_property in this question SQLAlchemy - Writing a hybrid method for child count

Upvotes: 1

Related Questions