Repository: igorbenav/fastcrud (python)

Issue:
Using multi_joined with response object in object
@igorbenav I have more a question about get multi_joined. When i joined 2 table many to one. I can't not create response object in object. Example: I only create like that
```json
"data": [
    {
      "id": 0,
      "title": "string",
      "template_id": 0,
      "description": "string",
      "created_at": "2024-04-14T16:28:01.403Z",
      "created_by": "string",
      "template_title": "Example text for encryption",
      "template_description": "Example description for encryption template"
    }
  ]

```
This is response that i want
```json
"data": [
    {
      "id": 0,
      "title": "string",
      "template_id": 0,
      "description": "string",
      "created_at": "2024-04-14T16:28:01.403Z",
      "created_by": "string",
      "created_at": "2024-04-14T16:28:01.403Z",
      "created_by": "string",
      "template":{
              "title": "Example text for encryption",
              "description": "Example description for encryption template"
       }
    }
  ]
```
Please help me :(

Pull request description:
Nested responses
Closes #52 

Patch:
diff --git a/docs/advanced/joins.md b/docs/advanced/joins.md
index 1342411..b17f674 100644
--- a/docs/advanced/joins.md
+++ b/docs/advanced/joins.md
@@ -71,6 +71,80 @@ tasks_with_users = await task_crud.get_joined(
 )
 ```
 
+#### Getting Joined Data Nested
+
+Note that by default, `FastCRUD` joins all the data and returns it in a single dictionary.
+Let's define two tables:
+```python
+class User(Base):
+    __tablename__ = "user"
+    id = Column(Integer, primary_key=True)
+    name = Column(String)
+    tier_id = Column(Integer, ForeignKey("tier.id"))
+
+
+class Tier(Base):
+    __tablename__ = "tier"
+    id = Column(Integer, primary_key=True)
+    name = Column(String, unique=True)
+```
+
+And join them with `FastCRUD`:
+
+```python
+user_tier = await user_crud.get_joined(
+    db=db,
+    model=Tier,
+    join_on=User.tier_id == Tier.id,
+    join_type="left",
+    join_prefix="tier_",,
+    id=1
+)
+```
+
+We'll get:
+
+```javascript
+{
+    "id": 1,
+    "name": "Example",
+    "tier_id": 1,
+    "tier_name": "Free",
+}
+```
+
+If you want the joined data in a nested dictionary instead, you may just pass `nest_joins=True`:
+
+```python
+user_tier = await user_crud.get_joined(
+    db=db,
+    model=Tier,
+    join_on=User.tier_id == Tier.id,
+    join_type="left",
+    join_prefix="tier_",
+    nest_joins=True,
+    id=1,
+)
+```
+
+And you will get:
+
+```javascript
+{
+    "id": 1,
+    "name": "Example",
+    "tier": {
+        "id": 1,
+        "name": "Free",
+    },
+}
+```
+
+This works for both `get_joined` and `get_multi_joined`.
+
+!!! WARNING
+    Note that the final `"_"` in the passed `"tier_"` is stripped.
+
 ### Complex Joins Using `JoinConfig`
 
 When dealing with more complex join conditions, such as multiple joins, self-referential joins, or needing to specify aliases and filters, `JoinConfig` instances become the norm. They offer granular control over each join's aspects, enabling precise and efficient data retrieval.
diff --git a/docs/changelog.md b/docs/changelog.md
index e20f66b..fd5ecfe 100644
--- a/docs/changelog.md
+++ b/docs/changelog.md
@@ -6,6 +6,110 @@ The Changelog documents all notable changes made to FastCRUD. This includes new
 
 ___
 
+## [0.11.1] - Apr 22, 2024
+
+#### Added
+- `one_or_none` parameter to FastCRUD `get` method (default `False`)
+- `nest_joins` parameter to FastCRUD `get_joined` and `get_multi_joined` (default `False`)
+
+#### Detailed Changes
+##### `get`
+By default, the `get` method in `FastCRUD` returns the `first` object matching all the filters it finds.
+
+If you want to ensure the `one_or_none` behavior, you may pass the parameter as `True`:
+
+```python
+crud.get(
+    async_session, 
+    one_or_none=True, 
+    category_id=1
+)
+```
+
+##### `get_joined` and `get_multi_joined`
+By default, `FastCRUD` joins all the data and returns it in a single dictionary.
+Let's define two tables:
+```python
+class User(Base):
+    __tablename__ = "user"
+    id = Column(Integer, primary_key=True)
+    name = Column(String)
+    tier_id = Column(Integer, ForeignKey("tier.id"))
+
+
+class Tier(Base):
+    __tablename__ = "tier"
+    id = Column(Integer, primary_key=True)
+    name = Column(String, unique=True)
+```
+
+And join them with `FastCRUD`:
+
+```python
+user_tier = await user_crud.get_joined(
+    db=db,
+    model=Tier,
+    join_on=User.tier_id == Tier.id,
+    join_type="left",
+    join_prefix="tier_",,
+    id=1
+)
+```
+
+We'll get:
+
+```javascript
+{
+    "id": 1,
+    "name": "Example",
+    "tier_id": 1,
+    "tier_name": "Free",
+}
+```
+
+Now, if you want the joined data in a nested dictionary instead, you may just pass `nest_joins=True`:
+
+```python
+user_tier = await user_crud.get_joined(
+    db=db,
+    model=Tier,
+    join_on=User.tier_id == Tier.id,
+    join_type="left",
+    join_prefix="tier_",
+    nest_joins=True,
+    id=1,
+)
+```
+
+And you will get:
+
+```javascript
+{
+    "id": 1,
+    "name": "Example",
+    "tier": {
+        "id": 1,
+        "name": "Free",
+    },
+}
+```
+
+This works for both `get_joined` and `get_multi_joined`.
+
+!!! WARNING
+    Note that the final `"_"` in the passed `"tier_"` is stripped.
+
+#### What's Changed
+- Reuse of `select` method in `FastCRUD`
+- Skip count call when possible
+- Add `one_or_none` parameter to FastCRUD `get` method
+- Add `nest_joins` parameter to FastCRUD `get_joined` and `get_multi_joined`
+
+#### New Contributors
+- [@JakNowy](https://github.com/JakNowy) made their first contribution in PR #51.
+
+**Full Changelog**: [View the full changelog](https://github.com/igorbenav/fastcrud/compare/v0.11.0...v0.11.1)
+
 ## [0.11.0] - Apr 7, 2024
 
 #### Added
diff --git a/docs/usage/crud.md b/docs/usage/crud.md
index 63aaaad..f12ea4f 100644
--- a/docs/usage/crud.md
+++ b/docs/usage/crud.md
@@ -69,6 +69,7 @@ get(
     db: AsyncSession,
     schema_to_select: Optional[type[BaseModel]] = None,
     return_as_model: bool = False,
+    one_or_none: bool = False,
     **kwargs: Any
 ) -> Optional[Union[dict, BaseModel]]
 ```
@@ -231,6 +232,7 @@ get_joined(
     join_type: str = "left",
     join_filters: Optional[dict] = None,
     joins_config: Optional[list[JoinConfig]] = None,
+    nest_joins: bool = False,
     **kwargs: Any,
 ) -> Optional[dict[str, Any]]
 ```
@@ -261,6 +263,7 @@ get_multi_joined(
     join_type: str = "left",
     alias: Optional[str] = None,
     join_filters: Optional[dict] = None,
+    nest_joins: bool = False,
     offset: int = 0,
     limit: int = 100,
     sort_columns: Optional[Union[str, list[str]]] = None,
diff --git a/fastcrud/crud/fast_crud.py b/fastcrud/crud/fast_crud.py
index 7b4dae6..617d7ec 100644
--- a/fastcrud/crud/fast_crud.py
+++ b/fastcrud/crud/fast_crud.py
@@ -15,6 +15,7 @@ from sqlalchemy.sql.selectable import Select
 from .helper import (
     _extract_matching_columns_from_schema,
     _auto_detect_join_condition,
+    _nest_join_data,
     JoinConfig,
 )
 
@@ -379,7 +380,6 @@ class FastCRUD(
 
         Raises:
             ValueError: If return_as_model is True but schema_to_select is not provided.
-            MultipleResultsFound: if `one_or_none` is False and many result correspond to the passed filter.
 
         Returns:
             A dictionary or a Pydantic model instance of the fetched database row, or None if no match is found.
@@ -696,6 +696,7 @@ class FastCRUD(
         alias: Optional[AliasedClass] = None,
         join_filters: Optional[dict] = None,
         joins_config: Optional[list[JoinConfig]] = None,
+        nest_joins: bool = False,
         **kwargs: Any,
     ) -> Optional[dict[str, Any]]:
         """
@@ -719,6 +720,7 @@ class FastCRUD(
             alias: An instance of `AliasedClass` for the join model, useful for self-joins or multiple joins on the same model. Result of `aliased(join_model)`.
             join_filters: Filters applied to the joined model, specified as a dictionary mapping column names to their expected values.
             joins_config: A list of JoinConfig instances, each specifying a model to join with, join condition, optional prefix for column names, schema for selecting specific columns, and the type of join. This parameter enables support for multiple joins.
+            nest_joins: If True, nested data structures will be returned where joined model data are nested under the join_prefix as a dictionary.
             **kwargs: Filters to apply to the primary model query, supporting advanced comparison operators for refined searching.
 
         Returns:
@@ -846,6 +848,34 @@ class FastCRUD(
                 joins_config=joins_config
             )
             ```
+
+            Example of using 'joins_config' for multiple joins with nested joins enabled:
+            ```python
+            from fastcrud import JoinConfig
+
+            result = await crud_user.get_joined(
+                db=session,
+                schema_to_select=UserSchema,
+                joins_config=[
+                    JoinConfig(
+                        model=Tier,
+                        join_on=User.tier_id == Tier.id,
+                        join_prefix="tier_",
+                        schema_to_select=TierSchema,
+                        join_type="left",
+                    ),
+                    JoinConfig(
+                        model=Department,
+                        join_on=User.department_id == Department.id,
+                        join_prefix="dept_",
+                        schema_to_select=DepartmentSchema,
+                        join_type="inner",
+                    )
+                ],
+                nest_joins=True
+            )
+            # Expect 'result' to have 'tier' and 'dept' as nested dictionaries
+            ```
         """
         if joins_config and (
             join_model or join_prefix or join_on or join_schema_to_select or alias
@@ -908,8 +938,11 @@ class FastCRUD(
         db_row = await db.execute(stmt)
         result: Optional[Row] = db_row.first()
         if result is not None:
-            out: dict = dict(result._mapping)
-            return out
+            data: dict = dict(result._mapping)
+            if nest_joins:
+                data = _nest_join_data(data, join_definitions)
+
+            return data
 
         return None
 
@@ -924,6 +957,7 @@ class FastCRUD(
         join_type: str = "left",
         alias: Optional[AliasedClass[Any]] = None,
         join_filters: Optional[dict] = None,
+        nest_joins: bool = False,
         offset: int = 0,
         limit: int = 100,
         sort_columns: Optional[Union[str, list[str]]] = None,
@@ -952,6 +986,7 @@ class FastCRUD(
             join_type: Specifies the type of join operation to perform. Can be "left" for a left outer join or "inner" for an inner join.
             alias: An instance of `AliasedClass` for the join model, useful for self-joins or multiple joins on the same model. Result of `aliased(join_model)`.
             join_filters: Filters applied to the joined model, specified as a dictionary mapping column names to their expected values.
+            nest_joins: If True, nested data structures will be returned where joined model data are nested under the join_prefix as a dictionary.
             offset: The offset (number of records to skip) for pagination.
             limit: The limit (maximum number of records to return) for pagination.
             sort_columns: A single column name or a list of column names on which to apply sorting.
@@ -1130,6 +1165,36 @@ class FastCRUD(
                 limit=10
             )
             ```
+
+            Fetching a list of projects, each with nested details of associated tasks and task creators, using nested joins:
+            ```python
+            projects = await crud.get_multi_joined(
+                db=session,
+                schema_to_select=ProjectSchema,
+                joins_config=[
+                    JoinConfig(
+                        model=Task,
+                        join_on=Project.id == Task.project_id,
+                        join_prefix="task_",
+                        schema_to_select=TaskSchema,
+                        join_type="left",
+                    ),
+                    JoinConfig(
+                        model=User,
+                        join_on=Task.creator_id == User.id,
+                        join_prefix="creator_",
+                        schema_to_select=UserSchema,
+                        join_type="left",
+                        alias=aliased(User, name="task_creator")
+                    )
+                ],
+                nest_joins=True,
+                offset=0,
+                limit=5,
+                sort_columns='project_name',
+                sort_orders='asc'
+            )
+        ```
         """
         if joins_config and (
             join_model or join_prefix or join_on or join_schema_to_select or alias
@@ -1198,27 +1263,36 @@ class FastCRUD(
         stmt = stmt.offset(offset).limit(limit)
 
         result = await db.execute(stmt)
-        data: list[dict] = [dict(row) for row in result.mappings().all()]
+        data: list[Union[dict, BaseModel]] = []
+        for row in result.mappings().all():
+            row_dict = dict(row)
+
+            if nest_joins:
+                row_dict = _nest_join_data(row_dict, join_definitions)
+
+            if return_as_model:
+                if schema_to_select is None:
+                    raise ValueError(
+                        "schema_to_select must be provided when return_as_model is True."
+                    )
+                try:
+                    model_instance = schema_to_select(**row_dict)
+                    data.append(model_instance)
+                except ValidationError as e:
+                    raise ValueError(
+                        f"Data validation error for schema {schema_to_select.__name__}: {e}"
+                    )
+            else:
+                data.append(row_dict)
 
         response: dict[str, Any] = {"data": data}
 
         if return_total_count:
-            total_count = await self.count(db=db, joins_config=joins_config, **kwargs)
+            total_count: int = await self.count(
+                db=db, joins_config=joins_config, **kwargs
+            )
             response["total_count"] = total_count
 
-        if return_as_model:
-            if not schema_to_select:
-                raise ValueError(
-                    "schema_to_select must be provided when return_as_model is True."
-                )
-            try:
-                model_data: list[BaseModel] = [schema_to_select(**row) for row in data]
-                response["data"] = model_data
-            except ValidationError as e:
-                raise ValueError(
-                    f"Data validation error for schema {schema_to_select.__name__}: {e}"
-                )
-
         return response
 
     async def get_multi_by_cursor(
diff --git a/fastcrud/crud/helper.py b/fastcrud/crud/helper.py
index bac1b0c..ad25fbf 100644
--- a/fastcrud/crud/helper.py
+++ b/fastcrud/crud/helper.py
@@ -99,3 +99,23 @@ def _auto_detect_join_condition(
         raise ValueError("Could not automatically get model columns.")
 
     return join_on
+
+
+def _nest_join_data(
+    data: dict[str, Any], join_definitions: list[JoinConfig]
+) -> dict[str, Any]:
+    nested_data: dict = {}
+    for key, value in data.items():
+        nested = False
+        for join in join_definitions:
+            if join.join_prefix and key.startswith(join.join_prefix):
+                nested_key = join.join_prefix.rstrip("_")
+                nested_field = key[len(join.join_prefix) :]
+                if nested_key not in nested_data:
+                    nested_data[nested_key] = {}
+                nested_data[nested_key][nested_field] = value
+                nested = True
+                break
+        if not nested:
+            nested_data[key] = value
+    return nested_data
diff --git a/pyproject.toml b/pyproject.toml
index aaee2fb..d3cfff6 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -1,6 +1,6 @@
 [tool.poetry]
 name = "fastcrud"
-version = "0.11.0"
+version = "0.11.1"
 description = "FastCRUD is a Python package for FastAPI, offering robust async CRUD operations and flexible endpoint creation utilities."
 authors = ["Igor Benav <igor.magalhaes.r@gmail.com>"]
 license = "MIT"


Test patch:
diff --git a/tests/sqlalchemy/crud/test_get_joined.py b/tests/sqlalchemy/crud/test_get_joined.py
index 8a98669..cb3590b 100644
--- a/tests/sqlalchemy/crud/test_get_joined.py
+++ b/tests/sqlalchemy/crud/test_get_joined.py
@@ -433,3 +433,35 @@ async def test_get_joined_with_joined_model_filters(
     assert (
         result["tier_name"] == "Premium"
     ), "Expected joined record to meet the filter criteria"
+
+
+@pytest.mark.asyncio
+async def test_get_joined_nest_joins(async_session, test_data, test_data_tier):
+    for tier_item in test_data_tier:
+        async_session.add(TierModel(**tier_item))
+    await async_session.commit()
+
+    for user_item in test_data:
+        async_session.add(ModelTest(**user_item))
+    await async_session.commit()
+
+    crud = FastCRUD(ModelTest)
+
+    result = await crud.get_joined(
+        db=async_session,
+        join_model=TierModel,
+        join_prefix="tier_",
+        schema_to_select=CreateSchemaTest,
+        join_schema_to_select=TierSchemaTest,
+        nest_joins=True,
+    )
+
+    assert result is not None, "No result returned, expected a nested join result."
+    assert "name" in result, "Expected primary model field 'name' in result."
+    assert "tier" in result, "Expected nested 'tier' key in result for joined fields."
+    assert (
+        "name" in result["tier"]
+    ), "Expected 'name' inside nested 'tier' dictionary from TierModel."
+    assert (
+        "tier_name" not in result
+    ), "'tier_name' should not be at the top level in the result."
diff --git a/tests/sqlalchemy/crud/test_get_multi_joined.py b/tests/sqlalchemy/crud/test_get_multi_joined.py
index e2043bc..d8d0317 100644
--- a/tests/sqlalchemy/crud/test_get_multi_joined.py
+++ b/tests/sqlalchemy/crud/test_get_multi_joined.py
@@ -622,7 +622,11 @@ async def test_get_multi_joined_with_joined_model_filters(
 
 
 @pytest.mark.asyncio
-async def test_get_multi_joined_missing_schema_to_select(async_session):
+async def test_get_multi_joined_missing_schema_to_select(async_session, test_data):
+    for test_item in test_data:
+        async_session.add(ModelTest(**test_item))
+    await async_session.commit()
+
     crud = FastCRUD(ModelTest)
     with pytest.raises(ValueError) as exc_info:
         await crud.get_multi_joined(
@@ -666,3 +670,63 @@ async def test_get_multi_joined_validation_error(
     assert "Data validation error for schema CustomCreateSchemaTest:" in str(
         exc_info.value
     )
+
+
+@pytest.mark.asyncio
+async def test_get_multi_joined_with_nesting(async_session, test_data, test_data_tier):
+    for tier_item in test_data_tier:
+        async_session.add(TierModel(**tier_item))
+    await async_session.commit()
+
+    for user_item in test_data:
+        async_session.add(ModelTest(**user_item))
+    await async_session.commit()
+
+    crud = FastCRUD(ModelTest)
+
+    result = await crud.get_multi_joined(
+        db=async_session,
+        joins_config=[
+            JoinConfig(
+                model=TierModel,
+                join_on=ModelTest.tier_id == TierModel.id,
+                join_prefix="tier_",
+                schema_to_select=TierSchemaTest,
+                join_type="left",
+            ),
+            JoinConfig(
+                model=CategoryModel,
+                join_on=ModelTest.category_id == CategoryModel.id,
+                join_prefix="category_",
+                schema_to_select=CategorySchemaTest,
+                join_type="left",
+            ),
+        ],
+        schema_to_select=CreateSchemaTest,
+        nest_joins=True,
+        offset=0,
+        limit=10,
+    )
+
+    assert result is not None, "Expected non-None result for multi joined query"
+    assert "data" in result, "Result should contain 'data' key"
+    assert isinstance(result["data"], list), "'data' should be a list"
+
+    if result["data"]:
+        for item in result["data"]:
+            assert "tier" in item, "Nested tier data should be present under key 'tier'"
+            assert (
+                "category" in item
+            ), "Nested category data should be present under key 'category'"
+            assert isinstance(
+                item["tier"], dict
+            ), "Nested tier data should be a dictionary"
+            assert 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"
diff --git a/tests/sqlmodel/crud/test_get_joined.py b/tests/sqlmodel/crud/test_get_joined.py
index 5957612..6385f2b 100644
--- a/tests/sqlmodel/crud/test_get_joined.py
+++ b/tests/sqlmodel/crud/test_get_joined.py
@@ -433,3 +433,35 @@ async def test_get_joined_with_joined_model_filters(
     assert (
         result["tier_name"] == "Premium"
     ), "Expected joined record to meet the filter criteria"
+
+
+@pytest.mark.asyncio
+async def test_get_joined_nest_joins(async_session, test_data, test_data_tier):
+    for tier_item in test_data_tier:
+        async_session.add(TierModel(**tier_item))
+    await async_session.commit()
+
+    for user_item in test_data:
+        async_session.add(ModelTest(**user_item))
+    await async_session.commit()
+
+    crud = FastCRUD(ModelTest)
+
+    result = await crud.get_joined(
+        db=async_session,
+        join_model=TierModel,
+        join_prefix="tier_",
+        schema_to_select=CreateSchemaTest,
+        join_schema_to_select=TierSchemaTest,
+        nest_joins=True,
+    )
+
+    assert result is not None, "No result returned, expected a nested join result."
+    assert "name" in result, "Expected primary model field 'name' in result."
+    assert "tier" in result, "Expected nested 'tier' key in result for joined fields."
+    assert (
+        "name" in result["tier"]
+    ), "Expected 'name' inside nested 'tier' dictionary from TierModel."
+    assert (
+        "tier_name" not in result
+    ), "'tier_name' should not be at the top level in the result."
diff --git a/tests/sqlmodel/crud/test_get_multi_joined.py b/tests/sqlmodel/crud/test_get_multi_joined.py
index 590e72f..ea21d0b 100644
--- a/tests/sqlmodel/crud/test_get_multi_joined.py
+++ b/tests/sqlmodel/crud/test_get_multi_joined.py
@@ -622,7 +622,11 @@ async def test_get_multi_joined_with_joined_model_filters(
 
 
 @pytest.mark.asyncio
-async def test_get_multi_joined_missing_schema_to_select(async_session):
+async def test_get_multi_joined_missing_schema_to_select(async_session, test_data):
+    for test_item in test_data:
+        async_session.add(ModelTest(**test_item))
+    await async_session.commit()
+
     crud = FastCRUD(ModelTest)
     with pytest.raises(ValueError) as exc_info:
         await crud.get_multi_joined(
@@ -666,3 +670,63 @@ async def test_get_multi_joined_validation_error(
     assert "Data validation error for schema CustomCreateSchemaTest:" in str(
         exc_info.value
     )
+
+
+@pytest.mark.asyncio
+async def test_get_multi_joined_with_nesting(async_session, test_data, test_data_tier):
+    for tier_item in test_data_tier:
+        async_session.add(TierModel(**tier_item))
+    await async_session.commit()
+
+    for user_item in test_data:
+        async_session.add(ModelTest(**user_item))
+    await async_session.commit()
+
+    crud = FastCRUD(ModelTest)
+
+    result = await crud.get_multi_joined(
+        db=async_session,
+        joins_config=[
+            JoinConfig(
+                model=TierModel,
+                join_on=ModelTest.tier_id == TierModel.id,
+                join_prefix="tier_",
+                schema_to_select=TierSchemaTest,
+                join_type="left",
+            ),
+            JoinConfig(
+                model=CategoryModel,
+                join_on=ModelTest.category_id == CategoryModel.id,
+                join_prefix="category_",
+                schema_to_select=CategorySchemaTest,
+                join_type="left",
+            ),
+        ],
+        schema_to_select=CreateSchemaTest,
+        nest_joins=True,
+        offset=0,
+        limit=10,
+    )
+
+    assert result is not None, "Expected non-None result for multi joined query"
+    assert "data" in result, "Result should contain 'data' key"
+    assert isinstance(result["data"], list), "'data' should be a list"
+
+    if result["data"]:
+        for item in result["data"]:
+            assert "tier" in item, "Nested tier data should be present under key 'tier'"
+            assert (
+                "category" in item
+            ), "Nested category data should be present under key 'category'"
+            assert isinstance(
+                item["tier"], dict
+            ), "Nested tier data should be a dictionary"
+            assert 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"