Nested join - object returned with all fields set to None
Closed this issue · 4 comments
First mentioned in #101 by @Justinianus2001:
"
I aim to retrieve a project and its nested objects based on client_id, department_id, and assignee_id as follows:
db_project = await crud_projects.get_joined(
db=db, id=id, is_deleted=False, nest_joins=True, schema_to_select=ProjectRead,
joins_config=[
JoinConfig(
model=Client,
join_on=Project.client_id == Client.id,
join_prefix="client",
schema_to_select=ClientRead,
join_type="left",
),
JoinConfig(
model=Department,
join_on=Project.department_id == Department.id,
join_prefix="department",
schema_to_select=DepartmentRead,
join_type="left",
),
JoinConfig(
model=User,
join_on=Project.assignee_id == User.id,
join_prefix="assignee",
schema_to_select=UserReadSub,
join_type="left",
),
],
)
However, I encounter an issue: when a foreign key is null, the object is still returned but with all fields set to None.
{
'id': 1,
'project_code': 'ABC1',
'status': 'Approved',
'name': 'Project Example',
'start_date': datetime.datetime(2024, 6, 5, 9, 26, 48, 914000, tzinfo=datetime.timezone.utc),
'end_date': datetime.datetime(2024, 6, 5, 9, 26, 48, 914000, tzinfo=datetime.timezone.utc),
'client': {
'name': None,
'contact': None,
'phone': None,
'email': None,
'id': None
},
'department': {
'id': 1,
'name': 'Department Name'
},
'assignee': {
'id': None,
'name': None,
'username': None,
'email': None,
'phone': None,
'profile_image_url': None,
'department_id': None,
'company_id': None
}
}
How can I modify the query so that if a nested object's foreign key is null, the entire nested object is returned as None (e.g., 'client': None)?
"
@Justinianus2001 could you please provide sample models and schemas so I can reproduce?
Below are my schemas:
class ProjectReadSub(BaseModel):
id: int
project_code: str
status: ProjectStatus
name: str
start_date: datetime
end_date: datetime
class ProjectRead(ProjectReadSub):
department: DepartmentRead | None
assignee: UserReadSub | None
client: ClientRead | None
class DepartmentRead(BaseModel):
id: int
name: str
class UserReadSub(BaseModel):
id: int
name: str
username: str
email: EmailStr
phone: str | None
profile_image_url: str
department_id: int | None
company_id: int | None
class ClientRead(BaseModel):
id: int
name: str
contact: str
phone: str
email: str
The fields in the models correspond to those listed.
I expect the output to be as follows:
{
'id': 1,
'project_code': 'ABC1',
'status': 'Approved',
'name': 'Project Example',
'start_date': datetime.datetime(2024, 6, 5, 9, 26, 48, 914000, tzinfo=datetime.timezone.utc),
'end_date': datetime.datetime(2024, 6, 5, 9, 26, 48, 914000, tzinfo=datetime.timezone.utc),
'client': None,
'department': None,
'assignee': None
}
Hello, has the issue been resolved? I am still waiting for the solution and am currently avoiding this bug. Thank you!
I finished this yesterday, I'm just writing the tests and fixing some typing stuff