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