From 4470c6cd3e8ce4c972c3701fe541478f412efa8a Mon Sep 17 00:00:00 2001 From: Fulgen301 Date: Sat, 27 Jul 2019 01:38:33 +0200 Subject: Database: Significantly speed up queries by using proper relationships with subquery lazy loading --- routes/uploads.py | 19 +++++++++---------- 1 file changed, 9 insertions(+), 10 deletions(-) (limited to 'routes/uploads.py') 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) -- cgit v1.2.3-54-g00ecf