Home sqlalchemy column_property and left joins
Reply: 0

sqlalchemy column_property and left joins

user2267 Published in March 23, 2018, 7:06 am

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?

class TestResult(db.Model):
    __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)

    def current_result(self):
        if self.test_date = datetime.today:
            return True
            return False

    def current_result(self):
        return case([
            (self.test_date = func.now(), True)
        ], else_ = False)

class Student(db.Model):
    __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,
            )).correlate_except(TestResult), True)],
    test_results = relationship('TestResult', backref='student')
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.304144 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO