Optimize ORM queries in Flask using Flask-SQLAlchemy

Question

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:

• Django Project
• Flask Project

My question is regarding optimizing ORM queries in SQLAlchemy or Flask-SQLAlchemy.

I'll try to write the schema of the tables as well as I can for better understanding.

Teams (id, team_name, email, phone, password)
Questions (id, name, body, hint, answer, points, visible)
Submissions (id, team(fk), question(fk), timestamp)

In case any of you want to see the actual code, here they are:
For Django - Question & Submission, Team
For Flask - Question, Team, Submission

For two of the routes, /submissions and /leaderboard, I had to write certain queries using the ORM. This is how the pages look like:

For Django, the queries look pretty good (or at least I think so :P )

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,
    })

And the raw SQL queries look like this:

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

That was an awfully lengthy introduction to my question.

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.

For Flask, these are what my functions look like:

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
    })

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.

Phew.

Answer 1

After going through Kelvin's answer, I sort of understand how I need to run queries in SQLAlchemy. I kept modifying Kelvin's answer to fit my needs and these are the queries that completely work.

@question_blueprint.route('/submissions')
def submissions():
    all_submissions = Question.query \
        .outerjoin(Submission) \
        .group_by(Question.id) \
        .order_by(Question.id) \
        .with_entities(
            Question.id,
            Question.name,
            func.count(Submission.id).label('submissions')) \
        .all()

    return render_template('submissions.html', **{
        'submissions': all_submissions
    })


@question_blueprint.route('/leaderboard')
def leaderboard():
    team_scores = Team.query \
        .outerjoin(Submission) \
        .outerjoin(Question) \
        .group_by(Team.team_name) \
        .with_entities(
            Team.team_name,
            func.coalesce(func.sum(Question.points), 0).label('score')) \
        .with_labels().all()

    return render_template('leaderboard.html', **{
        'team_scores': team_scores
    })

Answer 2

you could change your code above like thiis.

@question_blueprint.route('/leaderboard')
def leaderboard():
    team_scores =\
        Team.query.join(
            Submission,
        ).join(
            Question,
        ).with_entities(
            Team.team_name,
            func.sum(Question.points).label("score")
        ).all()

    return render_template('leaderboard.html', **{
        'team_scores': team_scores
    })

In this snippet below you join Team -> Submission -> Question, and then because you only need team name and total points based on question from each submission you do sum then you just need with_entities.

@question_blueprint.route('/submissions')
def submissions():
    all_submissions = Question.query.join(
        Submission
    ).with_entities(
        Question.id,
        Question.name,
        func.count().label("submissions")
    ).all()

    return render_template('submissions.html', **{
        'submissions': all_submissions
    })

To query question with submission yo do simillar query like i've added above but instead of calculate the sum you just want to count the row, so you just change it into func.sum