Optimise SQL queriesv1.24.3

3 files changed, 19 insertions, 4 deletions

diff --git a/app/blueprints/api/endpoints.py b/app/blueprints/api/endpoints.py
index 7c7a44a..65af3b0 100644
--- a/app/blueprints/api/endpoints.py
+++ b/app/blueprints/api/endpoints.py
@@ -28,6 +28,9 @@ from app.querybuilder import QueryBuilder
 
 @bp.route("/api/packages/")
 def packages():
+	import sys
+	print("\n\n############", file=sys.stderr)
+
 	qb    = QueryBuilder(request.args)
 	query = qb.buildPackageQuery()
 	ver   = qb.getMinetestVersion()
diff --git a/app/blueprints/homepage/__init__.py b/app/blueprints/homepage/__init__.py
index df638df..5df612f 100644
--- a/app/blueprints/homepage/__init__.py
+++ b/app/blueprints/homepage/__init__.py
@@ -4,17 +4,23 @@ bp = Blueprint("homepage", __name__)
 
 from app.models import *
 import flask_menu as menu
+from sqlalchemy.orm import joinedload
 from sqlalchemy.sql.expression import func
 
 @bp.route("/")
 @menu.register_menu(bp, ".", "Home")
 def home():
+	def join(query):
+		return query.options( \
+			joinedload(Package.license), \
+			joinedload(Package.media_license))
+
 	query   = Package.query.filter_by(approved=True, soft_deleted=False)
 	count   = query.count()
-	new     = query.order_by(db.desc(Package.created_at)).limit(8).all()
-	pop_mod = query.filter_by(type=PackageType.MOD).order_by(db.desc(Package.score)).limit(8).all()
-	pop_gam = query.filter_by(type=PackageType.GAME).order_by(db.desc(Package.score)).limit(4).all()
-	pop_txp = query.filter_by(type=PackageType.TXP).order_by(db.desc(Package.score)).limit(4).all()
+	new     = join(query.order_by(db.desc(Package.created_at))).limit(8).all()
+	pop_mod = join(query.filter_by(type=PackageType.MOD).order_by(db.desc(Package.score))).limit(8).all()
+	pop_gam = join(query.filter_by(type=PackageType.GAME).order_by(db.desc(Package.score))).limit(4).all()
+	pop_txp = join(query.filter_by(type=PackageType.TXP).order_by(db.desc(Package.score))).limit(4).all()
 	downloads_result = db.session.query(func.sum(PackageRelease.downloads)).one_or_none()
 	downloads = 0 if not downloads_result or not downloads_result[0] else downloads_result[0]
 	return render_template("index.html", count=count, downloads=downloads, \
diff --git a/app/blueprints/packages/packages.py b/app/blueprints/packages/packages.py
index a7d5793..700fc44 100644
--- a/app/blueprints/packages/packages.py
+++ b/app/blueprints/packages/packages.py
@@ -31,6 +31,7 @@ from wtforms import *
 from wtforms.validators import *
 from wtforms.ext.sqlalchemy.fields import QuerySelectField, QuerySelectMultipleField
 from sqlalchemy import or_, func
+from sqlalchemy.orm import joinedload, subqueryload
 
 
 @menu.register_menu(bp, ".mods", "Mods", order=11, endpoint_arguments_constructor=lambda: { 'type': 'mod' })
@@ -43,6 +44,11 @@ def list_all():
 	query = qb.buildPackageQuery()
 	title = qb.title
 
+	query = query.options( \
+			joinedload(Package.license), \
+			joinedload(Package.media_license), \
+			subqueryload(Package.tags))
+
 	if qb.lucky:
 		package = query.first()
 		if package: