async databases return relationship post

model_relationship.py

#________________microblog schema.py___________________

class PostBase(BaseModel):
    title: str
    text: str
    date: datetime


class PostCreate(PostBase):
    parent_id: Optional[int] = None

    class Config:
        orm_mode = True


class PostInDB(PostCreate):
    post_id: Optional[int]
    user: Optional[UserInResponse]


class PostCommentList(PostInDB):
    children: Optional[List[PostBase]] = []

#________________user schema.py___________________add

class UserInResponse(BaseModel):
    id: UUID
    name: Optional[str]
    email: str


#________________user models.py___________________

class User(Base, SQLAlchemyBaseUserTable):
    name = Column(String, unique=True)

#________________microblog models.py___________________

class Post(Base):
    __tablename__ = "posts"

    post_id = Column(Integer, primary_key=True, index=True, unique=True)
    title = Column(String)
    text = Column(String(350))
    date = Column(DateTime(timezone=True), server_default=sql.func.now())
    user_id = Column(String, ForeignKey('user.id'))
    user = relationship("User")
    parent_id = Column(Integer, ForeignKey('posts.post_id', ondelete='CASCADE'), nullable=True) # for cascade delete
    children = relationship("Post", backref=backref('parent', remote_side=[post_id], passive_deletes=True)) # for cascade delete


posts = Post.__table__


#________________Services.py___________________


async def create_post(item: PostCreate, user: User) -> PostInDB:
    post = posts.insert().values(**item.dict(), user_id=str(user.id))
    pk = await database.execute(post)
    post_in_db = PostInDB(**item.dict())
    post_in_db.post_id = pk
    post_in_db.user = UserInResponse(**user.dict())

    return post_in_db


async def get_post(pk: int) -> PostCommentList:
    """ Get post with children posts """
    query = union(
            posts.join(users).select().where(and_(
                posts.c.post_id == pk, users.c.id == posts.c.user_id)),
            posts.join(users).select().where(and_(
                posts.c.parent_id == pk, users.c.id == posts.c.user_id))
        )
    current_post = None
    async for post in database.iterate(query=query):
        if pk == dict(post).get("post_id"):
            current_post = PostCommentList(**post)
            current_post.user = UserInResponse(**post)
        elif pk == dict(post).get("parent_id"):
            current_post.children.append(PostBase(**post))
    return current_post


#________________returned model___________________

{
  "title": "string",
  "text": "string",
  "date": "2020-06-06T19:36:45.204Z",
  "id": 0,
  "parent_id": 0,
  "children": [
    {
      "title": "string",
      "text": "string",
      "date": "2020-06-06T19:36:45.204Z"
    }
  ],
  "user": {
    "id": "string",
    "email": "[email protected]",
    "is_active": true,
    "is_superuser": false,
    "name": "string"
  }
}