How to add count condition on relationship in hybrid property expression?

user_6560773 0
Viewed 36

I have these models as an example:

class Parent(db.Model):
    __tablename__ = 'parents'

    parent_id = db.Column(db.Integer, primary_key=True)
    children = db.relationship('Child', lazy='dynamic')

class Child(db.Model):
    __tablename__ = 'children'

    child_id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.ForeignKey("parents.parent_id"))

Now I'd like to create a hybrid property expression for the Parent model so I can query on it later:

@hybrid_property
def is_parent_of_multiple_children(self):
    return self.children.count() > 2

@is_parent_of_multiple_children.expression     
def is_parent_of_multiple_children(cls):
  return (
      ???
  )

I reckon this is how I do it in native SQL (MySQL in my case):

select parents.parent_id as ID
from children join parents on children.parent_id = parents.parent_id
group by ID
having count(ID) > 2

Should I implement this with sqlalchemy.select and do the join the same way? I have cls.children which would suggest I have some way to use func.count or something similar instead of implementing tedious native SQL logic.

sqlalchemy
0 Answers
Related