From: Jiri Vlasak <jiri.vlasak.2@cvut.cz>
Date: Fri, 4 Nov 2022 16:01:28 +0000 (+0100)
Subject: Improve database queries
X-Git-Tag: v0.5.0^2~4
X-Git-Url: https://rtime.felk.cvut.cz/gitweb/hubacji1/coffee-getter.git/commitdiff_plain/b41cae64fed1a6b02b7a6e197bbf10c64003b0be

Improve database queries
---

diff --git a/coffee_getter/db.py b/coffee_getter/db.py
index f1803ef..9239d7d 100644
--- a/coffee_getter/db.py
+++ b/coffee_getter/db.py
@@ -24,18 +24,19 @@ def Q(q, b="", t=["now", "-7 days"]):
 
         SELECT count(*), flavor
         FROM coffees
-        WHERE time BETWEEN
+        WHERE (coffees.time BETWEEN
             {dtf}
-            AND {dtt}
+            AND {dtt})
         GROUP BY flavor
 
         """
     elif q == "get_drinkers_of":
         if isinstance(b, tuple) or isinstance(b, list):
             assert len(b) > 0
-            f = f"WHERE flavor = '{b[0]}'"
+            f = f"WHERE (flavor = '{b[0]}'"
             for i in b[1:]:
                 f += f" OR flavor = '{i}'"
+            f += ")"
         else:
             assert b != ""
             f = f"WHERE flavor = '{b}'"
@@ -45,9 +46,9 @@ def Q(q, b="", t=["now", "-7 days"]):
         LEFT JOIN identifiers on coffees.id = identifiers.userid
         LEFT JOIN users on identifiers.userid = users.id
         {f}
-        AND coffees.time BETWEEN
+        AND (coffees.time BETWEEN
             {dtf}
-            AND {dtt}
+            AND {dtt})
         GROUP BY identifiers.userid
 
         """
diff --git a/ut/test_db.py b/ut/test_db.py
index 4320f56..ba2a84f 100644
--- a/ut/test_db.py
+++ b/ut/test_db.py
@@ -12,9 +12,9 @@ class Queries(TestCase):
 
         SELECT count(*), flavor
         FROM coffees
-        WHERE time BETWEEN
+        WHERE (coffees.time BETWEEN
             datetime('now', 'localtime', '-7 days')
-            AND datetime('now', 'localtime')
+            AND datetime('now', 'localtime'))
         GROUP BY flavor
 
         """
@@ -26,9 +26,9 @@ class Queries(TestCase):
         LEFT JOIN identifiers on coffees.id = identifiers.userid
         LEFT JOIN users on identifiers.userid = users.id
         WHERE flavor = 'FOO'
-        AND coffees.time BETWEEN
+        AND (coffees.time BETWEEN
             datetime('now', 'localtime', '-7 days')
-            AND datetime('now', 'localtime')
+            AND datetime('now', 'localtime'))
         GROUP BY identifiers.userid
 
         """
@@ -38,9 +38,9 @@ class Queries(TestCase):
         LEFT JOIN identifiers on coffees.id = identifiers.userid
         LEFT JOIN users on identifiers.userid = users.id
         WHERE flavor = 'BAR'
-        AND coffees.time BETWEEN
+        AND (coffees.time BETWEEN
             datetime('FROM', 'localtime')
-            AND datetime('TO', 'localtime')
+            AND datetime('TO', 'localtime'))
         GROUP BY identifiers.userid
 
         """
@@ -49,10 +49,10 @@ class Queries(TestCase):
         SELECT count(*), users.name FROM coffees
         LEFT JOIN identifiers on coffees.id = identifiers.userid
         LEFT JOIN users on identifiers.userid = users.id
-        WHERE flavor = 'FOO' OR flavor = 'BAR' OR flavor = 'B'
-        AND coffees.time BETWEEN
+        WHERE (flavor = 'FOO' OR flavor = 'BAR' OR flavor = 'B')
+        AND (coffees.time BETWEEN
             datetime('FROM', 'localtime')
-            AND datetime('TO', 'localtime')
+            AND datetime('TO', 'localtime'))
         GROUP BY identifiers.userid
 
         """