Grouping in SQLAlchemy

Question

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:

• Course 1
  • Assignment 1
    • Submission 1 (01-04-2017 01:02:03)
    • Submission 2 (01-04-2017 01:03:05)
  • Assignment 2
    • No submissions yet
• Course 2
  • Assignment 2
    • Submission 1 (01-04-2017 02:03:10)

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?

Answer 1

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 Submissions 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 Courses belonging to current_user, along with their Assignments and Submissions:

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>]