我有四个表/类: Group
。Bank
,Question
和Survey
。调查有很多问题,属于银行的问题又属于一个集团。我有小组和调查(我是在Survey
的实例方法中,正在循环通过所有Group
实例(,并想知道两者都属于哪些问题。
class Group(db.Model):
id = db.Column(db.Integer, primary_key=True)
banks = db.relationship('Bank', backref='group')
class Bank(db.Model):
id = db.Column(db.Integer, primary_key=True)
group_id = db.Column(db.Integer, db.ForeignKey('group.id')
questions = db.relationship('Question', backref='bank', lazy='dynamic')
class Question(db.Model):
id = db.Column(db.Integer, primary_key=True)
survey_id = db.Column(db.Integer, db.ForeignKey('survey.id')
bank_id = db.Column(db.Integer, db.ForeignKey('bank.id')
class Survey(db.Model):
id = db.Column(db.Integer, primary_key=True)
questions = db.relationship('Question', backref='survey', lazy='dynamic')
我想到尝试类似self.questions.filter(Question.bank.in_(group.banks))
(self
是Survey
实例(,但有NotImplementedError
。现在,我使用的是if
条件的丑陋的for
循环,并且正在尝试清理它,尤其是因为我预计随着调查和问题的数量的增加而存在速度问题。
for group in groups:
for bank in group.banks:
for question in bank.questions:
if question in self.questions:
# do stuff
您可以使用连接来遍历关系获取所需的信息。
此查询将调查表加入其共享的外国问题ID密钥的银行表,然后将银行表加入组表。
q = (session.query(Survey.id, Question.id, Group.id)
.join(Survey.questions, Bank)
.join(Group)
.filter(Survey.id == self.id)
.all())
要获取一个查询中所有调查的信息,请删除filter
子句。
查询生成的SQL是
SELECT survey.id AS survey_id, question.id AS question_id, "group".id AS group_id
FROM survey
JOIN question ON survey.id = question.survey_id
JOIN bank ON bank.id = question.bank_id
JOIN "group" ON "group".id = bank.group_id
WHERE survey.id = ?