simple-web-app

commit 4ca2f486141cbee0c2365202cb94c9a7e985900e
parent 86f6ceed6064a92690e70796a627166c3840462e
Author: Silas Brack <silasbrack@gmail.com>
Date:   Thu,  2 Oct 2025 09:32:39 +0200

fix: use of tables from migration

Diffstat:
M
src/simple_web_app/app.py
 | 
2
+-
M
src/simple_web_app/queries/basic.sql
 | 
18
+++++++++++-------

2 files changed, 12 insertions(+), 8 deletions(-)
diff --git a/src/simple_web_app/app.py b/src/simple_web_app/app.py
@@ -103,7 +103,7 @@ async def show_home_page(request: Request):
     rows = await queries_basic.get_news(request.state.conn, limit=limit, offset=offset, max_published_time=current_time)
     news = [dict(row) for row in rows]
     for new in news:
-        rows = await queries_basic.get_categories_for_news(request.state.conn, id=new["id"])
+        rows = await queries_basic.get_categories_for_news(request.state.conn, news_item_id=new["id"])
         categories = [row["category"] for row in rows]
         new["categories"] = categories
         published = datetime.datetime.strptime(new["published"], "%Y-%m-%dT%H:%M:%S.%f%z")
diff --git a/src/simple_web_app/queries/basic.sql b/src/simple_web_app/queries/basic.sql
@@ -12,15 +12,19 @@ SELECT title, text
 FROM news_item_fts(:query)
 LIMIT :limit;
 
--- name: get_categories_for_news(id)
-SELECT category_id
-FROM news_item_category
-WHERE id = :id;
+-- name: get_categories_for_news(news_item_id)
+SELECT nic.category_id, c.name AS category
+FROM news_item_category AS nic
+INNER JOIN category AS c
+  ON c.id = nic.category_id
+WHERE nic.news_item_id = :news_item_id;
 
 -- name: get_categories(limit)
-SELECT category_id
-FROM news_item_category
-GROUP BY category_id
+SELECT nic.category_id, c.name AS category
+FROM news_item_category AS nic
+INNER JOIN category AS c
+  ON c.id = nic.category_id
+GROUP BY nic.category_id
 ORDER BY COUNT(*) DESC
 LIMIT :limit;