From 477410a6c033a0cbd8b07fae123a1e9386488ba6 Mon Sep 17 00:00:00 2001
From: Jesse Antti Matikainen <jmatikai@local>
Date: Sun, 3 Dec 2023 14:48:30 +0200
Subject: Implementing user combination gathering in SQL.

---
 db/analyse.py     | 12 ++++++++++++
 routes/analyse.py |  3 +--
 2 files changed, 13 insertions(+), 2 deletions(-)

diff --git a/db/analyse.py b/db/analyse.py
index 612f637..c958cc1 100644
--- a/db/analyse.py
+++ b/db/analyse.py
@@ -38,3 +38,15 @@ class DBAnalyse:
             } ).scalar()
         
         
+    def combinations(self, quiz_id):
+        sql = "SELECT u1.id, u2.id \
+                FROM questionaires quiz \
+                JOIN answers a1 ON a1.question_id = quiz.questionset[1] \
+                JOIN answers a2 ON a2.question_id = quiz.questionset[1] \
+                JOIN users u1 ON u1.id = a1.user_id \
+                JOIN users u2 ON u2.id = a2.user_id \
+                WHERE quiz.id = (:quiz_id) AND u1.id > u2.id;"
+        return self.db.session.execute( text(sql), { 
+                'quiz_id': quiz_id
+                } ).fetchall()
+
diff --git a/routes/analyse.py b/routes/analyse.py
index 279b02e..f982f2e 100644
--- a/routes/analyse.py
+++ b/routes/analyse.py
@@ -5,8 +5,7 @@ from routes.tools import rows2dicts, get_alert, get_nick, csrf_check
 
 def find_best_and_worst(aid, uid):
     match = {}
-    users = [ x[0] for x in D.quiz.users(aid) ]
-    comb = list(combinations(users,2))
+    comb = D.analyse.combinations(aid)
     if len(comb)<1:
         comb=[(uid,uid)]
     min, minme, max, maxme = 101, 101, -1, -1
-- 
cgit v1.2.3