From: Jiri Vlasak <jiri.vlasak.2@cvut.cz>
Date: Mon, 7 Nov 2022 15:09:34 +0000 (+0100)
Subject: Use inner join for get drinkers query
X-Git-Tag: v0.5.0^2~3
X-Git-Url: https://rtime.felk.cvut.cz/gitweb/hubacji1/coffee-getter.git/commitdiff_plain/400efd3a7aea2033e964cbe338c9dcd139fdba93

Use inner join for get drinkers query
---

diff --git a/coffee_getter/db.py b/coffee_getter/db.py
index 9239d7d..5c4eb91 100644
--- 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 ba2a84f..069e871 100644
--- 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
 
         """