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

1 files changed, 9 insertions, 10 deletions

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)