I have two models, Student and TestResult that are linked through a one-to-many relationship using the student_id on both tables. One student can have many test results, or (and this is where the problems begin...) can have no test results at all.
I have created a calculated column using @hybrid_property on TestResult to tell me which is the current result, and I have added another column to my Student model to tell me if that particular student has a current test result or not, ie Student.test_taken = True if they have taken a test today.
My problem lies with students that do NOT have a current_result. I have got it working with the code below, but this seems way over complicated for what would be in pure SQL, a super simple problem. I have tried rewriting as a coalesce but I can't get it to work as I expect, and tbh I am not even sure if I am going about this in the right way.
Can someone advise if I am approaching this correctly, and if not, help me find a better solution?
__tablename__ = 'testresult'
result_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
student_id = db.Column(db.ForeignKey('students.studentid'))
test_date = db.Column(db.Date)
test_result = db.Column(db.String)
if self.test_date = datetime.today:
(self.test_date = func.now(), True)
], else_ = False)
__tablename__ = 'student'
student_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
firstname = db.Column(db.String)
surname = db.Column(db.String)
class = db.Column(db.String)
test_taken = column_property(
student_id == TestResult.student_id,
TestResult.current_result == True,
test_results = relationship('TestResult', backref='student')