MultiFilter with Many-to-Many relationships

[0asys]

Summary

Is there an example how to use the MultiFilter with many-to-many relationships?

I saw that there is a prefer_any option ot use the CollectionFilter with _any() - but I didn't get it to work so far, maybe I'm misunderstanding something.

advanced-alchemy/advanced_alchemy/filters.py

Line 293 in b1fea84

prefer_any: bool = False,

Example, with manually enabled "prefer_any":

{"and_":[{"type":"collection","field_name":"tags","values":["b0c29665-47c0-4c2f-a6ac-9dea28f89126"]}]}

Results in this error:

DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 
'asyncpg.exceptions.DataError'>: invalid input for query argument $1: 
['b0c29665-47c0-4c2f-a6ac-9dea28f89126'] (invalid array element at index 0: a 
boolean is required (got type str))
[SQL: SELECT item.description, item.article_nr, item.quantity, 
item.item_type_id, item.item_brand_id, item.price, item.size, item.commission, 
item.customer_id, item.state, item.bag_id, item.id, item.sa_orm_sentinel, 
item.created_at, item.updated_at, count(*) OVER () AS anon_1 
FROM item, item_tag_table AS item_tag_table_1, item_tag 
WHERE (item.id = item_tag_table_1.item_id AND item_tag.id = 
item_tag_table_1.tag_id) = ANY ($1) 
 LIMIT $2::INTEGER OFFSET $3::INTEGER]
[parameters: (['b0c29665-47c0-4c2f-a6ac-9dea28f89126'], 10, 0)]
(Background on this error at: https://sqlalche.me/e/20/dbapi)

Basic Example

No response

Drawbacks and Impact

No response

Unresolved questions

No response

[0asys]

Maybe something like this needs to be added, assuming the related field is id:

class CollectionFilter():

  def append_to_statement(...)
  
          # Handle the case for many-to-many relationships
          if hasattr(field.property, 'secondary'):  # Check if it's a many-to-many relationship
              # Dynamically get the related model from the relationship
              related_model = field.property.mapper.class_
  
              # Get the primary key column (e.g., 'id' in Tag)
              related_id_column = related_model.id
  
              # Now, we can dynamically use the related model's 'id' column in the condition
              return cast(
                  "StatementTypeT",
                  statement.where(
                      field.any(related_id_column.in_(self.values))  # Use the dynamic 'id' column
                  )
              )