Flask SQLAlchemy模型中由关系字段创建的数据库字段

Question

假设有一个国家，这个国家里面有州，每个州里有城市，每个城市里有学校，每所学校里有学生。

如果我的数据库是这样设置的，并且在学校的记录里有一个字段叫做goodOrBadStudent（表示学生是好学生还是坏学生），那么有没有简单的方法来计算每个表里有多少个好学生呢？

比如说，国家的好学生数量是y，州的好学生数量是x，依此类推……？

这些是我的真实数据，但想法是一样的。

class Projects(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    total = db.Column(db.Integer)
    percentDeadline = db.Column(db.Integer)
    percent = db.Column(db.Integer)
    name = db.Column(db.String(50))
    goals = db.relationship('Goals', backref='proj',
                                lazy='dynamic')
    def __repr__(self):
        return '<Project %r>' % (self.name)

class Goals(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    goal = db.Column(db.String(50))
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'))
    strategies = db.relationship('Strategies', backref='goa',
                                lazy='dynamic')

    def __repr__(self):
        return '<Goals %r>' % (self.goal)
class Strategies(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    strategy = db.Column(db.String(50))
    goal_id = db.Column(db.Integer, db.ForeignKey('goals.id'))
    tasks = db.relationship('Tasks', backref='strat',
                                lazy='dynamic')
    def __repr__(self):
        return '<Strategy %r>' % (self.strategy)

class Tasks(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    task = db.Column(db.String(50))
    note = db.Column(db.String(400))  
    complete = db.Column(db.Boolean())
    staff = db.Column(db.String(30))
    deadline = db.Column(db.Date)
    completeDate = db.Column(db.Date)
    created = db.Column(db.Date)
    strategy_id = db.Column(db.Integer, db.ForeignKey('strategies.id'))

    def __repr__(self):
        return '<Tasks %r>' % (self.task)

那么我应该在哪里进行这个计算呢？我希望不是在视图里，因为那样会很麻烦。

这是我开始计算百分比的方法，但可能更好的是传递x、y和总数。

P=models.Projects.query.all()
for p in P:  
    y=0     
    x=0
    total=0
    project=models.Projects.query.filter_by(name=p.name).first() 
    G=project.goals.all()
    for g in G:
        pgoal=models.Goals.query.filter_by(goal=g.goal).first() 
        S=pgoal.strategies.all()
        for s in S:
            pstrat=models.Strategies.query.filter_by(strategy=s.strategy).first() 
            T=pstrat.tasks.all()
            for t in T:
                if t.completeDate != None and t.deadline != None:
                    deadlineDiff= (t.deadline - t.completeDate) > datetime.timedelta(days=0)
                    print deadlineDiff, ' this is the deadlineDiff variable for ',t.task
                    if deadlineDiff==True:
                        y+=1
                total+=1
                if t.complete==True:
                    x+=1
    y=float(y*1.00)
    x=float(x*1.00)
    total=float(total*1.00)
    if x == 0 or total ==0:
        p.percent=0
        db.session.commit()
    else:
        p.percent=float(x/total*100.0)
        db.session.commit()   
    if y == 0 or x ==0:
        p.percentDeadline=0
        db.session.commit()
    else:
        p.percentDeadline=float(y/x*100.0)
        db.session.commit()     

我想我应该在每个层级记录x、y和总数吧？

Answer 1

在我看来，这种计算最好直接在SQL那边进行。下面这个用sqlalchemy写的查询会返回一个包含tuple的列表，格式是(project_id, x, y, total)，这些在你的示例代码中都有定义。只要你的数据库里有合适的索引，这个查询应该会非常快，你可以用它实时计算每个项目的进度，甚至不需要把结果存储在数据库里。

q_sum = (session.query(
            Projects.id.label("project_id"),
            func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"),
            func.sum(case([(and_(
                Tasks.deadline != None,
                Tasks.completeDate != None,
                Tasks.deadline > Tasks.completeDate), 1)],
                else_=0)).label("y"),
            func.count(Tasks.id).label("total"),
            )
        .join(Goals, Projects.goals)
        .join(Strategies, Goals.strategies)
        .join(Tasks, Strategies.tasks)
        .group_by(Projects.id)
        )

# (project_id, x, y, total)
for p in q_sum:
    print(p)

如果你只想获取特定项目的数据，只需在查询中添加.filter(Project.id == my_project_id)。