title: Optimize ORM queries in Flask using Flask-SQLAlchemy
body: <p>I have written the same project using Django and Flask. The entire code is available on my Github account. The website is a small question-answer based quiz website (in CTF format with very easy questions).
  Here are the links:</p>
  
  <ul>
  <li><a href="https://github.com/diptangsu/cyber-hunt-django" rel="nofollow noreferrer">Django Project</a></li>
  <li><a href="https://github.com/diptangsu/cyber-hunt-flask" rel="nofollow noreferrer">Flask Project</a></li>
  </ul>
  
  <p>My question is regarding optimizing ORM queries in SQLAlchemy or Flask-SQLAlchemy.</p>
  
  <p>I'll try to write the schema of the tables as well as I can for better understanding.</p>
  
  <pre><code>Teams (id, team_name, email, phone, password)
  Questions (id, name, body, hint, answer, points, visible)
  Submissions (id, team(fk), question(fk), timestamp)
  </code></pre>
  
  <p>In case any of you want to see the actual code, here they are:<br>
  For Django - <a href="https://github.com/diptangsu/cyber-hunt-django/blob/master/questions/models.py" rel="nofollow noreferrer">Question &amp; Submission</a>, <a href="https://github.com/diptangsu/cyber-hunt-django/blob/master/teams/models.py" rel="nofollow noreferrer">Team</a><br>
  For Flask - <a href="https://github.com/diptangsu/cyber-hunt-flask/blob/2a6713873c/models/question.py" rel="nofollow noreferrer">Question</a>, <a href="https://github.com/diptangsu/cyber-hunt-flask/blob/2a6713873c/models/team.py" rel="nofollow noreferrer">Team</a>, <a href="https://github.com/diptangsu/cyber-hunt-flask/blob/2a6713873c/models/submission.py" rel="nofollow noreferrer">Submission</a></p>
  
  <p>For two of the routes, <code>/submissions</code> and <code>/leaderboard</code>, I had to write certain queries using the ORM. This is how the pages look like:
  <a href="https://i.stack.imgur.com/vHRGC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vHRGC.png" alt="leaderboard"></a>
  <a href="https://i.stack.imgur.com/z0sNY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/z0sNY.png" alt="submissions"></a></p>
  
  <p>For Django, the queries look pretty good (or at least I think so :P )</p>
  
  <pre><code>def submissions(request):
      all_submissions = Question.objects \
          .values('id', 'name') \
          .order_by('id') \
          .annotate(submissions=Count('submission'))
  
      print(all_submissions.query)
  
      return render(request, 'questions/submissions.html', {
          'submissions': all_submissions
      })
  
  def leaderboard(request):
      team_scores = Team.objects \
          .values('team_name') \
          .order_by('team_name') \
          .annotate(score=Coalesce(Sum('submission__question__points'), 0)) \
          .order_by('-score')
  
      print(team_scores.query)
  
      return render(request, 'questions/leaderboard.html', {
          'team_scores': team_scores,
      })
  </code></pre>
  
  <p>And the raw SQL queries look like this:</p>
  
  <pre><code>SELECT "teams_team"."team_name", COALESCE(SUM("questions_question"."points"), 0) AS "score" FROM "teams_team" LEFT OUTER JOIN "questions_submission" ON ("teams_team"."id" = "questions_submission"."team_id") LEFT OUTER JOIN "questions_question" ON ("questions_submission"."question_id" = "questions_question"."id") GROUP BY "teams_team"."team_name" ORDER BY "score" DESC
  
  SELECT "questions_question"."id", "questions_question"."name", COUNT("questions_submission"."id") AS "submissions" FROM "questions_question" LEFT OUTER JOIN "questions_submission" ON ("questions_question"."id" = "questions_submission"."question_id") GROUP BY "questions_question"."id", "questions_question"."name" ORDER BY "questions_question"."id" ASC
  </code></pre>
  
  <p>That was an awfully lengthy introduction to my question.</p>
  
  <p>My question starts here, I can't seem to write this or similar queries using the SQLAlchemy ORM, and PyCharm doesn't provide proper code completion/suggestions for the same.</p>
  
  <p>For Flask, these are what my functions look like:</p>
  
  <pre><code>def get_team_score(team):
      team_submissions = Submission.query.filter_by(team_id=team.id)
      score = sum(
          submission.question.points
          for submission in team_submissions
      )
  
      return score
  
  @question_blueprint.route('/submissions')
  def submissions():
      all_submissions = [
          {
              'id': q.id,
              'name': q.name,
              'submissions': Submission.query.filter_by(question_id=q.id).count()
          }
          for q in Question.get()  # fetch all Question rows
      ]
      return render_template('submissions.html', **{
          'submissions': all_submissions
      })
  
  @question_blueprint.route('/leaderboard')
  def leaderboard():
      team_scores = [
          {
              'team_name': team.team_name,
              'score': get_team_score(team)
          }
          for team in Team.query.filter_by()
      ]
      return render_template('leaderboard.html', **{
          'team_scores': team_scores
      })
  </code></pre>
  
  <p>The queries are not optimized and I would like to know if it's possible to write elegant queries like the django-orm without having to write raw SQL statements. And if possible, I would like to get some nicely optimized queries for these two routes mentioned in this question.</p>
  
  <p>Phew.</p>