FastAPI Pydantic mapping joined tables

Viewed 60

I'm working on a GET request with FastAPI to get data from database. I have to use ".join" on my query because I need to add filters on the child table. And I want the JSON response can include both the parent and child table. I used "relationship" in my model.py for "Parent" and "Child", and the following the schema.py works when I remove the .join(), and query on the Parent only. The response contains all the fields in Parent and Child. But I really need the filter on the Child table.

How should I build the schema to handle this joined tables scenario? I appreciate for all the answers, thank you!!!

server.py

    @app.get("/test/{id}", response_model = schema.TestRead)
    def get_filter(id:int):
        db = getDB('TESTDB')
        result = db.query(Parent, Child)\
            .join(Child)\
            .filter(Parent.id == id)\
            .filter(Child.name == 'TestName')\
            .all()
    
        return result

schema.py

    class Parent(BaseModel):
        id: int
    
        class Config:
            orm_mode = True
    
    class Child(BaseModel):
        id: int
        name: str
    
        class Config:
            orm_mode = True
    
    class TestRead(Parent):
        child: List[Child]=[]
        class Config:
            orm_mode = True

I got all nulls..

    {
      "id": null,
      "child": []
    }

My expect response:

    {
        "id": 1
        "child": {
            "id": 1
            "name": "TestName"
        }
        "child": {
            "id": 1
            "name": "TestName"
        }
        ...
    }
0 Answers
Related