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 — Paddy Popeye
– Paddy Popeye, Commented Dec 20, 2019 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
– thebitsdontfit, Commented Dec 20, 2019 at 17:30

bfontaine · Accepted Answer · 2022-04-02 16:32:09Z

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 2, 2022 at 16:32

bfontaine

21.3k14 gold badges88 silver badges127 bronze badges

answered Dec 20, 2019 at 16:27

Andi Schroff

1,3741 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thebitsdontfit Over a year ago

Thanks Andi - this is an elegant piece of code that perfectly suits my needs. Many thanks.

Collectives™ on Stack Overflow

flask-sqlalchemy query using function of row values

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related