From 001a042ccba1c3faf340d8217ee8d15efa60dc05 Mon Sep 17 00:00:00 2001
From: Jordan <me@jordan.im>
Date: Fri, 18 Aug 2023 00:05:40 -0700
Subject: db: rm subqueries, optimize

---
 db.go | 55 +++++++++++++++----------------------------------------
 1 file changed, 15 insertions(+), 40 deletions(-)

diff --git a/db.go b/db.go
index 7521666..92bbcd1 100644
--- a/db.go
+++ b/db.go
@@ -163,58 +163,33 @@ func (db *SqliteDB) ListEntries(limit int, offset int, user string,
 		FROM urls
 		INNER JOIN users ON users.id = urls.user_string_id
 		INNER JOIN guilds ON guilds.id = urls.guild_string_id
-		INNER JOIN channels ON channels.id = urls.channel_string_id`
+		INNER JOIN channels ON channels.id = urls.channel_string_id
+		WHERE TRUE`
 
-	hasWhere := false
 	args := []interface{}{}
 	if user != "" {
 		query = fmt.Sprintf(`%s
-		WHERE user_string_id = (SELECT id FROM users WHERE user_id = ?)
+		AND users.user_id = ?
 		`, query)
 		args = append(args, user)
-		hasWhere = true
 	}
 	if guild != "" {
-		if !hasWhere {
-			query = fmt.Sprintf(`%s
-			WHERE guild_string_id = (SELECT id FROM guilds WHERE guild_id = ?)
-			`, query)
-			args = append(args, guild)
-			hasWhere = true
-		} else {
-			query = fmt.Sprintf(`%s
-			AND guild_string_id = (SELECT id FROM guilds WHERE guild_id = ?)
-			`, query)
-			args = append(args, guild)
-		}
+		query = fmt.Sprintf(`%s
+		AND guilds.guild_id = ?
+		`, query)
+		args = append(args, guild)
 	}
 	if channel != "" {
-		if !hasWhere {
-			query = fmt.Sprintf(`%s
-			WHERE channel_string_id = (SELECT id FROM channels WHERE channel_id = ?)
-			`, query)
-			args = append(args, channel)
-			hasWhere = true
-		} else {
-			query = fmt.Sprintf(`%s
-			AND channel_string_id = (SELECT id FROM channels WHERE channel_id = ?)
-			`, query)
-			args = append(args, channel)
-		}
+		query = fmt.Sprintf(`%s
+		AND channels.channel_id = ?
+		`, query)
+		args = append(args, channel)
 	}
 	if search != "" {
-		if !hasWhere {
-			query = fmt.Sprintf(`%s
-			WHERE urls.url LIKE '%%' || ? || '%%'
-			`, query)
-			args = append(args, search)
-			hasWhere = true
-		} else {
-			query = fmt.Sprintf(`%s
-			AND urls.url LIKE '%%' || ? || '%%'
-			`, query)
-			args = append(args, search)
-		}
+		query = fmt.Sprintf(`%s
+		AND urls.url LIKE '%%' || ? || '%%'
+		`, query)
+		args = append(args, search)
 	}
 	query = fmt.Sprintf(`%s
 		ORDER BY urls.id DESC
-- 
cgit v1.2.3-54-g00ecf