这是我的场景。我有一个Books
表,每本书都有一个Questions
表,每个问题都有一个Answers
表。
我想做的是有效地获得给定Question.id
列表的答案数量。例:
# 6,000 Question.id's for Book.id == 1
questions = [0, 1, 2, 3, 4, 5, 6, ..., 6000]
这是我正在做的事情,它被证明是非常非常低效的:
# This takes a couple minutes for it to finish
count = 0
query = QuestionModel.query.filter(QuestionModel.id.in_(questions)).all()
for q in query:
count += len(list(q.Answers))
# count = 3456
我假设这很慢,因为q.Answers
实际上正在填充数据。
这是我的模型:
class BookModel(db.base):
__tablename__ = 'books_book'
__table_args__ = {
'autoload': True,
'extend_existing': True,
'autoload_with': db.instance.engine,
}
id = Column(Integer, primary_key=True)
Chapters = relationship(ChapterModel)
QuestionMeta = relationship(QuestionMetaModel)
class QuestionMetaModel(db.base):
__tablename__ = 'questions_questionmeta'
__table_args__ = {
'autoload': True,
'extend_existing': True,
'autoload_with': db.instance.engine,
}
id = Column(Integer, primary_key=True)
question_id = Column(ForeignKey('questions_question.id'))
book_id = Column(ForeignKey('books_book.id'))
chapter_id = Column(ForeignKey('books_chapter.id'))
class QuestionModel(db.base):
__tablename__ = 'questions_question'
__table_args__ = {
'autoload': True,
'extend_existing': True,
'autoload_with': db.instance.engine,
}
id = Column(Integer, primary_key=True)
Answers = relationship(AnswerModel)
class AnswerModel(db.base):
__tablename__ = 'answers_answer'
__table_args__ = {
'autoload': True,
'extend_existing': True,
'autoload_with': db.instance.engine,
}
id = Column(Integer, primary_key=True)
question_id = Column(ForeignKey('questions_question.id'))
问题:我想要的只是QuestionModel.Answers
中的条目数量,而不是实际数据本身。我将如何做到这一点,所以一个Book.id
不需要 2 分钟?抓住每本书的Question.id
很快,但抓住每本书的答案数量非常慢Question.id
。
跨BookModel
、QuestionModel
和AnswerModel
执行join
,并使用 SQLAlchemy func.count
。
from sqlalchemy import func
count_query = (session.query(BookModel, QuestionModel, AnswerModel, func.count(AnswerModel.id))
.select_from(AnswerModel)
.join(QuestionModel)
.join(BookModel)
.group_by(BookModel)
)