Database: Significantly speed up queries by using proper relationships with subquery lazy loading

2 files changed, 22 insertions, 18 deletions

diff --git a/database.py b/database.py
index 746078e..2d3d070 100644
--- a/database.py
+++ b/database.py
@@ -58,7 +58,11 @@ class Upload(Base):
     readonly = db.Column(db.Boolean, nullable=True, default=bool)
     version = db.Column(db.Unicode(10), nullable=False, default=str)
     
-    author = relationship("User")
+    author = relationship("User", lazy="subquery")
+    
+    files = relationship("File", back_populates="upload", lazy="subquery")
+    comments = relationship("Comment", back_populates="upload", lazy="subquery")
+    votes = relationship("Vote", back_populates="upload", lazy="subquery")
     
     def json(self):
         return {
@@ -84,7 +88,7 @@ class File(Base):
     upload_id = db.Column(Types.ObjectId, db.ForeignKey("uploads.id"))
     download_url = db.Column(db.Text)
     
-    upload = relationship("Upload")
+    upload = relationship("Upload", back_populates="files", lazy="subquery")
     
     def json(self):
         return {
@@ -111,8 +115,9 @@ class Comment(Base):
     updated_at = db.Column(db.DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
     body = db.Column(db.UnicodeText, nullable=False)
     
-    author = relationship("User")
-    upload = relationship("Upload")
+    author = relationship("User", lazy="subquery")
+    upload = relationship("Upload", back_populates="comments", lazy="subquery")
+    votes = relationship("CommentVote", back_populates="comment", lazy="subquery")
     
     def json(self):
         return {
@@ -131,8 +136,8 @@ class Vote(Base):
     upload_id = db.Column(Types.ObjectId, db.ForeignKey("uploads.id"))
     impact = db.Column(db.Integer, nullable=False)
     
-    author = relationship("User")
-    upload = relationship("Upload")
+    author = relationship("User", lazy="subquery")
+    upload = relationship("Upload", back_populates="votes", lazy="subquery")
     
     def json(self):
         return {
@@ -149,8 +154,8 @@ class CommentVote(Base):
     comment_id = db.Column(Types.ObjectId, db.ForeignKey("comments.id"))
     impact = db.Column(db.Integer, nullable=False)
     
-    author = relationship("User")
-    comment = relationship("Comment")
+    author = relationship("User", lazy="subquery")
+    comment = relationship("Comment", back_populates="votes", lazy="subquery")
     
     def json(self):
         return {
diff --git a/routes/uploads.py b/routes/uploads.py
index fbbba3b..7bf06c5 100644
--- a/routes/uploads.py
+++ b/routes/uploads.py
@@ -23,29 +23,28 @@ def _add_upload(entry : Upload, session : DBSession):
     e = entry.json()
     
     e["comments"] = []
-    for comment in session.query(Comment).filter_by(upload=entry):
+    for comment in entry.comments:
         c = comment.json()
-        votes = session.query(CommentVote).filter_by(comment=comment)
         c["voting"] = {
             "sum" : 0,
             "count" : 0,
             "votes" : []
             }
         
-        for vote in votes:
+        for vote in comment.votes:
             c["voting"]["sum"] += vote.impact
             c["voting"]["count"] += 1
             c["voting"]["votes"].append(vote.json())
         
         e["comments"].append(c)
     
-    e["files"] = [file.json() for file in session.query(File).filter_by(upload=entry)]
+    e["files"] = [file.json() for file in entry.files]
     e["dependencies"] = [] #TODO
     
-    votes = session.query(Vote).filter_by(upload=entry)
+    votes = entry.votes
     e["voting"] = {
         "sum" : sum(i.impact for i in votes),
-        "count" : votes.count()
+        "count" : len(votes)
         }
     
     return e
@@ -152,10 +151,10 @@ def delete_upload(id):
     if entry.readonly:
         raise HTTPResponse("Resource is read-only", 403)
     
+    session.delete(entry.votes)
+    session.delete(entry.comments.votes)
+    session.delete(entry.comments)
     session.delete(entry)
-    for i in [Comment, Vote]:
-        for e in session.query(i).filter_by(upload=entry):
-            session.delete(e)
     
     for f in session.query(File).filter_by(upload=entry):
         session.delete(f)
@@ -221,7 +220,7 @@ def get_comment_vote(id, comment_id):
         raise HTTPResponse("Invalid upload id", 404)
     
     try:
-        comment = session.query(Comment).filter_by(id=comment_id, author=get_user(session), upload=session.query(Upload).get(id)).one()
+        comment = session.query(Comment).filter_by(id=comment_id, author=get_user(session), upload=upload).one()
     except db.orm.exc.NoResultFound:
         raise HTTPResponse("Invalid comment id", status=404)