db: rm subqueries, optimize

1 files 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