Use inner join for get drinkers query

diff --git a/coffee_getter/db.py b/coffee_getter/db.py
index 9239d7d9d3b2de77ca853b171b1b326881c716d2..5c4eb91b88d713e1c9b0ff05fe194241dbbd5afb 100644 (file)
--- a/coffee_getter/db.py
+++ b/coffee_getter/db.py
@@ -43,13 +43,13 @@ def Q(q, b="", t=["now", "-7 days"]):
         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
             {dtf}
             AND {dtt})
-        GROUP BY identifiers.userid
+        GROUP BY users.id
 
         """
 

diff --git a/ut/test_db.py b/ut/test_db.py
index ba2a84fbb0a1e1aefc9105959b4bb204108730ba..069e8714b28a86f8e663ee31e5d5c5ed16dbb12e 100644 (file)
--- a/ut/test_db.py
+++ b/ut/test_db.py
@@ -23,37 +23,37 @@ class Queries(TestCase):
         assert Q("get_drinkers_of", "FOO") == """
 
         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
         WHERE flavor = 'FOO'
         AND (coffees.time BETWEEN
             datetime('now', 'localtime', '-7 days')
             AND datetime('now', 'localtime'))
-        GROUP BY identifiers.userid
+        GROUP BY users.id
 
         """
         assert Q("get_drinkers_of", "BAR", ["FROM", "TO"]) == """
 
         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
         WHERE flavor = 'BAR'
         AND (coffees.time BETWEEN
             datetime('FROM', 'localtime')
             AND datetime('TO', 'localtime'))
-        GROUP BY identifiers.userid
+        GROUP BY users.id
 
         """
         assert Q("get_drinkers_of", ["FOO", "BAR", "B"], ["FROM", "TO"]) == """
 
         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
         WHERE (flavor = 'FOO' OR flavor = 'BAR' OR flavor = 'B')
         AND (coffees.time BETWEEN
             datetime('FROM', 'localtime')
             AND datetime('TO', 'localtime'))
-        GROUP BY identifiers.userid
+        GROUP BY users.id
 
         """