Repository: igorbenav/fastcrud
Pull request #217 (2025-04-03 05:55:52)

implementing sort for nested fields
# Pull Request Template for FastCRUD

## Description
Extended crud helper to suport sorting by some fields and with order pick. Added test cases and updated existing tests.

## Changes
Solving: https://github.com/igorbenav/fastcrud/issues/171
sort_columns and sort_orders added to joinconfig
added _sort_nested_list to crud/helper.py
updated _nest_join_data to use new logic
updated conftests
updated tests/sqlalchemy/core/test_nest_multi_join_data.py

## Tests
added tests/sqlalchemy/crud/test_sort_nested_relationships.py

## Checklist
- [x] I have read the [CONTRIBUTING](CONTRIBUTING.md) document.
- [x] My code follows the code style of this project.
- [x] I have added necessary documentation (if appropriate).
- [x] I have added tests that cover my changes (if applicable).
- [x] All new and existing tests passed.

## Additional Notes
Include any additional information that you think is important for reviewers to know.



Patch:
diff --git a/docs/advanced/joins.md b/docs/advanced/joins.md
index 40e81ef..f8b0fb5 100644
--- a/docs/advanced/joins.md
+++ b/docs/advanced/joins.md
@@ -14,6 +14,8 @@ FastCRUD simplifies CRUD operations while offering capabilities for handling com
 - **`alias`**: An optional SQLAlchemy `AliasedClass` for complex scenarios like self-referential joins or multiple joins on the same model.
 - **`filters`**: An optional dictionary to apply filters directly to the joined model.
 - **`relationship_type`**: Specifies the relationship type, such as `"one-to-one"` or `"one-to-many"`. Default is `"one-to-one"`.
+- **`sort_columns`**: An optional column name or list of column names to sort the nested items by. Only applies to `"one-to-many"` relationships.
+- **`sort_orders`**: An optional sort order (`"asc"` or `"desc"`) or list of sort orders corresponding to the columns in `sort_columns`. If not provided, defaults to `"asc"` for each column.
 
 !!! TIP
 
@@ -86,12 +88,12 @@ task_count = await task_crud.count(
     db=db,
     joins_config=[
         JoinConfig(
-            model=User, 
+            model=User,
             join_on=Task.assigned_user_id == User.id,
         ),
         JoinConfig(
-            model=Department, 
-            join_on=User.department_id == Department.id, 
+            model=Department,
+            join_on=User.department_id == Department.id,
             filters={"name": "Engineering"},
         ),
     ],
@@ -213,19 +215,19 @@ users = await user_crud.get_multi_joined(
     schema_to_select=ReadUserSchema,
     joins_config=[
         JoinConfig(
-            model=Department, 
-            join_on=User.department_id == Department.id, 
+            model=Department,
+            join_on=User.department_id == Department.id,
             join_prefix="dept_",
         ),
         JoinConfig(
-            model=Tier, 
-            join_on=User.tier_id == Tier.id, 
+            model=Tier,
+            join_on=User.tier_id == Tier.id,
             join_prefix="tier_",
         ),
         JoinConfig(
-            model=User, 
-            alias=manager_alias, 
-            join_on=User.manager_id == manager_alias.id, 
+            model=User,
+            alias=manager_alias,
+            join_on=User.manager_id == manager_alias.id,
             join_prefix="manager_",
         ),
     ],
@@ -355,13 +357,59 @@ The result will be:
 }
 ```
 
+##### Sorting Nested Items in One-to-Many Relationships
+
+FastCRUD allows you to sort nested items in one-to-many relationships using the `sort_columns` and `sort_orders` parameters in the `JoinConfig`. This is particularly useful when you want to display nested items in a specific order.
+
+```python
+from fastcrud import FastCRUD, JoinConfig
+
+author_crud = FastCRUD(Author)
+
+# Define join configuration with sorting
+joins_config = [
+    JoinConfig(
+        model=Article,
+        join_on=Author.id == Article.author_id,
+        join_prefix="articles_",
+        relationship_type="one-to-many",
+        sort_columns="title",  # Sort articles by title
+        sort_orders="asc"      # In ascending order
+    )
+]
+
+# Fetch authors with their articles sorted by title
+result = await author_crud.get_multi_joined(
+    db=db,
+    joins_config=joins_config,
+    nest_joins=True
+)
+```
+
+You can also sort by multiple columns with different sort orders:
+
+```python
+joins_config = [
+    JoinConfig(
+        model=Article,
+        join_on=Author.id == Article.author_id,
+        join_prefix="articles_",
+        relationship_type="one-to-many",
+        sort_columns=["published_date", "title"],  # Sort by date first, then title
+        sort_orders=["desc", "asc"]               # Date descending, title ascending
+    )
+]
+```
+
+This will result in nested articles being sorted first by published_date in descending order, and then by title in ascending order within each date group.
+
 #### Many-to-Many Relationships with `get_multi_joined`
 
 FastCRUD simplifies dealing with many-to-many relationships by allowing easy fetch operations with joined models. Here, we demonstrate using `get_multi_joined` to handle a many-to-many relationship between `Project` and `Participant` models, linked through an association table.
 
 **Note on Handling Many-to-Many Relationships:**
 
-When using `get_multi_joined` for many-to-many relationships, it's essential to maintain a specific order in your `joins_config`: 
+When using `get_multi_joined` for many-to-many relationships, it's essential to maintain a specific order in your `joins_config`:
 
 1. **First**, specify the main table you're querying from.
 2. **Next**, include the association table that links your main table to the other table involved in the many-to-many relationship.
@@ -419,7 +467,7 @@ joins_config = [
 
 # Fetch projects with their participants
 projects_with_participants = await project_crud.get_multi_joined(
-    db_session, 
+    db_session,
     joins_config=joins_config,
 )
 ```
diff --git a/fastcrud/crud/helper.py b/fastcrud/crud/helper.py
index 3aa938a..b6efe24 100644
--- a/fastcrud/crud/helper.py
+++ b/fastcrud/crud/helper.py
@@ -20,6 +20,8 @@ class JoinConfig(BaseModel):
     alias: Optional[AliasedClass] = None
     filters: Optional[dict] = None
     relationship_type: Optional[str] = "one-to-one"
+    sort_columns: Optional[Union[str, list[str]]] = None
+    sort_orders: Optional[Union[str, list[str]]] = None
 
     model_config = ConfigDict(arbitrary_types_allowed=True)
 
@@ -289,6 +291,57 @@ def _handle_one_to_many(nested_data, nested_key, nested_field, value):
     return nested_data
 
 
+def _sort_nested_list(nested_list: list[dict], sort_columns: Union[str, list[str]], sort_orders: Optional[Union[str, list[str]]] = None) -> list[dict]:
+    """
+    Sorts a list of dictionaries based on specified sort columns and orders.
+
+    Args:
+        nested_list: The list of dictionaries to sort.
+        sort_columns: A single column name or a list of column names on which to apply sorting.
+        sort_orders: A single sort order ("asc" or "desc") or a list of sort orders corresponding
+            to the columns in `sort_columns`. If not provided, defaults to "asc" for each column.
+
+    Returns:
+        The sorted list of dictionaries.
+
+    Examples:
+        Sorting a list of dictionaries by a single column in ascending order:
+        >>> _sort_nested_list([{"id": 2, "name": "B"}, {"id": 1, "name": "A"}], "name")
+        [{"id": 1, "name": "A"}, {"id": 2, "name": "B"}]
+
+        Sorting by multiple columns with different orders:
+        >>> _sort_nested_list([{"id": 1, "name": "A"}, {"id": 2, "name": "A"}], ["name", "id"], ["asc", "desc"])
+        [{"id": 2, "name": "A"}, {"id": 1, "name": "A"}]
+    """
+    if not nested_list or not sort_columns:
+        return nested_list
+
+    if not isinstance(sort_columns, list):
+        sort_columns = [sort_columns]
+
+    if sort_orders:
+        if not isinstance(sort_orders, list):
+            sort_orders = [sort_orders] * len(sort_columns)
+        if len(sort_columns) != len(sort_orders):
+            raise ValueError("The length of sort_columns and sort_orders must match.")
+
+        for order in sort_orders:
+            if order not in ["asc", "desc"]:
+                raise ValueError(f"Invalid sort order: {order}. Only 'asc' or 'desc' are allowed.")
+    else:
+        sort_orders = ["asc"] * len(sort_columns)
+
+    # Create a list of (column, order) tuples for sorting
+    sort_specs = [(col, 1 if order == "asc" else -1) for col, order in zip(sort_columns, sort_orders)]
+
+    # Sort the list using the sort_specs
+    sorted_list = nested_list.copy()
+    for col, direction in reversed(sort_specs):
+        sorted_list.sort(key=lambda x: (x.get(col) is None, x.get(col)), reverse=direction == -1)
+
+    return sorted_list
+
+
 def _nest_join_data(
     data: dict,
     join_definitions: list[JoinConfig],
@@ -432,6 +485,11 @@ def _nest_join_data(
                     item[join_primary_key] is None for item in nested_data[nested_key]
                 ):
                     nested_data[nested_key] = []
+                # Apply sorting to nested list if sort_columns is specified
+                elif join.sort_columns and nested_data[nested_key]:
+                    nested_data[nested_key] = _sort_nested_list(
+                        nested_data[nested_key], join.sort_columns, join.sort_orders
+                    )
 
         if nested_key in nested_data and isinstance(nested_data[nested_key], dict):
             if (
@@ -576,6 +634,14 @@ def _nest_multi_join_data(
                                         join_prefix
                                     ].append(item)
                                     existing_items.add(item[join_primary_key])
+
+                            # Apply sorting to nested list if sort_columns is specified
+                            if join_config.sort_columns and pre_nested_data[primary_key_value][join_prefix]:
+                                pre_nested_data[primary_key_value][join_prefix] = _sort_nested_list(
+                                    pre_nested_data[primary_key_value][join_prefix],
+                                    join_config.sort_columns,
+                                    join_config.sort_orders
+                                )
             else:  # pragma: no cover
                 if join_prefix in row_dict:
                     value = row_dict[join_prefix]


Test patch:
diff --git a/tests/sqlalchemy/conftest.py b/tests/sqlalchemy/conftest.py
index 68f87a9..5ed7122 100644
--- a/tests/sqlalchemy/conftest.py
+++ b/tests/sqlalchemy/conftest.py
@@ -138,11 +138,22 @@ class Card(Base):
     title = Column(String(32))
 
 
+class Author(Base):
+    __tablename__ = "authors"
+    id = Column(Integer, primary_key=True)
+    name = Column(String(32))
+    articles = relationship("Article", back_populates="author")
+
+
 class Article(Base):
     __tablename__ = "articles"
     id = Column(Integer, primary_key=True)
     title = Column(String(32))
-    card_id = Column(Integer, ForeignKey("cards.id"))
+    content = Column(String(100))
+    published_date = Column(String(32))
+    author_id = Column(Integer, ForeignKey("authors.id"))
+    author = relationship("Author", back_populates="articles")
+    card_id = Column(Integer, ForeignKey("cards.id"), nullable=True)
     card = relationship("Card", back_populates="articles")
 
 
@@ -254,7 +265,16 @@ class BookingSchema(BaseModel):
 class ArticleSchema(BaseModel):
     id: int
     title: str
-    card_id: int
+    content: Optional[str] = None
+    published_date: Optional[str] = None
+    author_id: Optional[int] = None
+    card_id: Optional[int] = None
+
+
+class AuthorSchema(BaseModel):
+    id: int
+    name: str
+    articles: Optional[list[ArticleSchema]] = []
 
 
 class CardSchema(BaseModel):
diff --git a/tests/sqlalchemy/core/test_nest_multi_join_data.py b/tests/sqlalchemy/core/test_nest_multi_join_data.py
index 47d1ae4..ae11bb3 100644
--- a/tests/sqlalchemy/core/test_nest_multi_join_data.py
+++ b/tests/sqlalchemy/core/test_nest_multi_join_data.py
@@ -20,9 +20,9 @@ async def test_nest_multi_join_data_new_row_none(async_session):
     await async_session.flush()
 
     articles = [
-        Article(title="Article 1", card_id=cards[0].id),
+        Article(title="Article 1", content="Content 1", author_id=1, card_id=cards[0].id),
         Article(
-            title="Article 2", card_id=None
+            title="Article 2", content="Content 2", author_id=2, card_id=None
         ),  # This should trigger new_row[key] = []
     ]
     async_session.add_all(articles)
@@ -76,10 +76,10 @@ async def test_nest_multi_join_data_existing_row_none(async_session):
     await async_session.flush()
 
     articles = [
-        Article(title="Article 1", card_id=cards[0].id),
-        Article(title="Article 2", card_id=cards[0].id),
+        Article(title="Article 1", content="Content 1", author_id=1, card_id=cards[0].id),
+        Article(title="Article 2", content="Content 2", author_id=2, card_id=cards[0].id),
         Article(
-            title="Article 3", card_id=None
+            title="Article 3", content="Content 3", author_id=3, card_id=None
         ),  # This will trigger existing_row[key] = []
     ]
     async_session.add_all(articles)
@@ -128,7 +128,7 @@ async def test_nest_multi_join_data_nested_schema(async_session):
     await async_session.flush()
 
     articles = [
-        Article(title="Article 1", card_id=cards[0].id),
+        Article(title="Article 1", content="Content 1", author_id=1, card_id=cards[0].id),
     ]
     async_session.add_all(articles)
     await async_session.commit()
@@ -178,7 +178,7 @@ async def test_nest_multi_join_data_prefix_in_item(async_session):
     await async_session.flush()
 
     articles = [
-        Article(title="Article 1", card_id=cards[0].id),
+        Article(title="Article 1", content="Content 1", author_id=1, card_id=cards[0].id),
     ]
     async_session.add_all(articles)
     await async_session.commit()
@@ -228,8 +228,8 @@ async def test_nest_multi_join_data_isinstance_list(async_session):
     await async_session.flush()
 
     articles = [
-        Article(title="Article 1", card_id=cards[0].id),
-        Article(title="Article 2", card_id=cards[0].id),
+        Article(title="Article 1", content="Content 1", author_id=1, card_id=cards[0].id),
+        Article(title="Article 2", content="Content 2", author_id=2, card_id=cards[0].id),
     ]
     async_session.add_all(articles)
     await async_session.commit()
@@ -276,8 +276,8 @@ async def test_nest_multi_join_data_convert_list_to_schema(async_session):
     await async_session.flush()
 
     articles = [
-        Article(title="Article 1", card_id=cards[0].id),
-        Article(title="Article 2", card_id=cards[0].id),
+        Article(title="Article 1", content="Content 1", author_id=1, card_id=cards[0].id),
+        Article(title="Article 2", content="Content 2", author_id=2, card_id=cards[0].id),
     ]
     async_session.add_all(articles)
     await async_session.commit()
@@ -324,7 +324,7 @@ async def test_nest_multi_join_data_convert_dict_to_schema(async_session):
     await async_session.flush()
 
     articles = [
-        Article(title="Article 1", card_id=cards[0].id),
+        Article(title="Article 1", content="Content 1", author_id=1, card_id=cards[0].id),
     ]
     async_session.add_all(articles)
     await async_session.commit()
diff --git a/tests/sqlalchemy/crud/test_sort_nested_relationships.py b/tests/sqlalchemy/crud/test_sort_nested_relationships.py
new file mode 100644
index 0000000..994d669
--- /dev/null
+++ b/tests/sqlalchemy/crud/test_sort_nested_relationships.py
@@ -0,0 +1,114 @@
+import pytest
+
+from fastcrud import FastCRUD, JoinConfig
+from ..conftest import (
+    Article,
+    Author,
+)
+
+
+@pytest.mark.asyncio
+async def test_sort_nested_one_to_many_relationships(async_session):
+    # Create test data
+    author1 = Author(id=1, name="Author 1")
+    author2 = Author(id=2, name="Author 2")
+
+    # Create articles with different titles and dates for sorting
+    article1 = Article(id=1, title="C Article", content="Content 1", author_id=1, published_date="2023-01-01")
+    article2 = Article(id=2, title="A Article", content="Content 2", author_id=1, published_date="2023-03-01")
+    article3 = Article(id=3, title="B Article", content="Content 3", author_id=1, published_date="2023-02-01")
+    article4 = Article(id=4, title="D Article", content="Content 4", author_id=2, published_date="2023-01-15")
+
+    async_session.add_all([author1, author2, article1, article2, article3, article4])
+    await async_session.commit()
+
+    # Test sorting by title in ascending order
+    author_crud = FastCRUD(Author)
+    joins_config = [
+        JoinConfig(
+            model=Article,
+            join_on=Author.id == Article.author_id,
+            join_prefix="articles_",
+            relationship_type="one-to-many",
+            sort_columns="title",
+            sort_orders="asc"
+        )
+    ]
+
+    result = await author_crud.get_multi_joined(
+        db=async_session,
+        joins_config=joins_config,
+        nest_joins=True
+    )
+
+    # Verify that articles are sorted by title in ascending order
+    author1_data = next(item for item in result["data"] if item["id"] == 1)
+    assert [article["title"] for article in author1_data["articles"]] == ["A Article", "B Article", "C Article"]
+
+    # Test sorting by title in descending order
+    joins_config = [
+        JoinConfig(
+            model=Article,
+            join_on=Author.id == Article.author_id,
+            join_prefix="articles_",
+            relationship_type="one-to-many",
+            sort_columns="title",
+            sort_orders="desc"
+        )
+    ]
+
+    result = await author_crud.get_multi_joined(
+        db=async_session,
+        joins_config=joins_config,
+        nest_joins=True
+    )
+
+    # Verify that articles are sorted by title in descending order
+    author1_data = next(item for item in result["data"] if item["id"] == 1)
+    assert [article["title"] for article in author1_data["articles"]] == ["C Article", "B Article", "A Article"]
+
+    # Test sorting by published_date
+    joins_config = [
+        JoinConfig(
+            model=Article,
+            join_on=Author.id == Article.author_id,
+            join_prefix="articles_",
+            relationship_type="one-to-many",
+            sort_columns="published_date",
+            sort_orders="asc"
+        )
+    ]
+
+    result = await author_crud.get_multi_joined(
+        db=async_session,
+        joins_config=joins_config,
+        nest_joins=True
+    )
+
+    # Verify that articles are sorted by published_date in ascending order
+    author1_data = next(item for item in result["data"] if item["id"] == 1)
+    assert [article["published_date"] for article in author1_data["articles"]] == ["2023-01-01", "2023-02-01",
+                                                                                   "2023-03-01"]
+
+    # Test sorting by multiple columns
+    joins_config = [
+        JoinConfig(
+            model=Article,
+            join_on=Author.id == Article.author_id,
+            join_prefix="articles_",
+            relationship_type="one-to-many",
+            sort_columns=["published_date", "title"],
+            sort_orders=["desc", "asc"]
+        )
+    ]
+
+    result = await author_crud.get_multi_joined(
+        db=async_session,
+        joins_config=joins_config,
+        nest_joins=True
+    )
+
+    # Verify that articles are sorted by published_date in descending order and then by title in ascending order
+    author1_data = next(item for item in result["data"] if item["id"] == 1)
+    assert [article["published_date"] for article in author1_data["articles"]] == ["2023-03-01", "2023-02-01",
+                                                                                   "2023-01-01"]