Repository: igorbenav/fastcrud (python)

Issue:
Nested join - object returned with all fields set to None
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:

```python
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.

```python
{
    '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)?
"

Pull request:
null primary key bug fixed
closes #102 

Patch:
diff --git a/fastcrud/crud/fast_crud.py b/fastcrud/crud/fast_crud.py
index da08c74..82c3e3d 100644
--- a/fastcrud/crud/fast_crud.py
+++ b/fastcrud/crud/fast_crud.py
@@ -17,6 +17,7 @@ from .helper import (
     _auto_detect_join_condition,
     _nest_join_data,
     _nest_multi_join_data,
+    _handle_null_primary_key_multi_join,
     JoinConfig,
 )
 
@@ -179,6 +180,7 @@ class FastCRUD(
         # Now 'archived' and 'archived_at' will be used for soft delete operations.
         ```
     """
+
     _SUPPORTED_FILTERS = {
         "gt": lambda column: column.__gt__,
         "lt": lambda column: column.__lt__,
@@ -215,13 +217,13 @@ class FastCRUD(
         self._primary_keys = _get_primary_keys(self.model)
 
     def _get_sqlalchemy_filter(
-        self, operator: str, value: Any,
-    ) ->Optional[Callable[[str], Callable]]:
-        if operator in {'in', 'not_in', 'between'}:
+        self,
+        operator: str,
+        value: Any,
+    ) -> Optional[Callable[[str], Callable]]:
+        if operator in {"in", "not_in", "between"}:
             if not isinstance(value, (tuple, list, set)):
-                raise ValueError(
-                    f"<{operator}> filter must be tuple, list or set"
-                )
+                raise ValueError(f"<{operator}> filter must be tuple, list or set")
         return self._SUPPORTED_FILTERS.get(operator)
 
     def _parse_filters(
@@ -236,20 +238,22 @@ class FastCRUD(
                 column = getattr(model, field_name, None)
                 if column is None:
                     raise ValueError(f"Invalid filter column: {field_name}")
-                if op == 'or':
+                if op == "or":
                     or_filters = [
                         sqlalchemy_filter(column)(or_value)
                         for or_key, or_value in value.items()
-                        if (sqlalchemy_filter := self._get_sqlalchemy_filter(
-                            or_key, value)) is not None
+                        if (
+                            sqlalchemy_filter := self._get_sqlalchemy_filter(
+                                or_key, value
+                            )
+                        )
+                        is not None
                     ]
                     filters.append(or_(*or_filters))
                 else:
                     sqlalchemy_filter = self._get_sqlalchemy_filter(op, value)
                     if sqlalchemy_filter:
-                        filters.append(
-                                sqlalchemy_filter(column)(value)
-                        )
+                        filters.append(sqlalchemy_filter(column)(value))
             else:
                 column = getattr(model, key, None)
                 if column is not None:
@@ -1401,6 +1405,9 @@ class FastCRUD(
         if (limit is not None and limit < 0) or offset < 0:
             raise ValueError("Limit and offset must be non-negative.")
 
+        if relationship_type is None:
+            relationship_type = "one-to-one"
+
         primary_select = _extract_matching_columns_from_schema(
             model=self.model, schema=schema_to_select
         )
@@ -1485,7 +1492,7 @@ class FastCRUD(
                 },
             )
         else:
-            nested_data = data
+            nested_data = _handle_null_primary_key_multi_join(data, join_definitions)
 
         response: dict[str, Any] = {"data": nested_data}
 
diff --git a/fastcrud/crud/helper.py b/fastcrud/crud/helper.py
index 25ea3e5..d45e68e 100644
--- a/fastcrud/crud/helper.py
+++ b/fastcrud/crud/helper.py
@@ -18,7 +18,7 @@ class JoinConfig(BaseModel):
     join_type: str = "left"
     alias: Optional[AliasedClass] = None
     filters: Optional[dict] = None
-    relationship_type: Optional[str] = None
+    relationship_type: Optional[str] = "one-to-one"
 
     model_config = ConfigDict(arbitrary_types_allowed=True)
 
@@ -170,7 +170,7 @@ def _handle_one_to_one(nested_data, nested_key, nested_field, value):
             }
         }
     """
-    if nested_key not in nested_data:
+    if nested_key not in nested_data or not isinstance(nested_data[nested_key], dict):
         nested_data[nested_key] = {}
     nested_data[nested_key][nested_field] = value
     return nested_data
@@ -383,6 +383,13 @@ def _nest_join_data(
                 ):
                     nested_data[nested_key] = []
 
+        if nested_key in nested_data and isinstance(nested_data[nested_key], dict):
+            if (
+                join_primary_key in nested_data[nested_key]
+                and nested_data[nested_key][join_primary_key] is None
+            ):
+                nested_data[nested_key] = None
+
     assert nested_data is not None, "Couldn't nest the data."
     return nested_data
 
@@ -486,6 +493,10 @@ def _nest_multi_join_data(
                         item[join_primary_key] is None for item in value
                     ):  # pragma: no cover
                         new_row[key] = []
+                    elif (
+                        isinstance(value, dict) and value[join_primary_key] is None
+                    ):  # pragma: no cover
+                        new_row[key] = None
 
                 pre_nested_data[primary_key_value] = new_row
             else:
@@ -516,3 +527,33 @@ def _nest_multi_join_data(
                 nested_data[i] = schema_to_select(**item)
 
     return nested_data
+
+
+def _handle_null_primary_key_multi_join(
+    data: list[Union[dict[str, Any], BaseModel]], join_definitions: list[JoinConfig]
+) -> list[Union[dict[str, Any], BaseModel]]:
+    for item in data:
+        item_dict = item if isinstance(item, dict) else item.model_dump()
+
+        for join in join_definitions:
+            join_prefix = join.join_prefix or ""
+            nested_key = (
+                join_prefix.rstrip("_") if join_prefix else join.model.__tablename__
+            )
+
+            if nested_key in item_dict and isinstance(item_dict[nested_key], dict):
+                join_primary_key = _get_primary_key(join.model)
+
+                primary_key = join_primary_key
+                if join_primary_key:
+                    if (
+                        primary_key in item_dict[nested_key]
+                        and item_dict[nested_key][primary_key] is None
+                    ):  # pragma: no cover
+                        item_dict[nested_key] = None
+
+        if isinstance(item, BaseModel):
+            for key, value in item_dict.items():
+                setattr(item, key, value)
+
+    return data


Test patch:
diff --git a/tests/sqlalchemy/conftest.py b/tests/sqlalchemy/conftest.py
index 2b0a03f..b2a896c 100644
--- a/tests/sqlalchemy/conftest.py
+++ b/tests/sqlalchemy/conftest.py
@@ -130,6 +130,48 @@ class Article(Base):
 Card.articles = relationship("Article", order_by=Article.id, back_populates="card")
 
 
+class Client(Base):
+    __tablename__ = "clients"
+    id = Column(Integer, primary_key=True)
+    name = Column(String, nullable=False)
+    contact = Column(String, nullable=False)
+    phone = Column(String, nullable=False)
+    email = Column(String, nullable=False)
+
+
+class Department(Base):
+    __tablename__ = "departments"
+    id = Column(Integer, primary_key=True)
+    name = Column(String, nullable=False)
+
+
+class User(Base):
+    __tablename__ = "users"
+    id = Column(Integer, primary_key=True)
+    name = Column(String, nullable=False)
+    username = Column(String, nullable=False, unique=True)
+    email = Column(String, nullable=False, unique=True)
+    phone = Column(String, nullable=True)
+    profile_image_url = Column(String, nullable=True)
+    department_id = Column(Integer, ForeignKey("departments.id"), nullable=True)
+    company_id = Column(Integer, ForeignKey("clients.id"), nullable=True)
+    department = relationship("Department", backref="users")
+    company = relationship("Client", backref="users")
+
+
+class Task(Base):
+    __tablename__ = "tasks"
+    id = Column(Integer, primary_key=True)
+    name = Column(String, nullable=False)
+    description = Column(String, nullable=True)
+    client_id = Column(Integer, ForeignKey("clients.id"), nullable=True)
+    department_id = Column(Integer, ForeignKey("departments.id"), nullable=True)
+    assignee_id = Column(Integer, ForeignKey("users.id"), nullable=True)
+    client = relationship("Client", backref="tasks")
+    department = relationship("Department", backref="tasks")
+    assignee = relationship("User", backref="tasks")
+
+
 class CreateSchemaTest(BaseModel):
     model_config = ConfigDict(extra="forbid")
     name: str
@@ -194,6 +236,42 @@ class CardSchema(BaseModel):
     articles: Optional[list[ArticleSchema]] = []
 
 
+class DepartmentRead(BaseModel):
+    id: int
+    name: str
+
+
+class UserReadSub(BaseModel):
+    id: int
+    name: str
+    username: str
+    email: str
+    phone: Optional[str]
+    profile_image_url: str
+    department_id: Optional[int]
+    company_id: Optional[int]
+
+
+class ClientRead(BaseModel):
+    id: int
+    name: str
+    contact: str
+    phone: str
+    email: str
+
+
+class TaskReadSub(BaseModel):
+    id: int
+    name: str
+    description: Optional[str]
+
+
+class TaskRead(TaskReadSub):
+    department: Optional[DepartmentRead]
+    assignee: Optional[UserReadSub]
+    client: Optional[ClientRead]
+
+
 async_engine = create_async_engine(
     "sqlite+aiosqlite:///:memory:", echo=True, future=True
 )
diff --git a/tests/sqlalchemy/core/test_parse_filters.py b/tests/sqlalchemy/core/test_parse_filters.py
index 58aa813..5320542 100644
--- a/tests/sqlalchemy/core/test_parse_filters.py
+++ b/tests/sqlalchemy/core/test_parse_filters.py
@@ -25,9 +25,7 @@ async def test_parse_filters_multiple_conditions(test_model):
 async def test_parse_filters_or_condition(test_model):
     fast_crud = FastCRUD(test_model)
 
-    filters = fast_crud._parse_filters(
-        name__or={'gt': 1, 'lt': 5}
-    )
+    filters = fast_crud._parse_filters(name__or={"gt": 1, "lt": 5})
     assert len(filters) == 1
     assert str(filters[0]) == "test.name > :name_1 OR test.name < :name_2"
 
diff --git a/tests/sqlalchemy/crud/test_get_joined.py b/tests/sqlalchemy/crud/test_get_joined.py
index 5f40e93..864b4ea 100644
--- a/tests/sqlalchemy/crud/test_get_joined.py
+++ b/tests/sqlalchemy/crud/test_get_joined.py
@@ -13,6 +13,14 @@ from ...sqlalchemy.conftest import (
     ReadSchemaTest,
     Article,
     Card,
+    Client,
+    Department,
+    User,
+    Task,
+    ClientRead,
+    DepartmentRead,
+    UserReadSub,
+    TaskRead,
 )
 
 
@@ -565,3 +573,143 @@ async def test_get_joined_card_with_articles(async_session):
     assert all(
         "title" in article for article in result["articles"]
     ), "Each article should have a title."
+
+
+@pytest.mark.asyncio
+async def test_get_joined_nested_data_none_dict(async_session):
+    clients = [
+        Client(
+            name="Client A",
+            contact="Contact A",
+            phone="111-111-1111",
+            email="a@client.com",
+        ),
+        Client(
+            name="Client B",
+            contact="Contact B",
+            phone="222-222-2222",
+            email="b@client.com",
+        ),
+    ]
+    async_session.add_all(clients)
+    await async_session.flush()
+
+    departments = [
+        Department(name="Department A"),
+        Department(name="Department B"),
+    ]
+    async_session.add_all(departments)
+    await async_session.flush()
+
+    users = [
+        User(
+            name="User A",
+            username="usera",
+            email="usera@example.com",
+            phone="123-123-1234",
+        ),
+        User(
+            name="User B",
+            username="userb",
+            email="userb@example.com",
+            phone="234-234-2345",
+        ),
+    ]
+    async_session.add_all(users)
+    await async_session.flush()
+
+    tasks = [
+        Task(
+            name="Task 1",
+            description="Task 1 Description",
+            client_id=clients[0].id,
+            department_id=departments[0].id,
+            assignee_id=users[0].id,
+        ),
+        Task(
+            name="Task 2",
+            description="Task 2 Description",
+            client_id=clients[1].id,
+            department_id=departments[1].id,
+            assignee_id=users[1].id,
+        ),
+        Task(
+            name="Task 3",
+            description="Task 3 Description",
+            client_id=None,
+            department_id=None,
+            assignee_id=None,
+        ),
+    ]
+    async_session.add_all(tasks)
+    await async_session.commit()
+
+    task_crud = FastCRUD(Task)
+
+    joins_config = [
+        JoinConfig(
+            model=Client,
+            join_on=Task.client_id == Client.id,
+            join_prefix="client_",
+            schema_to_select=ClientRead,
+            join_type="left",
+        ),
+        JoinConfig(
+            model=Department,
+            join_on=Task.department_id == Department.id,
+            join_prefix="department_",
+            schema_to_select=DepartmentRead,
+            join_type="left",
+        ),
+        JoinConfig(
+            model=User,
+            join_on=Task.assignee_id == User.id,
+            join_prefix="assignee_",
+            schema_to_select=UserReadSub,
+            join_type="left",
+        ),
+    ]
+
+    task1_result = await task_crud.get_joined(
+        db=async_session,
+        id=tasks[0].id,
+        schema_to_select=TaskRead,
+        joins_config=joins_config,
+        nest_joins=True,
+    )
+
+    assert task1_result is not None, "No data returned from the database."
+    assert (
+        "client" in task1_result
+    ), "Nested client data should be present under key 'client'"
+    assert (
+        "department" in task1_result
+    ), "Nested department data should be present under key 'department'"
+    assert (
+        "assignee" in task1_result
+    ), "Nested assignee data should be present under key 'assignee'"
+    assert task1_result["client"] is not None, "Task 1 should have a client."
+    assert task1_result["department"] is not None, "Task 1 should have a department."
+    assert task1_result["assignee"] is not None, "Task 1 should have an assignee."
+
+    task3_result = await task_crud.get_joined(
+        db=async_session,
+        id=tasks[2].id,
+        schema_to_select=TaskRead,
+        joins_config=joins_config,
+        nest_joins=True,
+    )
+
+    assert task3_result is not None, "No data returned from the database."
+    assert (
+        "client" in task3_result
+    ), "Nested client data should be present under key 'client'"
+    assert (
+        "department" in task3_result
+    ), "Nested department data should be present under key 'department'"
+    assert (
+        "assignee" in task3_result
+    ), "Nested assignee data should be present under key 'assignee'"
+    assert task3_result["client"] is None, "Task 3 should have no client."
+    assert task3_result["department"] is None, "Task 3 should have no department."
+    assert task3_result["assignee"] is None, "Task 3 should have no assignee."
diff --git a/tests/sqlalchemy/crud/test_get_multi_joined.py b/tests/sqlalchemy/crud/test_get_multi_joined.py
index a9ec4b9..b8a02a6 100644
--- a/tests/sqlalchemy/crud/test_get_multi_joined.py
+++ b/tests/sqlalchemy/crud/test_get_multi_joined.py
@@ -19,6 +19,14 @@ from ...sqlalchemy.conftest import (
     Card,
     ArticleSchema,
     CardSchema,
+    Client,
+    Department,
+    User,
+    Task,
+    ClientRead,
+    DepartmentRead,
+    UserReadSub,
+    TaskRead,
 )
 
 
@@ -748,18 +756,20 @@ async def test_get_multi_joined_with_nesting(async_session, test_data, test_data
             assert (
                 "category" in item
             ), "Nested category data should be present under key 'category'"
-            assert isinstance(
+            assert item["tier"] is None or isinstance(
                 item["tier"], dict
             ), "Nested tier data should be a dictionary"
-            assert isinstance(
+            assert item["category"] is None or isinstance(
                 item["category"], dict
             ), "Nested category data should be a dictionary"
-            assert (
-                "tier_" not in item["tier"]
-            ), "No prefix should be present in the nested tier keys"
-            assert (
-                "category_" not in item["category"]
-            ), "No prefix should be present in the nested category keys"
+            if item["tier"] is not None:
+                assert (
+                    "tier_" not in item["tier"]
+                ), "No prefix should be present in the nested tier keys"
+            if item["category"] is not None:  # pragma: no cover
+                assert (
+                    "category_" not in item["category"]
+                ), "No prefix should be present in the nested category keys"
 
 
 @pytest.mark.asyncio
@@ -1074,3 +1084,118 @@ async def test_get_multi_joined_card_with_multiple_articles_as_models(async_sess
         card_d, "articles"
     ), "Card D should have nested articles."
     assert len(card_d.articles) == 0, "Card D should have no articles."
+
+
+@pytest.mark.asyncio
+async def test_get_multi_joined_nested_data_none_dict(async_session):
+    clients = [
+        Client(
+            name="Client A",
+            contact="Contact A",
+            phone="111-111-1111",
+            email="a@client.com",
+        ),
+        Client(
+            name="Client B",
+            contact="Contact B",
+            phone="222-222-2222",
+            email="b@client.com",
+        ),
+    ]
+    async_session.add_all(clients)
+    await async_session.flush()
+
+    departments = [
+        Department(name="Department A"),
+        Department(name="Department B"),
+    ]
+    async_session.add_all(departments)
+    await async_session.flush()
+
+    users = [
+        User(
+            name="User A",
+            username="usera",
+            email="usera@example.com",
+            phone="123-123-1234",
+        ),
+        User(
+            name="User B",
+            username="userb",
+            email="userb@example.com",
+            phone="234-234-2345",
+        ),
+    ]
+    async_session.add_all(users)
+    await async_session.flush()
+
+    tasks = [
+        Task(
+            name="Task 1",
+            description="Task 1 Description",
+            client_id=clients[0].id,
+            department_id=departments[0].id,
+            assignee_id=users[0].id,
+        ),
+        Task(
+            name="Task 2",
+            description="Task 2 Description",
+            client_id=clients[1].id,
+            department_id=departments[1].id,
+            assignee_id=users[1].id,
+        ),
+        Task(
+            name="Task 3",
+            description="Task 3 Description",
+            client_id=None,
+            department_id=None,
+            assignee_id=None,
+        ),
+    ]
+    async_session.add_all(tasks)
+    await async_session.commit()
+
+    task_crud = FastCRUD(Task)
+
+    result = await task_crud.get_multi_joined(
+        db=async_session,
+        nest_joins=True,
+        schema_to_select=TaskRead,
+        joins_config=[
+            JoinConfig(
+                model=Client,
+                join_on=Task.client_id == Client.id,
+                join_prefix="client",
+                schema_to_select=ClientRead,
+                join_type="left",
+            ),
+            JoinConfig(
+                model=Department,
+                join_on=Task.department_id == Department.id,
+                join_prefix="department",
+                schema_to_select=DepartmentRead,
+                join_type="left",
+            ),
+            JoinConfig(
+                model=User,
+                join_on=Task.assignee_id == User.id,
+                join_prefix="assignee",
+                schema_to_select=UserReadSub,
+                join_type="left",
+            ),
+        ],
+    )
+
+    assert result is not None, "No data returned from the database."
+    data = result["data"]
+    assert len(data) == 3, "Expected three tasks."
+
+    task1 = data[0]
+    assert task1["client"] is not None, "Task 1 should have a client."
+    assert task1["department"] is not None, "Task 1 should have a department."
+    assert task1["assignee"] is not None, "Task 1 should have an assignee."
+
+    task3 = data[2]
+    assert task3["client"] is None, "Task 3 should have no client."
+    assert task3["department"] is None, "Task 3 should have no department."
+    assert task3["assignee"] is None, "Task 3 should have no assignee."
diff --git a/tests/sqlmodel/conftest.py b/tests/sqlmodel/conftest.py
index 9d29a9d..d4b2ef8 100644
--- a/tests/sqlmodel/conftest.py
+++ b/tests/sqlmodel/conftest.py
@@ -105,6 +105,53 @@ class Article(SQLModel, table=True):
     card: Optional[Card] = Relationship(back_populates="articles")
 
 
+class Client(SQLModel, table=True):
+    __tablename__ = "clients"
+    id: Optional[int] = Field(default=None, primary_key=True)
+    name: str = Field(nullable=False)
+    contact: str = Field(nullable=False)
+    phone: str = Field(nullable=False)
+    email: str = Field(nullable=False)
+    tasks: list["Task"] = Relationship(back_populates="client")
+    users: list["User"] = Relationship(back_populates="company")
+
+
+class Department(SQLModel, table=True):
+    __tablename__ = "departments"
+    id: Optional[int] = Field(default=None, primary_key=True)
+    name: str = Field(nullable=False)
+    tasks: list["Task"] = Relationship(back_populates="department")
+    users: list["User"] = Relationship(back_populates="department")
+
+
+class User(SQLModel, table=True):
+    __tablename__ = "users"
+    id: Optional[int] = Field(default=None, primary_key=True)
+    name: str = Field(nullable=False)
+    username: str = Field(nullable=False, unique=True)
+    email: str = Field(nullable=False, unique=True)
+    phone: Optional[str] = Field(default=None)
+    profile_image_url: Optional[str] = Field(default=None)
+    department_id: Optional[int] = Field(default=None, foreign_key="departments.id")
+    company_id: Optional[int] = Field(default=None, foreign_key="clients.id")
+    department: Optional[Department] = Relationship(back_populates="users")
+    company: Optional[Client] = Relationship(back_populates="users")
+    tasks: list["Task"] = Relationship(back_populates="assignee")
+
+
+class Task(SQLModel, table=True):
+    __tablename__ = "tasks"
+    id: Optional[int] = Field(default=None, primary_key=True)
+    name: str = Field(nullable=False)
+    description: Optional[str] = Field(default=None)
+    client_id: Optional[int] = Field(default=None, foreign_key="clients.id")
+    department_id: Optional[int] = Field(default=None, foreign_key="departments.id")
+    assignee_id: Optional[int] = Field(default=None, foreign_key="users.id")
+    client: Optional[Client] = Relationship(back_populates="tasks")
+    department: Optional[Department] = Relationship(back_populates="tasks")
+    assignee: Optional[User] = Relationship(back_populates="tasks")
+
+
 class CreateSchemaTest(SQLModel):
     model_config = ConfigDict(extra="forbid")
     name: str
@@ -168,7 +215,7 @@ class MultiPkCreate(SQLModel):
 
 class MultiPkSchema(SQLModel):
     name: str
-    test_id: int = None
+    test_id: Optional[int] = None
 
 
 class ArticleSchema(SQLModel):
@@ -183,6 +230,42 @@ class CardSchema(SQLModel):
     articles: Optional[list[ArticleSchema]] = []
 
 
+class DepartmentRead(SQLModel):
+    id: int
+    name: str
+
+
+class UserReadSub(SQLModel):
+    id: int
+    name: str
+    username: str
+    email: str
+    phone: Optional[str]
+    profile_image_url: str
+    department_id: Optional[int]
+    company_id: Optional[int]
+
+
+class ClientRead(SQLModel):
+    id: int
+    name: str
+    contact: str
+    phone: str
+    email: str
+
+
+class TaskReadSub(SQLModel):
+    id: int
+    name: str
+    description: Optional[str]
+
+
+class TaskRead(TaskReadSub):
+    department: Optional[DepartmentRead]
+    assignee: Optional[UserReadSub]
+    client: Optional[ClientRead]
+
+
 async_engine = create_async_engine(
     "sqlite+aiosqlite:///:memory:", echo=True, future=True
 )
diff --git a/tests/sqlmodel/core/test_parse_filters.py b/tests/sqlmodel/core/test_parse_filters.py
index e5cecb6..5320542 100644
--- a/tests/sqlmodel/core/test_parse_filters.py
+++ b/tests/sqlmodel/core/test_parse_filters.py
@@ -25,9 +25,7 @@ async def test_parse_filters_multiple_conditions(test_model):
 async def test_parse_filters_or_condition(test_model):
     fast_crud = FastCRUD(test_model)
 
-    filters = fast_crud._parse_filters(
-        name__or={'gt': 1, 'lt': 5}
-    )
+    filters = fast_crud._parse_filters(name__or={"gt": 1, "lt": 5})
     assert len(filters) == 1
     assert str(filters[0]) == "test.name > :name_1 OR test.name < :name_2"
 
@@ -40,6 +38,16 @@ async def test_parse_filters_contained_in(test_model):
     assert str(filters[0]) == "test.category_id IN (__[POSTCOMPILE_category_id_1])"
 
 
+@pytest.mark.asyncio
+async def test_parse_filters_not_contained_in(test_model):
+    fast_crud = FastCRUD(test_model)
+    filters = fast_crud._parse_filters(category_id__not_in=[1, 2])
+    assert len(filters) == 1
+    assert (
+        str(filters[0]) == "(test.category_id NOT IN (__[POSTCOMPILE_category_id_1]))"
+    )
+
+
 @pytest.mark.asyncio
 @pytest.mark.parametrize("operator", ("in", "not_in", "between"))
 async def test_parse_filters_raises_exception(test_model, operator: str):
diff --git a/tests/sqlmodel/crud/test_get_joined.py b/tests/sqlmodel/crud/test_get_joined.py
index 2d63490..3faa06b 100644
--- a/tests/sqlmodel/crud/test_get_joined.py
+++ b/tests/sqlmodel/crud/test_get_joined.py
@@ -13,6 +13,14 @@ from ...sqlmodel.conftest import (
     ReadSchemaTest,
     Article,
     Card,
+    Client,
+    Department,
+    User,
+    Task,
+    ClientRead,
+    DepartmentRead,
+    UserReadSub,
+    TaskRead,
 )
 
 
@@ -566,3 +574,143 @@ async def test_get_joined_card_with_articles(async_session):
     assert all(
         "title" in article for article in result["articles"]
     ), "Each article should have a title."
+
+
+@pytest.mark.asyncio
+async def test_get_joined_nested_data_none_dict(async_session):
+    clients = [
+        Client(
+            name="Client A",
+            contact="Contact A",
+            phone="111-111-1111",
+            email="a@client.com",
+        ),
+        Client(
+            name="Client B",
+            contact="Contact B",
+            phone="222-222-2222",
+            email="b@client.com",
+        ),
+    ]
+    async_session.add_all(clients)
+    await async_session.flush()
+
+    departments = [
+        Department(name="Department A"),
+        Department(name="Department B"),
+    ]
+    async_session.add_all(departments)
+    await async_session.flush()
+
+    users = [
+        User(
+            name="User A",
+            username="usera",
+            email="usera@example.com",
+            phone="123-123-1234",
+        ),
+        User(
+            name="User B",
+            username="userb",
+            email="userb@example.com",
+            phone="234-234-2345",
+        ),
+    ]
+    async_session.add_all(users)
+    await async_session.flush()
+
+    tasks = [
+        Task(
+            name="Task 1",
+            description="Task 1 Description",
+            client_id=clients[0].id,
+            department_id=departments[0].id,
+            assignee_id=users[0].id,
+        ),
+        Task(
+            name="Task 2",
+            description="Task 2 Description",
+            client_id=clients[1].id,
+            department_id=departments[1].id,
+            assignee_id=users[1].id,
+        ),
+        Task(
+            name="Task 3",
+            description="Task 3 Description",
+            client_id=None,
+            department_id=None,
+            assignee_id=None,
+        ),
+    ]
+    async_session.add_all(tasks)
+    await async_session.commit()
+
+    task_crud = FastCRUD(Task)
+
+    joins_config = [
+        JoinConfig(
+            model=Client,
+            join_on=Task.client_id == Client.id,
+            join_prefix="client_",
+            schema_to_select=ClientRead,
+            join_type="left",
+        ),
+        JoinConfig(
+            model=Department,
+            join_on=Task.department_id == Department.id,
+            join_prefix="department_",
+            schema_to_select=DepartmentRead,
+            join_type="left",
+        ),
+        JoinConfig(
+            model=User,
+            join_on=Task.assignee_id == User.id,
+            join_prefix="assignee_",
+            schema_to_select=UserReadSub,
+            join_type="left",
+        ),
+    ]
+
+    task1_result = await task_crud.get_joined(
+        db=async_session,
+        id=tasks[0].id,
+        schema_to_select=TaskRead,
+        joins_config=joins_config,
+        nest_joins=True,
+    )
+
+    assert task1_result is not None, "No data returned from the database."
+    assert (
+        "client" in task1_result
+    ), "Nested client data should be present under key 'client'"
+    assert (
+        "department" in task1_result
+    ), "Nested department data should be present under key 'department'"
+    assert (
+        "assignee" in task1_result
+    ), "Nested assignee data should be present under key 'assignee'"
+    assert task1_result["client"] is not None, "Task 1 should have a client."
+    assert task1_result["department"] is not None, "Task 1 should have a department."
+    assert task1_result["assignee"] is not None, "Task 1 should have an assignee."
+
+    task3_result = await task_crud.get_joined(
+        db=async_session,
+        id=tasks[2].id,
+        schema_to_select=TaskRead,
+        joins_config=joins_config,
+        nest_joins=True,
+    )
+
+    assert task3_result is not None, "No data returned from the database."
+    assert (
+        "client" in task3_result
+    ), "Nested client data should be present under key 'client'"
+    assert (
+        "department" in task3_result
+    ), "Nested department data should be present under key 'department'"
+    assert (
+        "assignee" in task3_result
+    ), "Nested assignee data should be present under key 'assignee'"
+    assert task3_result["client"] is None, "Task 3 should have no client."
+    assert task3_result["department"] is None, "Task 3 should have no department."
+    assert task3_result["assignee"] is None, "Task 3 should have no assignee."
diff --git a/tests/sqlmodel/crud/test_get_multi_joined.py b/tests/sqlmodel/crud/test_get_multi_joined.py
index 79e4fad..4d91318 100644
--- a/tests/sqlmodel/crud/test_get_multi_joined.py
+++ b/tests/sqlmodel/crud/test_get_multi_joined.py
@@ -19,6 +19,14 @@ from ...sqlmodel.conftest import (
     Card,
     ArticleSchema,
     CardSchema,
+    Client,
+    Department,
+    User,
+    Task,
+    ClientRead,
+    DepartmentRead,
+    UserReadSub,
+    TaskRead,
 )
 
 
@@ -748,18 +756,20 @@ async def test_get_multi_joined_with_nesting(async_session, test_data, test_data
             assert (
                 "category" in item
             ), "Nested category data should be present under key 'category'"
-            assert isinstance(
+            assert item["tier"] is None or isinstance(
                 item["tier"], dict
             ), "Nested tier data should be a dictionary"
-            assert isinstance(
+            assert item["category"] is None or isinstance(
                 item["category"], dict
             ), "Nested category data should be a dictionary"
-            assert (
-                "tier_" not in item["tier"]
-            ), "No prefix should be present in the nested tier keys"
-            assert (
-                "category_" not in item["category"]
-            ), "No prefix should be present in the nested category keys"
+            if item["tier"] is not None:
+                assert (
+                    "tier_" not in item["tier"]
+                ), "No prefix should be present in the nested tier keys"
+            if item["category"] is not None:  # pragma: no cover
+                assert (
+                    "category_" not in item["category"]
+                ), "No prefix should be present in the nested category keys"
 
 
 @pytest.mark.asyncio
@@ -1074,3 +1084,118 @@ async def test_get_multi_joined_card_with_multiple_articles_as_models(async_sess
         card_d, "articles"
     ), "Card D should have nested articles."
     assert len(card_d.articles) == 0, "Card D should have no articles."
+
+
+@pytest.mark.asyncio
+async def test_get_multi_joined_nested_data_none_dict(async_session):
+    clients = [
+        Client(
+            name="Client A",
+            contact="Contact A",
+            phone="111-111-1111",
+            email="a@client.com",
+        ),
+        Client(
+            name="Client B",
+            contact="Contact B",
+            phone="222-222-2222",
+            email="b@client.com",
+        ),
+    ]
+    async_session.add_all(clients)
+    await async_session.flush()
+
+    departments = [
+        Department(name="Department A"),
+        Department(name="Department B"),
+    ]
+    async_session.add_all(departments)
+    await async_session.flush()
+
+    users = [
+        User(
+            name="User A",
+            username="usera",
+            email="usera@example.com",
+            phone="123-123-1234",
+        ),
+        User(
+            name="User B",
+            username="userb",
+            email="userb@example.com",
+            phone="234-234-2345",
+        ),
+    ]
+    async_session.add_all(users)
+    await async_session.flush()
+
+    tasks = [
+        Task(
+            name="Task 1",
+            description="Task 1 Description",
+            client_id=clients[0].id,
+            department_id=departments[0].id,
+            assignee_id=users[0].id,
+        ),
+        Task(
+            name="Task 2",
+            description="Task 2 Description",
+            client_id=clients[1].id,
+            department_id=departments[1].id,
+            assignee_id=users[1].id,
+        ),
+        Task(
+            name="Task 3",
+            description="Task 3 Description",
+            client_id=None,
+            department_id=None,
+            assignee_id=None,
+        ),
+    ]
+    async_session.add_all(tasks)
+    await async_session.commit()
+
+    task_crud = FastCRUD(Task)
+
+    result = await task_crud.get_multi_joined(
+        db=async_session,
+        nest_joins=True,
+        schema_to_select=TaskRead,
+        joins_config=[
+            JoinConfig(
+                model=Client,
+                join_on=Task.client_id == Client.id,
+                join_prefix="client",
+                schema_to_select=ClientRead,
+                join_type="left",
+            ),
+            JoinConfig(
+                model=Department,
+                join_on=Task.department_id == Department.id,
+                join_prefix="department",
+                schema_to_select=DepartmentRead,
+                join_type="left",
+            ),
+            JoinConfig(
+                model=User,
+                join_on=Task.assignee_id == User.id,
+                join_prefix="assignee",
+                schema_to_select=UserReadSub,
+                join_type="left",
+            ),
+        ],
+    )
+
+    assert result is not None, "No data returned from the database."
+    data = result["data"]
+    assert len(data) == 3, "Expected three tasks."
+
+    task1 = data[0]
+    assert task1["client"] is not None, "Task 1 should have a client."
+    assert task1["department"] is not None, "Task 1 should have a department."
+    assert task1["assignee"] is not None, "Task 1 should have an assignee."
+
+    task3 = data[2]
+    assert task3["client"] is None, "Task 3 should have no client."
+    assert task3["department"] is None, "Task 3 should have no department."
+    assert task3["assignee"] is None, "Task 3 should have no assignee."