SQLAlchemy - 为子计数写一个混合方法 [英] SQLAlchemy - Writing a hybrid method for child count
问题描述
我正在使用Flask-SQLAlchemy,并且正在尝试编写一个混合方法在父模型中返回它所拥有的子类的数量,所以我可以使用它来进行过滤,排序等。下面是一些我正在尝试的代码:
#parent.py
from program.extensions import db
from sqlalchemy.ext.hybrid import hybrid_method
class父(db.Model):
__tablename__ ='parents'
parent_id = db.Column(db.Integer,primary_key = True)
name = db.Column(db.String (80))
children = db.relationship('Child',backref ='parent',lazy ='dynamic')
def __init __(self,name):
self.name = name
@hybrid_method
def child_count(self):
return self.children.count()
@ child_count.expression
def child_count(cls):
return ?????
#child.py
from program.extensions import db $ b $ from program.models import父
$ b $ Child(db.Model):
__tablename__ ='children'
child_id = db.Column(db.Integer,primary_key = True)
parent_id = db.Column(db.Integer,db.ForeignKey(Parent.parent_id))
$ b $ _ $ __init __(self,name,time) :
self.name = name
self.time = time
这里遇到两个问题首先,我不知道在child_count(cls)中返回的是什么,它必须是一个SQL表达式,我认为它应该是类似于
return select([func.count('*'),from_obj = Child)。(Child.parent_id == cls.parent_id).label('Child count')
但我不确定。另一个问题是我无法从parent.py中导入Child类,所以我无法使用该代码。有没有什么办法使用这个字符串?例如,
select([func.count('*'),from_obj ='children')。 .parent_id == parents.parent_id')。label('Child count')
最后,我们希望将方法更改为如下所示:
$ p $ def child_count(cls,start_time,end_time):
#return date参数介于start_time和end_time
之间的子元素的数量
...但是现在,我只是想让这个工作。非常感谢谁能帮助我,因为我一直试图弄清楚这个很长一段时间。
解决方案
class Parent(Base):
__tablename__ ='parents'
#...
@hybrid_property
def child_count(self):
#return len(self.children)#@note:非动态关系时使用
返回self.children.count()#@note:动态关系时使用
@ child_count.expression
def child_count(cls):
return(select([func。 count(Child.child_id)])。
where(Child.parent_id == cls.parent_id)。
label(child_count)
)
@hybrid_method
def child_count_ex(self,stime,etime):
return len([_ child为self.children中的_child为
,如果stime <= _child.time <= etime])
@child_count _ex.expression
def child_count_ex(cls,stime,etime):
return(select([func.count(Child.child_id)])。
where(Child.parent_id == cls.parent_id)。
where(Child.time> = stime)。
where(Child.time< = etime)。
标签(child_count)
)
#使用表达式:
stime,etime = datetime.datetime(2012,1,1) ,datetime.datetime(2012,1,31)
qry = session.query(Parent)
#qry = qry.filter(Parent.child_count> 2)
qry = qry.filter (Parent.child_count_ex(stime,etime)> 0)
I'm using Flask-SQLAlchemy, and I'm trying to write a hybrid method in a parent model that returns the number of children it has, so I can use it for filtering, sorting, etc. Here's some stripped down code of what I'm trying:
# parent.py
from program.extensions import db
from sqlalchemy.ext.hybrid import hybrid_method
class Parent(db.Model):
__tablename__ = 'parents'
parent_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
children = db.relationship('Child', backref='parent', lazy='dynamic')
def __init__(self, name):
self.name = name
@hybrid_method
def child_count(self):
return self.children.count()
@child_count.expression
def child_count(cls):
return ?????
# child.py
from program.extensions import db
from program.models import Parent
class Child(db.Model):
__tablename__ = 'children'
child_id = db.Column(db.Integer, primary_key=True)
parent_id = db.Column(db.Integer, db.ForeignKey(Parent.parent_id))
name = db.Column(db.String(80))
time = db.Column(db.DateTime)
def __init__(self, name, time):
self.name = name
self.time = time
I'm running into two problems here. For one, I don't know what exactly to return in the "child_count(cls)", which has to be an SQL expression... I think it should be something like
return select([func.count('*'), from_obj=Child).where(Child.parent_id==cls.parent_id).label('Child count')
but I'm not sure. Another issue I have is that I can't import the Child class from parent.py, so I couldn't use that code anyway. Is there any way to use a string for this? For example,
select([func.count('*'), from_obj='children').where('children.parent_id==parents.parent_id').label('Child count')
Eventually, I'll want to change the method to something like:
def child_count(cls, start_time, end_time):
# return the number of children whose "date" parameter is between start_time and end_time
...but for now, I'm just trying to get this to work. Huge thanks to whoever can help me with this, as I've been trying to figure this out for a long time now.
The code below shows it all.
class Parent(Base):
__tablename__ = 'parents'
# ...
@hybrid_property
def child_count(self):
#return len(self.children) # @note: use when non-dynamic relationship
return self.children.count()# @note: use when dynamic relationship
@child_count.expression
def child_count(cls):
return (select([func.count(Child.child_id)]).
where(Child.parent_id == cls.parent_id).
label("child_count")
)
@hybrid_method
def child_count_ex(self, stime, etime):
return len([_child for _child in self.children
if stime <= _child.time <= etime ])
@child_count_ex.expression
def child_count_ex(cls, stime, etime):
return (select([func.count(Child.child_id)]).
where(Child.parent_id == cls.parent_id).
where(Child.time >= stime).
where(Child.time <= etime).
label("child_count")
)
# usage of expressions:
stime, etime = datetime.datetime(2012, 1, 1), datetime.datetime(2012, 1, 31)
qry = session.query(Parent)
#qry = qry.filter(Parent.child_count > 2)
qry = qry.filter(Parent.child_count_ex(stime, etime) > 0)
这篇关于SQLAlchemy - 为子计数写一个混合方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!