Reputation: 8097
I'm developing something like a little learning management system with Flask and SQLAlchemy. I have users, courses, assignments and submissions. User can take several courses. Each assignment belongs to exactly one course. User can submit several submissions for every assignment associated with her courses. I'm trying to write a function that will show all assignments available to a user with all submissions already submitted. The structure of an output page is like this:
So for a fixed user I have to query all courses the user belong, for every course query all assignments and for every assignment query all submissions that belong to a particular user.
I can do it with some Python code but I believe this can be done with wise SQLAlchemy queries I'm asking for. The details follows.
My model specificion is basically like this:
class Course(db.Model):
id = db.Column(db.Integer, primary_key=True)
assignments = db.relationship('Assignment', backref='course',
lazy='dynamic')
class User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key=True)
courses = db.relationship('Course', secondary=courses_users,
backref=db.backref(
'users', lazy='dynamic'))
submissions = db.relationship('Submission', backref=db.backref('user'),
lazy='dynamic')
class Assignment(db.Model):
id = db.Column(db.Integer, primary_key=True)
course_id = db.Column(db.Integer, db.ForeignKey('course.id'))
submissions = db.relationship('Submission', backref='assignment',
lazy='dynamic')
class Submission(db.Model):
id = db.Column(db.Integer, primary_key=True)
assignment_id = db.Column(db.Integer, db.ForeignKey('assignment.id'))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
Currently I prepare data for list_assignments
view in the following way:
@app.route("/list/assignments")
@login_required
def list_assignments():
submissions = current_user.submissions.all()
courses = current_user.courses
mycourses = []
for course in courses:
mycourse={'id': course.id, 'assignments': []}
for assignment in course.assignments:
mycourse['assignments'].append(
(assignment,
[s for s in submissions if s.assignment == assignment]))
mycourses.append(mycourse)
return render_template("list_assignments.html",
mycourses=mycourses)
So I have a list of courses and each course contains a list of assignment records, each assignment record is a pair, the first element is an assignment itself and the second element is a list of submissions that belong to this assignment and this user. Not very elegant at all.
What is the best way to replace this logic with SQLAlchemy queries? Joins? Groupby's?
Upvotes: 0
Views: 470
Reputation: 53017
What you have currently is the "ORM way" of doing things – nothing wrong with it. You handle the instances and their relationships as if they were plain Python objects that contained collections of other objects. In reality since you have configured your relationships as lazy='dynamic'
each iteration over a relationship Query
object fires an SQL query against the DB.
If you were to either configure your relationships to act as collections (lazy or eager), or added such additional relationships to your models, you could fetch the whole graph in a single query:
# Lazy collections that by default issue SELECTs
Course.assignments_collection = db.relationship('Assignment')
Assignment.submissions_collection = db.relationship('Submission')
Since you want to limit the Submission
s to only those owned by the current user, we need an alias:
submissions = db.aliased(
Submission,
db.session.query(Submission).
filter(Submission.user == current_user).
subquery()
)
We can then form the eager loading query that fetches Course
s belonging to current_user
, along with their Assignment
s and Submission
s:
courses = db.session.query(Course).\
outerjoin(Course.assignments_collection).\
outerjoin(submissions, Assignment.submissions_collection).\
options(db.contains_eager(Course.assignments_collection).
contains_eager(Assignment.submissions_collection,
alias=submissions)).\
filter(Course.users.any(User.id == current_user.id)).\
all()
You would then use the new relationship attributes we defined to access the eagerly loaded collections. For example if we were to:
In [104]: current_user = User()
In [105]: db.session.add(current_user)
In [106]: db.session.add(Course(assignments=[Assignment(submissions=[Submission(user=current_user)])], users=[current_user]))
In [107]: db.session.commit()
then the above query would yield a result such as:
Out[110]: [<__main__.Course at 0x7f37a4a2dcc0>]
In [111]: _[0].assignments_collection[0].submissions_collection
Out[111]: [<__main__.Submission at 0x7f37a49d5358>]
Upvotes: 1