flask-sqlalchemy query using function of row values

Question

I have two tables, Student and Result, with a one to one relationship. The Student table will eventually contain full student profile information, and the Result table is maths and english test results for each student.

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    student_name = db.Column(db.String(80), nullable=False)
    result_id = db.Column(db.Integer, db.ForeignKey('result.id'), nullable=False)
    result = db.relationship('Result', backref=db.backref('students', lazy=True))


class Result(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    math_score = db.Column(db.Integer)
    english_score = db.Column(db.Integer)

I want to select the top 10 highest scoring students ordered by the sum of the two test scores. So something along the lines of:

Student.query.order_by(sum_of(maths_score, english_score)).limit(10).all()

I want to display the query result as three columns, student name, math score, english score, from highest to lowest score sum. I don't need the sum of the test scores to be output from the query. I am trying to avoid storing the sum of the test scores in the Result table.

I can't find any examples of how to perform such a flask-sqlalchemy query based on a function of row values.

Can anyone help shed some light on this please.

Does this answer your question? [Flask SQLAlchemy query function inside models](https://stackoverflow.com/questions/57842652/flask-sqlalchemy-query-function-inside-models) — Paddy Popeye, Dec 20 '19 at 10:41
Thanks. This got me digging a bit further using functions defined in the model but I still struggled to piece the full query together i the view. — thebitsdontfit, Dec 20 '19 at 17:30

score 2 · Accepted Answer · edited Apr 02 '22 at 16:32

2

You can use a query like this:

topten = db.session.query(Student.student_name, (Result.english_score + 
  Result.math_score).label("overall")).join(Result).order_by(desc("overall")).limit(10)

This creates output like this: [('Student 2', 12), ('Student 13', 12), ('Student 18', 12), ('Student 19', 10), ('Student 3', 9), ('Student 11', 9), ('Student 16', 9), ('Student 20', 9), ('Student 21', 9), ('Student 6', 8)].

if you have created students 10+ with random results (in the range from 1 to 6).

edited Apr 02 '22 at 16:32

bfontaine

18,169
13
73
107

answered Dec 20 '19 at 16:27

Andi Schroff

1,156
1
12
18

Thanks Andi - this is an elegant piece of code that perfectly suits my needs. Many thanks. – thebitsdontfit Dec 20 '19 at 17:31

flask-sqlalchemy query using function of row values

1 Answers1