Use inner join for get drinkers query

[hubacji1/coffee-getter.git] / coffee_getter / db.py

diff --git a/coffee_getter/db.py b/coffee_getter/db.py
index d2dfdeb5d3d55a8c91dd99ae8260c0073a4f9d07..5c4eb91b88d713e1c9b0ff05fe194241dbbd5afb 100644 (file)
--- a/coffee_getter/db.py
+++ b/coffee_getter/db.py
@@ -24,31 +24,32 @@ 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}'"
         return f"""
 
         SELECT count(*), users.name FROM coffees
-        LEFT JOIN identifiers on coffees.id = identifiers.userid
-        LEFT JOIN users on identifiers.userid = users.id
+        INNER JOIN identifiers on coffees.id = identifiers.id
+        INNER JOIN users on identifiers.userid = users.id
         {f}
-        AND coffees.time BETWEEN
+        AND (coffees.time BETWEEN
             {dtf}
-            AND {dtt}
-        GROUP BY identifiers.userid
+            AND {dtt})
+        GROUP BY users.id
 
         """
 
@@ -70,7 +71,7 @@ class Db:
         top.sort(key=lambda x: (x[1], x[0]), reverse=True)
         return tuple(top)
 
-    def getTopMateDrinkers(self):
+    def get_top_mate_drinkers(self):
         """Return list of pairs of name, count for Mate drinkers."""
         users = {}
         que = Q("get_drinkers_of", "Club-Mate 0,5 l")
@@ -97,7 +98,7 @@ class Db:
         top.sort(key=lambda x: (x[1], x[0]), reverse=True)
         return tuple(top)
 
-    def getDrunkSum(self, *args, **kwargs):
+    def get_drunk_sum(self, flavor, dtf, dtt):
         """Return list of drunken ``flavor`` from ``dtf`` to ``dtt``.
 
         Keyword arguments:
@@ -105,23 +106,13 @@ class Db:
         dtf -- Date and time *from*.
         dtt -- Date and time *to*.
         """
-        assert ((len(args) == 3 and len(kwargs) == 0)
-                or (len(args) == 0 and len(kwargs) == 3))
-        if args:
-            flavor = args[0]
-            dtf = args[1]
-            dtt = args[2]
-        elif kwargs:
-            flavor = kwargs["flavor"]
-            dtf = kwargs["dtf"]
-            dtt = kwargs["dtt"]
         que = Q("get_drinkers_of", flavor.split(";"), (dtf, dtt))
         drunk = []
         for (cnt, un) in self.cur.execute(que):
             drunk.append((un, cnt))
         return tuple(drunk)
 
-    def getDrunkList(self, *args, **kwargs):
+    def get_drunk_list(self, flavor, dtf, dtt):
         """Return dict of lists of drunken ``flavor`` from ``dtf`` to ``dtt``.
 
         Keyword arguments:
@@ -129,16 +120,6 @@ class Db:
         dtf -- Date and time *from*.
         dtt -- Date and time *to*.
         """
-        assert ((len(args) == 3 and len(kwargs) == 0)
-                or (len(args) == 0 and len(kwargs) == 3))
-        if args:
-            flavor = args[0]
-            dtf = args[1]
-            dtt = args[2]
-        elif kwargs:
-            flavor = kwargs["flavor"]
-            dtf = kwargs["dtf"]
-            dtt = kwargs["dtt"]
         flavors = flavor.split(";")
         drunk = {}
         i = 0