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"
}
...
}