WIP: Optimise packages queryoptimise_packages_query

3 files changed, 30 insertions, 10 deletions

diff --git a/app/blueprints/api/endpoints.py b/app/blueprints/api/endpoints.py
index d5ef9d0..d9defb0 100644
--- a/app/blueprints/api/endpoints.py
+++ b/app/blueprints/api/endpoints.py
@@ -25,15 +25,27 @@ from app.models import *
 from app.utils import is_package_page
 from app.markdown import render_markdown
 from app.querybuilder import QueryBuilder
+from sqlalchemy.orm import joinedload
 
 @bp.route("/api/packages/")
 def packages():
 	qb    = QueryBuilder(request.args)
-	query = qb.buildPackageQuery()
-	ver   = qb.getMinetestVersion()
+	query = qb.buildPackageQuery(db.session.query(Package, PackageRelease))
+
+	import sys
+	print(query, file=sys.stdout)
+
+	pkgs = [result[0].getAsDictionaryShort(current_app.config["BASE_URL"], release=result[1]) \
+			for result in query.all()]
+
+
+	# qb    = QueryBuilder(request.args)
+	# query = qb.buildPackageQuery()
+	# ver   = qb.getMinetestVersion()
+
+	# pkgs = [result.getAsDictionaryShort(current_app.config["BASE_URL"], version=ver) \
+	# 		for result in query.all()]
 
-	pkgs = [package.getAsDictionaryShort(current_app.config["BASE_URL"], version=ver) \
-			for package in query.all()]
 	return jsonify(pkgs)
 
 
diff --git a/app/models.py b/app/models.py
index f3e65a2..9a25488 100644
--- a/app/models.py
+++ b/app/models.py
@@ -534,9 +534,9 @@ class Package(db.Model):
 			"type": self.type.toName(),
 		}
 
-	def getAsDictionaryShort(self, base_url, version=None):
+	def getAsDictionaryShort(self, base_url, version=None, release=None):
 		tnurl = self.getThumbnailURL(1)
-		release = self.getDownloadRelease(version=version)
+		release = release if release else self.getDownloadRelease(version=version)
 		return {
 			"name": self.name,
 			"title": self.title,
diff --git a/app/querybuilder.py b/app/querybuilder.py
index 5b75b4c..74b680c 100644
--- a/app/querybuilder.py
+++ b/app/querybuilder.py
@@ -64,8 +64,11 @@ class QueryBuilder:
 
 		return MinetestRelease.get(self.minetest_version, self.protocol_version)
 
-	def buildPackageQuery(self):
-		query = Package.query.filter_by(soft_deleted=False, approved=True)
+	def buildPackageQuery(self, query=None):
+		if not query:
+			query = Package.query
+
+		query = query.filter(Package.soft_deleted==False, Package.approved==True)
 
 		if len(self.types) > 0:
 			query = query.filter(Package.type.in_(self.types))
@@ -110,10 +113,15 @@ class QueryBuilder:
 		if self.protocol_version or self.minetest_version:
 			version = self.getMinetestVersion()
 			if version:
-				query = query.join(Package.releases) \
+				subqry = db.session.query(PackageRelease.id).correlate(PackageRelease) \
+					.filter(PackageRelease.package_id==Package.id) \
 					.filter(PackageRelease.approved==True) \
 					.filter(or_(PackageRelease.min_rel_id==None, PackageRelease.min_rel_id<=version.id)) \
-					.filter(or_(PackageRelease.max_rel_id==None, PackageRelease.max_rel_id>=version.id))
+					.filter(or_(PackageRelease.max_rel_id==None, PackageRelease.max_rel_id>=version.id)) \
+					.order_by(db.desc(PackageRelease.id)) \
+					.limit(1).subquery()
+
+				query = query.filter(PackageRelease.id==subqry)
 
 		if self.limit:
 			query = query.limit(self.limit)