From 4e6d02e4ae9a9718e15e6b5c1328f28eea444e75 Mon Sep 17 00:00:00 2001
From: Arjun Satarkar <me@arjunsatarkar.net>
Date: Mon, 31 Jul 2023 00:46:48 +0530
Subject: Get tags for all shown feeds in one query for /list_feeds

This also avoids the need to pass `core` to the template, which I never
liked since it is basically giving up on encapsulation and passing the
whole world (as far as the application is concerned) to the template -
not only the values it needs.

But that could be avoided in other ways too without reducing the number
of queries from 1 + (number of feeds shown) - eg. 51 - to just 2, which
this does.

The next place something like this needs to be done is with the /
(index.tpl) view. Currently, that is also passed `core` and actually
does 2 * (number of entries shown) + 1 queries, which could be eg. 101,
since we fetch both the feed title and feed tags for every entry
separately.

With some improvements, it should be possible to do that too in 2
queries.
---
 serve.py             |  3 +--
 tagrss.py            | 42 ++++++++++++++++++++++++++++++++----------
 views/list_feeds.tpl |  3 +--
 3 files changed, 34 insertions(+), 14 deletions(-)

diff --git a/serve.py b/serve.py
index 63aea53..92c79ff 100755
--- a/serve.py
+++ b/serve.py
@@ -126,7 +126,7 @@ def list_feeds():
     offset = (page_num - 1) * per_page
     with core_lock:
         total_pages: int = max(1, math.ceil(core.get_feed_count() / per_page))
-        feeds = core.get_feeds(limit=per_page, offset=offset)
+        feeds = core.get_feeds(limit=per_page, offset=offset, get_tags=True)
         return bottle.template(
             "list_feeds",
             feeds=feeds,
@@ -135,7 +135,6 @@ def list_feeds():
             total_pages=total_pages,
             per_page=per_page,
             max_per_page=MAX_PER_PAGE_ENTRIES,
-            core=core,
         )
 
 
diff --git a/tagrss.py b/tagrss.py
index c5ad368..076a205 100644
--- a/tagrss.py
+++ b/tagrss.py
@@ -184,23 +184,45 @@ class TagRss:
         with self.connection:
             self.connection.execute("DELETE FROM feeds WHERE id = ?;", (feed_id,))
 
-    def get_feeds(self, *, limit: int, offset: int = 0) -> list[dict[str, typing.Any]]:
+    def get_feeds(
+        self, *, limit: int, offset: int = 0, get_tags: bool = False
+    ) -> list[dict[str, typing.Any]]:
         with self.connection:
             resp = self.connection.execute(
                 "SELECT id, source, title FROM feeds \
                 ORDER BY id ASC LIMIT ? OFFSET ?;",
                 (limit, offset),
             ).fetchall()
-        feeds = []
+        feeds: dict[int, dict[str, typing.Any]] = {}
         for row in resp:
-            feeds.append(
-                {
-                    "id": row[0],
-                    "source": row[1],
-                    "title": row[2],
-                }
-            )
-        return feeds
+            feeds[row[0]] = {
+                "source": row[1],
+                "title": row[2],
+            }
+        if get_tags:
+            with self.connection:
+                feed_ids = feeds.keys()
+                placeholder_str = ",".join(["?"] * len(feed_ids))
+                resp = self.connection.execute(
+                    f"SELECT feed_id, tag FROM feed_tags WHERE feed_id in ({placeholder_str});",
+                    (*feed_ids,),
+                ).fetchall()
+                for row in resp:
+                    try:
+                        feeds[row[0]]["tags"].append(row[1])
+                    except KeyError:
+                        feeds[row[0]]["tags"] = [row[1]]
+        result: list[dict[str, typing.Any]] = []
+        for item in feeds.items():
+            feed = {
+                "id": item[0],
+                "source": item[1]["source"],
+                "title": item[1]["title"],
+            }
+            if get_tags:
+                feed["tags"] = item[1]["tags"]
+            result.append(feed)
+        return result
 
     def get_entry_count(
         self,
diff --git a/views/list_feeds.tpl b/views/list_feeds.tpl
index 05cd8c9..2b4bd6d 100644
--- a/views/list_feeds.tpl
+++ b/views/list_feeds.tpl
@@ -59,8 +59,7 @@
                     <td>{{feed["title"]}} (<a href="/?included_feeds={{feed['id']}}" class="no-visited-indication">filter</a>)</td>
                     <td class="td-tags">
                         <div>
-                            % tags = core.get_feed_tags(feed["id"])
-                            % for i, tag in enumerate(tags):
+                            % for i, tag in enumerate(feed["tags"]):
                                 % if i > 0:
                                     {{", "}}
                                 % end
-- 
cgit v1.2.3-57-g22cb