I'm working on a FastAPI application and using SQLModel with a Postgres backend. I have Post objects, each of which can be upvoted by Users. We represent this with a PostUpvote many-to-many relation between Users and Posts. So far, so boring.
from datetime import datetime
from typing import List, Optional
from pydantic import BaseModel
from sqlmodel import Field, Relationship, SQLModel
import uuid as uuid_pkg
def uuid_hex():
return uuid_pkg.uuid4().hex
def PkIdField():
return Field(
default_factory=uuid_hex,
primary_key=True,
index=True,
nullable=False,
)
class PostBase(SQLModel):
title: str
description: str
class Post(PostBase, table=True):
creator_id: str = Field(foreign_key="amp_users.id")
id: str = PkIdField()
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
creator: User = Relationship(back_populates="posts")
upvotes: List["PostUpvote"] = Relationship(back_populates="post")
class UserBase(SQLModel):
email: str
class User(UserBase, table=True):
# "user" table is reserved by postgres
__tablename__ = "app_users"
id: str = PkIdField()
posts: List["Post"] = Relationship(back_populates="creator")
class PostUpvote(SQLModel, table=True):
post: Post = Relationship(back_populates="upvotes")
post_id: str = Field(foreign_key="posts.id", primary_key=True)
user_id: str = Field(foreign_key="app_users.id", primary_key=True)
As you can see, I've set up an upvotes relationship on my Post object, which will give me a list of all the upvotes for that post. But when I'm returning this to the frontend, I don't need or want a list of all the upvotes. I just need the count. Obviously, I can use len(post.updates) to get this, but that still requires us to fetch all the individual upvote objects for that post. So my question is, is there some way to add an upvote_count relationship to my Post object, like so:
class Post(PostBase, table=True):
creator_id: str = Field(foreign_key="amp_users.id")
id: str = PkIdField()
created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
creator: User = Relationship(back_populates="posts")
upvotes: List["PostUpvote"] = Relationship(back_populates="post")
upvote_count: int = Relationship(...)
Note that this is using SQLModel's Relationship feature (https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/), not SQLAlchemy relations (though I am running SQLAlchemy under the hood).
If there's some way to provide a custom SQLAlchemy query to the SQLModel relationship, that would solve the problem neatly. But I've not been able to find anything in the SQLModel docs about how to do so. Is this even possible? Or should I just resign myself to doing the query manually?
