Flask SQLAlchemy query from heirarchy

Question

I'm using Flask and SQLAlchemy with marshmallow, and trying to work my head around something. If I have several models (tables) that work using Foreign Keys in a heirarchy, how can I properly query a result from the furthest child?

Eg:

class School(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name =  db.Column(db.String(50))

class Course(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    school_id = db.Column(db.Integer,db.ForeignKey('school.id'))
    school = db.relationship("School", backref=db.backref("schoola", uselist=False))

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(60))
    course_id = db.Column(db.Integer, db.ForeignKey('course.id'))
    course = db.relationship("Course", backref=db.backref("coursea", uselist=False))

I've got Schemas for each in Marshmallow. What I'm looking for here is how I could retrieve all the student information for Students in a particular school (using SQLAlchemy). Later I will serialize these into JSON.

A side note: I frankly don't think you need both course_id and course, or school_id and school; the latter in the pairs should suffice. — 9000
– 9000, Commented Dec 29, 2016 at 1:05
Yes, I have seen this in examples, and assumed the backref is necessary. — CareFree
– CareFree, Commented Dec 29, 2016 at 1:13

Alex Seifi · Accepted Answer · 2016-12-28 23:48:05Z

2

I'm not familiar with Marshmallow but I guess you can do something similar to this:

School.query.\
         join(Course).\
         join(Course.schoola).\
         filter(School.name == 'School Name').all()

OR

Student.query \
            .join(Course) \
            .join(School) \
            .filter(School.name == 'School Name')

answered Dec 28, 2016 at 23:48

Alex Seifi

801 silver badge7 bronze badges

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

Collectives™ on Stack Overflow

Flask SQLAlchemy query from heirarchy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related