Here's my scenario. I have a table of Books
each book has a table of Questions
and each question has a table of Answers
.
What I want to do is efficiently get the amount of answers for a given list of Question.id
. Example:
# 6,000 Question.id's for Book.id == 1
questions = [0, 1, 2, 3, 4, 5, 6, ..., 6000]
Here's what I'm doing and its proving to be very very inefficient:
# 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
I'm assuming this is so slow because q.Answers
is actually getting populated with data.
Here are my models:
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'))
Question: All I want is the amount of entries in QuestionModel.Answers
not the actual data itself. How would I accomplish this, so it doesn't take 2 minutes for one Book.id
? Grabbing the Question.id
for each book is fast, but grabbing the amount of answers for each Question.id
is very slow.
Perform a
join
acrossBookModel
,QuestionModel
, andAnswerModel
and use SQLAlchemyfunc.count
.