Repository: igorbenav/fastcrud
Pull request #57 (2024-04-23 15:28:31)

feat: filter __in
# Pull Request Template for FastCRUD

## Description
This PR adds support for in_ filtering in crud methods like FastCRUD.select(item_id__in=[1,2]).

## Changes
Extended parse_filters method with addtional elif op == "in", mapping to sqlalchemy/sqlmodel or() filter.

## Tests
Added a test to test_parse_filters for both sqlalchemy and sqlmodel.

## 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
I was trying to acomplish something similar for or_() but it turned out to be too complex for now.


Patch:
diff --git a/docs/advanced/crud.md b/docs/advanced/crud.md
index 230ce2a..3febfab 100644
--- a/docs/advanced/crud.md
+++ b/docs/advanced/crud.md
@@ -100,6 +100,15 @@ items = await item_crud.get_multi(
 )
 ```
 
+Currently supported filter operators are:
+- __gt - greater than
+- __lt - less than
+- __gte - greater than or equal to
+- __lte - less than or equal to
+- __ne - not equal
+- __in - included in (tuple, list or set)
+- __not_in - not included in (tuple, list or set)
+
 #### Counting Records
 
 ```python
diff --git a/fastcrud/crud/fast_crud.py b/fastcrud/crud/fast_crud.py
index d4ee416..4819132 100644
--- a/fastcrud/crud/fast_crud.py
+++ b/fastcrud/crud/fast_crud.py
@@ -213,6 +213,18 @@ class FastCRUD(
                     filters.append(column <= value)
                 elif op == "ne":
                     filters.append(column != value)
+                elif op == "in":
+                    if not isinstance(value, (tuple, list, set)):
+                        raise ValueError(
+                            "in filter must be tuple, list or set"
+                        )
+                    filters.append(column.in_(value))
+                elif op == "not_in":
+                    if not isinstance(value, (tuple, list, set)):
+                        raise ValueError(
+                            "in filter must be tuple, list or set"
+                        )
+                    filters.append(column.not_in(value))
             else:
                 column = getattr(model, key, None)
                 if column is not None:
@@ -371,8 +383,10 @@ class FastCRUD(
             '__gt' (greater than),
             '__lt' (less than),
             '__gte' (greater than or equal to),
-            '__lte' (less than or equal to), and
-            '__ne' (not equal).
+            '__lte' (less than or equal to),
+            '__ne' (not equal),
+            '__in' (included in [tuple, list or set]),
+            '__not_in' (not included in [tuple, list or set]).
 
         Args:
             schema_to_select: Pydantic schema to determine which columns to include in the selection. If not provided, selects all columns of the model.
@@ -435,8 +449,10 @@ class FastCRUD(
             '__gt' (greater than),
             '__lt' (less than),
             '__gte' (greater than or equal to),
-            '__lte' (less than or equal to), and
-            '__ne' (not equal).
+            '__lte' (less than or equal to),
+            '__ne' (not equal),
+            '__in' (included in [tuple, list or set]),
+            '__not_in' (not included in [tuple, list or set]).
 
         Args:
             db: The database session to use for the operation.
@@ -493,8 +509,10 @@ class FastCRUD(
             '__gt' (greater than),
             '__lt' (less than),
             '__gte' (greater than or equal to),
-            '__lte' (less than or equal to), and
-            '__ne' (not equal).
+            '__lte' (less than or equal to),
+            '__ne' (not equal),
+            '__in' (included in [tuple, list or set]),
+            '__not_in' (not included in [tuple, list or set]).
 
         Args:
             db: The database session to use for the operation.
@@ -540,7 +558,9 @@ class FastCRUD(
         Counts records that match specified filters, supporting advanced filtering through comparison operators:
             '__gt' (greater than), '__lt' (less than),
             '__gte' (greater than or equal to),
-            '__lte' (less than or equal to), and '__ne' (not equal).
+            '__lte' (less than or equal to), '__ne' (not equal),
+            '__in' (included in [tuple, list or set]),
+            '__not_in' (not included in [tuple, list or set]).
         Can also count records based on a configuration of joins, useful for complex queries involving relationships.
 
         Args:
@@ -664,8 +684,10 @@ class FastCRUD(
             '__gt' (greater than),
             '__lt' (less than),
             '__gte' (greater than or equal to),
-            '__lte' (less than or equal to), and
-            '__ne' (not equal).
+            '__lte' (less than or equal to),
+            '__ne' (not equal),
+            '__in' (included in [tuple, list or set]),
+            '__not_in' (not included in [tuple, list or set]).
 
         Args:
             db: The database session to use for the operation.
@@ -776,8 +798,10 @@ class FastCRUD(
             '__gt' (greater than),
             '__lt' (less than),
             '__gte' (greater than or equal to),
-            '__lte' (less than or equal to), and
-            '__ne' (not equal).
+            '__lte' (less than or equal to),
+            '__ne' (not equal),
+            '__in' (included in [tuple, list or set]),
+            '__not_in' (not included in [tuple, list or set]).
 
         Args:
             db: The SQLAlchemy async session.
@@ -1021,8 +1045,10 @@ class FastCRUD(
             '__gt' (greater than),
             '__lt' (less than),
             '__gte' (greater than or equal to),
-            '__lte' (less than or equal to), and
-            '__ne' (not equal).
+            '__lte' (less than or equal to),
+            '__ne' (not equal),
+            '__in' (included in [tuple, list or set]),
+            '__not_in' (not included in [tuple, list or set]).
 
         Args:
             db: The SQLAlchemy async session.
@@ -1341,8 +1367,10 @@ class FastCRUD(
             '__gt' (greater than),
             '__lt' (less than),
             '__gte' (greater than or equal to),
-            '__lte' (less than or equal to), and
-            '__ne' (not equal).
+            '__lte' (less than or equal to),
+            '__ne' (not equal),
+            '__in' (included in [tuple, list or set]),
+            '__not_in' (not included in [tuple, list or set]).
 
         Args:
             db: The SQLAlchemy async session.
@@ -1428,8 +1456,10 @@ class FastCRUD(
             '__gt' (greater than),
             '__lt' (less than),
             '__gte' (greater than or equal to),
-            '__lte' (less than or equal to), and
-            '__ne' (not equal).
+            '__lte' (less than or equal to),
+            '__ne' (not equal),
+            '__in' (included in [tuple, list or set]),
+            '__not_in' (not included in [tuple, list or set]).
 
         Args:
             db: The database session to use for the operation.
@@ -1500,8 +1530,10 @@ class FastCRUD(
             '__gt' (greater than),
             '__lt' (less than),
             '__gte' (greater than or equal to),
-            '__lte' (less than or equal to), and
-            '__ne' (not equal).
+            '__lte' (less than or equal to),
+            '__ne' (not equal),
+            '__in' (included in [tuple, list or set]),
+            '__not_in' (not included in [tuple, list or set]).
 
         Args:
             db: The database session to use for the operation.
@@ -1556,8 +1588,10 @@ class FastCRUD(
             '__gt' (greater than),
             '__lt' (less than),
             '__gte' (greater than or equal to),
-            '__lte' (less than or equal to), and
-            '__ne' (not equal).
+            '__lte' (less than or equal to),
+            '__ne' (not equal),
+            '__in' (included in [tuple, list or set]),
+            '__not_in' (not included in [tuple, list or set]).
 
         Args:
             db: The database session to use for the operation.


Test patch:
diff --git a/tests/sqlalchemy/core/test_parse_filters.py b/tests/sqlalchemy/core/test_parse_filters.py
index c8398cc..ec75b2b 100644
--- a/tests/sqlalchemy/core/test_parse_filters.py
+++ b/tests/sqlalchemy/core/test_parse_filters.py
@@ -21,6 +21,42 @@ async def test_parse_filters_multiple_conditions(test_model):
     assert str(filters[1]) == "test.is_deleted = true"
 
 
+@pytest.mark.asyncio
+async def test_parse_filters_contained_in(test_model):
+    fast_crud = FastCRUD(test_model)
+    filters = fast_crud._parse_filters(category_id__in=[1, 2])
+    assert len(filters) == 1
+    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(
+    'type', ('in', 'not_in')
+)
+async def test_parse_filters_contained_in_raises_exception(
+    test_model, type: str
+):
+    fast_crud = FastCRUD(test_model)
+    with pytest.raises(ValueError) as exc:
+        if type == 'in':
+            fast_crud._parse_filters(category_id__in=1)
+        elif type == 'not_in':
+            fast_crud._parse_filters(category_id__not_in=1)
+    assert str(exc.value) == "in filter must be tuple, list or set"
+
+
 @pytest.mark.asyncio
 async def test_parse_filters_invalid_column(test_model):
     fast_crud = FastCRUD(test_model)
diff --git a/tests/sqlmodel/core/test_parse_filters.py b/tests/sqlmodel/core/test_parse_filters.py
index c8398cc..a8ed3c4 100644
--- a/tests/sqlmodel/core/test_parse_filters.py
+++ b/tests/sqlmodel/core/test_parse_filters.py
@@ -21,6 +21,24 @@ async def test_parse_filters_multiple_conditions(test_model):
     assert str(filters[1]) == "test.is_deleted = true"
 
 
+@pytest.mark.asyncio
+async def test_parse_filters_contained_in(test_model):
+    fast_crud = FastCRUD(test_model)
+    filters = fast_crud._parse_filters(category_id__in=[1, 2])
+    assert len(filters) == 1
+    assert str(
+        filters[0]
+    ) == "test.category_id IN (__[POSTCOMPILE_category_id_1])"
+
+
+@pytest.mark.asyncio
+async def test_parse_filters_contained_in_exception(test_model):
+    fast_crud = FastCRUD(test_model)
+    with pytest.raises(ValueError) as exc:
+        fast_crud._parse_filters(category_id__in=1)
+    assert str(exc.value) == "in filter must be tuple, list or set"
+
+
 @pytest.mark.asyncio
 async def test_parse_filters_invalid_column(test_model):
     fast_crud = FastCRUD(test_model)