Extracting reusable queries with SQLAlchemy

--- 
+++ 
@@ -1,6 +1,7 @@
 import logging
 from typing import Annotated

+import sqlalchemy
 from fastapi import APIRouter, Depends, HTTPException
 from storeapi.database import comment_table, database, like_table, post_table
 from storeapi.models.post import (
@@ -18,6 +19,12 @@
 router = APIRouter()

 logger = logging.getLogger(__name__)
+
+select_post_and_likes = (
+    sqlalchemy.select(post_table, sqlalchemy.func.count(like_table.c.id).label("likes"))
+    .select_from(post_table.outerjoin(like_table))
+    .group_by(post_table.c.id)
+)


 async def find_post(post_id: int):
@@ -90,7 +97,11 @@
 async def get_post_with_comments(post_id: int):
     logger.info("Getting post and its comments")

-    post = await find_post(post_id)
+    query = select_post_and_likes.where(post_table.c.id == post_id)
+
+    logger.debug(query)
+
+    post = await database.fetch_one(query)
     if not post:
         raise HTTPException(status_code=404, detail="Post not found")

--- 
+++ 
@@ -168,7 +168,13 @@
     response = await async_client.get(f"/post/{created_post['id']}")

     assert response.status_code == 200
-    assert response.json() == {"post": created_post, "comments": [created_comment]}
+    assert response.json() == {
+        "post": {
+            **created_post,
+            "likes": 0,
+        },
+        "comments": [created_comment],
+    }


 @pytest.mark.anyio

--- 
+++ 
@@ -10,6 +10,12 @@

     id: int
     user_id: int
+
+
+class UserPostWithLikes(UserPost):
+    model_config = ConfigDict(from_attributes=True)
+
+    likes: int


 class CommentIn(BaseModel):
@@ -25,7 +31,7 @@


 class UserPostWithComments(BaseModel):
-    post: UserPost
+    post: UserPostWithLikes
     comments: list[Comment]