Home sqlalchemy column_property and left joins
Reply: 0

sqlalchemy column_property and left joins

user2267
1#
user2267 Published in July 23, 2018, 9:50 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)

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

    @current_result.expression
    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(

        select([
            case([(exists().where(and_(
                student_id == TestResult.student_id,
                TestResult.current_result == True,
            )).correlate_except(TestResult), True)],
                 else_=False,
            )
        ])
    )
    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 3.49924 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO