aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorArjun Satarkar <me@arjunsatarkar.net>2023-07-30 19:16:48 +0000
committerArjun Satarkar <me@arjunsatarkar.net>2023-07-30 19:57:22 +0000
commit4e6d02e4ae9a9718e15e6b5c1328f28eea444e75 (patch)
treef296475160d0187fa8fbc00d6a2a4f109a4058c7
parent88db0f45477f2278edd7877161942a215aaace3f (diff)
downloadtagrss-4e6d02e4ae9a9718e15e6b5c1328f28eea444e75.tar
tagrss-4e6d02e4ae9a9718e15e6b5c1328f28eea444e75.tar.gz
tagrss-4e6d02e4ae9a9718e15e6b5c1328f28eea444e75.zip
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.
-rwxr-xr-xserve.py3
-rw-r--r--tagrss.py42
-rw-r--r--views/list_feeds.tpl3
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