Repository: igorbenav/fastcrud
Pull request #85 (2024-05-13 23:06:00)

Missing sqlalchemy operators
This implements #79 by introducing `FastCRUD._get_sqlalchemy_filter()` method and a dictonary of filter mappings in `FastCRUD._SUPPORTED_FILTERS`. Adding new sqlalchemy opertors is as simple as extending the dictonary.

It also solves the `or_()` operator issue:
```
                    ItemCRUD.get_multi(
                        name__or={'startswith': 'foo', 'endswith': 'bar'}
                    )
```

There is some mypy issue which I struggle to solve, but it looks like it's not required. The tests pass with good coverage.


Patch:
diff --git a/docs/advanced/crud.md b/docs/advanced/crud.md
index 6459b2d..c2b6ca6 100644
--- a/docs/advanced/crud.md
+++ b/docs/advanced/crud.md
@@ -85,29 +85,62 @@ await item_crud.delete(
 
 FastCRUD supports advanced filtering options, allowing you to query records using operators such as greater than (`__gt`), less than (`__lt`), and their inclusive counterparts (`__gte`, `__lte`). These filters can be used in any method that retrieves or operates on records, including `get`, `get_multi`, `exists`, `count`, `update`, and `delete`.
 
-### Using Advanced Filters
+### Single parameter filters
 
-The following examples demonstrate how to use advanced filters for querying and manipulating data:
-
-#### Fetching Records with Advanced Filters
+Most filter operators require a single string or integer value.
 
 ```python
-# Fetch items priced between $5 and $20
+# Fetch items priced between above $5
 items = await item_crud.get_multi(
     db=db,
     price__gte=5,
-    price__lte=20
 )
 ```
 
-Currently supported filter operators are:
+Currently supported single parameter filters 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)
+- __is - used to test True, False and None identity
+- __is_not - negation of "is"
+- __like - SQL "like" search for specific text pattern
+- __notlike - negation of "like"
+- __ilike - case insensitive "like"
+- __notilike - case insensitive "notlike"
+- __startswith - text starts with given string
+- __endswith - text ends with given string
+- __contains - text contains given string
+- __match - database-specific match expression
+
+### Complex parameter filters
+
+Some operators require multiple values. They must be passed as a python tuple, list or set.
+
+```python
+# Fetch items priced between $5 and $20
+items = await item_crud.get_multi(
+    db=db,
+    price__between=(5, 20),
+)
+```
+- __between - between 2 numeric values
+- __in - included in
+- __not_in - not included in
+
+### OR parameter filters
+
+More complex OR filters are supported. They must be passed as dictionary, where each key is a library-supported operator to be used in OR expression and values is what get's passed as the parameter.
+
+```python
+# Fetch items priced under $5 or above $20
+items = await item_crud.get_multi(
+    db=db,
+    price__or={'lt': 5, 'gt': 20},
+)
+```
+
 
 #### Counting Records
 
diff --git a/fastcrud/crud/fast_crud.py b/fastcrud/crud/fast_crud.py
index ab58dc6..f0b7d7f 100644
--- a/fastcrud/crud/fast_crud.py
+++ b/fastcrud/crud/fast_crud.py
@@ -1,15 +1,15 @@
-from typing import Any, Dict, Generic, TypeVar, Union, Optional
+from typing import Any, Dict, Generic, TypeVar, Union, Optional, Callable
 from datetime import datetime, timezone
 
 from pydantic import BaseModel, ValidationError
-from sqlalchemy import select, update, delete, func, inspect, asc, desc
+from sqlalchemy import select, update, delete, func, inspect, asc, desc, or_
 from sqlalchemy.exc import ArgumentError, MultipleResultsFound, NoResultFound
 from sqlalchemy.sql import Join
 from sqlalchemy.ext.asyncio import AsyncSession
 from sqlalchemy.engine.row import Row
 from sqlalchemy.orm import DeclarativeBase
 from sqlalchemy.orm.util import AliasedClass
-from sqlalchemy.sql.elements import BinaryExpression
+from sqlalchemy.sql.elements import BinaryExpression, ColumnElement
 from sqlalchemy.sql.selectable import Select
 
 from .helper import (
@@ -178,6 +178,26 @@ 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__,
+        "gte": lambda column: column.__ge__,
+        "lte": lambda column: column.__le__,
+        "ne": lambda column: column.__ne__,
+        "is": lambda column: column.is_,
+        "is_not": lambda column: column.is_not,
+        "like": lambda column: column.like,
+        "notlike": lambda column: column.notlike,
+        "ilike": lambda column: column.ilike,
+        "notilike": lambda column: column.notilike,
+        "startswith": lambda column: column.startswith,
+        "endswith": lambda column: column.endswith,
+        "contains": lambda column: column.contains,
+        "match": lambda column: column.match,
+        "between": lambda column: column.between,
+        "in": lambda column: column.in_,
+        "not_in": lambda column: column.not_in,
+    }
 
     def __init__(
         self,
@@ -193,36 +213,42 @@ class FastCRUD(
         self.updated_at_column = updated_at_column
         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'}:
+            if not isinstance(value, (tuple, list, set)):
+                raise ValueError(
+                    f"<{operator}> filter must be tuple, list or set"
+                )
+        return self._SUPPORTED_FILTERS.get(operator)
+
     def _parse_filters(
         self, model: Optional[Union[type[ModelType], AliasedClass]] = None, **kwargs
-    ) -> list[BinaryExpression]:
+    ) -> list[ColumnElement]:
         model = model or self.model
         filters = []
+
         for key, value in kwargs.items():
             if "__" in key:
                 field_name, op = key.rsplit("__", 1)
                 column = getattr(model, field_name, None)
                 if column is None:
                     raise ValueError(f"Invalid filter column: {field_name}")
-
-                if op == "gt":
-                    filters.append(column > value)
-                elif op == "lt":
-                    filters.append(column < value)
-                elif op == "gte":
-                    filters.append(column >= value)
-                elif op == "lte":
-                    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))
+                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
+                    ]
+                    filters.append(or_(*or_filters))
+                else:
+                    sqlalchemy_filter = self._get_sqlalchemy_filter(op, value)
+                    if sqlalchemy_filter:
+                        filters.append(
+                                sqlalchemy_filter(column)(value)
+                        )
             else:
                 column = getattr(model, key, None)
                 if column is not None:
@@ -377,14 +403,8 @@ class FastCRUD(
         """
         Constructs a SQL Alchemy `Select` statement with optional column selection, filtering, and sorting.
         This method allows for advanced filtering through comparison operators, enabling queries to be refined beyond simple equality checks.
-        Supported operators include:
-            '__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]).
+        For filtering details see:
+        https://igorbenav.github.io/fastcrud/advanced/crud/#advanced-filters
 
         Args:
             schema_to_select: Pydantic schema to determine which columns to include in the selection. If not provided, selects all columns of the model.
@@ -443,14 +463,8 @@ class FastCRUD(
         """
         Fetches a single record based on specified filters.
         This method allows for advanced filtering through comparison operators, enabling queries to be refined beyond simple equality checks.
-        Supported operators include:
-            '__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]).
+        For filtering details see:
+        https://igorbenav.github.io/fastcrud/advanced/crud/#advanced-filters
 
         Args:
             db: The database session to use for the operation.
@@ -550,14 +564,8 @@ class FastCRUD(
     async def exists(self, db: AsyncSession, **kwargs: Any) -> bool:
         """
         Checks if any records exist that match the given filter conditions.
-        This method supports advanced filtering with comparison operators:
-            '__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]).
+        For filtering details see:
+        https://igorbenav.github.io/fastcrud/advanced/crud/#advanced-filters
 
         Args:
             db: The database session to use for the operation.
@@ -600,12 +608,9 @@ class FastCRUD(
         **kwargs: Any,
     ) -> int:
         """
-        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), '__ne' (not equal),
-            '__in' (included in [tuple, list or set]),
-            '__not_in' (not included in [tuple, list or set]).
+        Counts records that match specified filters. For filtering details see:
+        https://igorbenav.github.io/fastcrud/advanced/crud/#advanced-filters
+
         Can also count records based on a configuration of joins, useful for complex queries involving relationships.
 
         Args:
@@ -725,14 +730,9 @@ class FastCRUD(
         **kwargs: Any,
     ) -> dict[str, Any]:
         """
-        Fetches multiple records based on filters, supporting sorting, pagination, and advanced filtering with comparison operators:
-            '__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]).
+        Fetches multiple records based on filters, supporting sorting, pagination.
+        For filtering details see:
+        https://igorbenav.github.io/fastcrud/advanced/crud/#advanced-filters
 
         Args:
             db: The database session to use for the operation.
@@ -839,14 +839,8 @@ class FastCRUD(
         """
         Fetches a single record with one or multiple joins on other models. If 'join_on' is not provided, the method attempts
         to automatically detect the join condition using foreign key relationships. For multiple joins, use 'joins_config' to
-        specify each join configuration. Advanced filters supported:
-            '__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]).
+        specify each join configuration. For filtering details see:
+        https://igorbenav.github.io/fastcrud/advanced/crud/#advanced-filters
 
         Args:
             db: The SQLAlchemy async session.
@@ -1085,15 +1079,9 @@ class FastCRUD(
         **kwargs: Any,
     ) -> dict[str, Any]:
         """
-        Fetch multiple records with a join on another model, allowing for pagination, optional sorting, and model conversion,
-        supporting advanced filtering with comparison operators:
-            '__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]).
+        Fetch multiple records with a join on another model, allowing for pagination, optional sorting, and model conversion.
+        For filtering details see:
+        https://igorbenav.github.io/fastcrud/advanced/crud/#advanced-filters
 
         Args:
             db: The SQLAlchemy async session.
@@ -1408,14 +1396,8 @@ class FastCRUD(
     ) -> dict[str, Any]:
         """
         Implements cursor-based pagination for fetching records. This method is designed for efficient data retrieval in large datasets and is ideal for features like infinite scrolling.
-        It supports advanced filtering with comparison operators:
-            '__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]).
+        For filtering details see:
+        https://igorbenav.github.io/fastcrud/advanced/crud/#advanced-filters
 
         Args:
             db: The SQLAlchemy async session.
@@ -1497,14 +1479,8 @@ class FastCRUD(
     ) -> None:
         """
         Updates an existing record or multiple records in the database based on specified filters. This method allows for precise targeting of records to update.
-        It supports advanced filtering through comparison operators:
-            '__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]).
+        For filtering details see:
+        https://igorbenav.github.io/fastcrud/advanced/crud/#advanced-filters
 
         Args:
             db: The database session to use for the operation.
@@ -1571,14 +1547,9 @@ class FastCRUD(
         **kwargs: Any,
     ) -> None:
         """
-        Deletes a record or multiple records from the database based on specified filters, with support for advanced filtering through comparison operators:
-            '__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]).
+        Deletes a record or multiple records from the database based on specified filters.
+        For filtering details see:
+        https://igorbenav.github.io/fastcrud/advanced/crud/#advanced-filters
 
         Args:
             db: The database session to use for the operation.
@@ -1629,14 +1600,8 @@ class FastCRUD(
     ) -> None:
         """
         Soft deletes a record or optionally multiple records if it has an "is_deleted" attribute, otherwise performs a hard delete, based on specified filters.
-        Supports advanced filtering through comparison operators:
-            '__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]).
+        For filtering details see:
+        https://igorbenav.github.io/fastcrud/advanced/crud/#advanced-filters
 
         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 2c36f35..58aa813 100644
--- a/tests/sqlalchemy/core/test_parse_filters.py
+++ b/tests/sqlalchemy/core/test_parse_filters.py
@@ -21,6 +21,17 @@ 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_or_condition(test_model):
+    fast_crud = FastCRUD(test_model)
+
+    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"
+
+
 @pytest.mark.asyncio
 async def test_parse_filters_contained_in(test_model):
     fast_crud = FastCRUD(test_model)
@@ -40,15 +51,17 @@ async def test_parse_filters_not_contained_in(test_model):
 
 
 @pytest.mark.asyncio
-@pytest.mark.parametrize("type", ("in", "not_in"))
-async def test_parse_filters_contained_in_raises_exception(test_model, type: str):
+@pytest.mark.parametrize("operator", ("in", "not_in", "between"))
+async def test_parse_filters_raises_exception(test_model, operator: str):
     fast_crud = FastCRUD(test_model)
     with pytest.raises(ValueError) as exc:
-        if type == "in":
+        if operator == "in":
             fast_crud._parse_filters(category_id__in=1)
-        elif type == "not_in":
+        elif operator == "not_in":
             fast_crud._parse_filters(category_id__not_in=1)
-    assert str(exc.value) == "in filter must be tuple, list or set"
+        elif operator == "between":
+            fast_crud._parse_filters(category_id__between=1)
+    assert str(exc.value) == f"<{operator}> filter must be tuple, list or set"
 
 
 @pytest.mark.asyncio
diff --git a/tests/sqlmodel/core/test_parse_filters.py b/tests/sqlmodel/core/test_parse_filters.py
index 09b089f..e5cecb6 100644
--- a/tests/sqlmodel/core/test_parse_filters.py
+++ b/tests/sqlmodel/core/test_parse_filters.py
@@ -21,6 +21,17 @@ 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_or_condition(test_model):
+    fast_crud = FastCRUD(test_model)
+
+    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"
+
+
 @pytest.mark.asyncio
 async def test_parse_filters_contained_in(test_model):
     fast_crud = FastCRUD(test_model)
@@ -30,11 +41,17 @@ async def test_parse_filters_contained_in(test_model):
 
 
 @pytest.mark.asyncio
-async def test_parse_filters_contained_in_exception(test_model):
+@pytest.mark.parametrize("operator", ("in", "not_in", "between"))
+async def test_parse_filters_raises_exception(test_model, operator: str):
     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"
+        if operator == "in":
+            fast_crud._parse_filters(category_id__in=1)
+        elif operator == "not_in":
+            fast_crud._parse_filters(category_id__not_in=1)
+        elif operator == "between":
+            fast_crud._parse_filters(category_id__between=1)
+    assert str(exc.value) == f"<{operator}> filter must be tuple, list or set"
 
 
 @pytest.mark.asyncio