CODEWITHSUNDEEP
pythonflask-sqlalchemy
thebitsdontfit
thebitsdontfit

Reputation: 143

flask-sqlalchemy query using function of row values

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.

Upvotes: 2

Views: 1074

Answers (1)

Andi Schroff
Andi Schroff

Reputation: 1354

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

Upvotes: 2

Related Questions