sqlalchemy-filterset

Key Updates

1. Renaming of Strategies for Clarity:

   • RelationJoinStrategy has been renamed to JoinStrategy
   • RelationSubqueryExistsStrategy has been renamed to SubqueryExistsStrategy.

2. Enhanced JoinStrategy:

   • JoinStrategy now supports additional attributes:
     • is_outer: Enables the creation of outer joins.
     • is_full: Enables the creation of full joins.

3. Introduction of MultiJoinStrategy:

   • The new MultiJoinStrategy allows for the addition of several joins or several chained joins with filtering capabilities.
   • This strategy is designed to handle complex querying scenarios where multiple joins and filters are required.

Example Usage

ParentsTagFilter

This filter demonstrates how to implement a custom filter using the new MultiJoinStrategy:

class ParentsTagFilter(Filter):
    def filter(self, query, value, values):
        expression = or_(Parent.name == value, GrandParent.name == value))
        return self.strategy.filter(query, expression)

ItemFilterSet

This filter set showcases how to use the new MultiJoinStrategy in combination with JoinStrategy:

class ItemFilterSet(AsyncFilterSet[Item]):
    several_joins = MultiJoinStrategy(
        JoinStrategy(Parent, Item.parent_id == Parent.right_id),
        JoinStrategy(GrandParent, Parent.grand_parent_id == GrandParent.id)
    )
    
    id = Filter(Item.id)  
    ids = InFilter(Item.id)
    tag = ParentsTagFilter(strategy=several_joins)

With this update, all joins, whether originating from the base query or added via the RelationJoinStrategy, will be automatically deduplicated.

Suppose you have the following strategy defined:
strategy = RelationJoinStrategy(Parent, onclause=Parent.id == Item.parent_id)

Below are three different ways to construct a query:

1. strategy.filter(select(Item.id), Parent.name == "test")
2. strategy.filter(select(Item.id).join(Parent), Parent.name == "test")
3. strategy.filter(select(Item.id).join(Parent, onclause=Parent.id == Item.parent_id), Parent.name == "test")

With the new join deduplication feature, all of these methods will produce the same optimized query:

SELECT item.id 
FROM item 
JOIN parent ON parent.id = item.parent_id 
WHERE parent.name = 'test'

1. Added IsNullFilter
2. Minor improvement: BaseFilterSet added to main init.py

Supprot for sqlalchemy 2.0+

Simplify imports and dependencies.

First version of sqlalchemy-filterset!