Filters on Many2Many

[MariusVinaschi]

Hi,

I have a database with Users and Organizations. Users can be in several organizations and organizations can have several users.
I put my models below to describe my database.

class User(Base, TimestampMixin, UUIDMixin):
    __tablename__ = "user"

    email: Mapped[str] = mapped_column("email", unique=True, nullable=False, index=True)
    organizations: Mapped[List["Organization"]] = relationship(secondary="organization_user", back_populates="users", viewonly=True)
    organization_associations: Mapped[List["OrganizationUser"]] = relationship(back_populates="user")

class Organization(Base, TimestampMixin, UUIDMixin):
    __tablename__ = "organization"

    name: Mapped[str] = mapped_column("name", unique=True, nullable=False, index=False)

    users: Mapped[List["User"]] = relationship(
        secondary="organization_user", back_populates="organizations", viewonly=True
    )
    user_associations: Mapped[List["OrganizationUser"]] = relationship(back_populates="organization")

class OrganizationUser(Base, TimestampMixin):
    __tablename__ = "organization_user"

    user_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("user.id"), primary_key=True)
    organization_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("organization.id"), primary_key=True)
    organization: Mapped["Organization"] = relationship(back_populates="user_associations")
    user: Mapped["User"] = relationship(back_populates="organization_associations")

I try to create filters to be able to get users from one specific organization and users who are not in one specific organization.

class OrganizationFilter(Filter):
    id: Optional[str] = None
    id__in: Optional[list[str]] = None
    id__neq: Optional[str] = None

    class Constants(Filter.Constants):
        model = Organization
        search_field_name = "search"
        search_model_fields = ["name"]
        
class UserFilter(Filter):
    organizations: Optional[OrganizationFilter] = FilterDepends(with_prefix("organizations", OrganizationFilter))

    class Constants(Filter.Constants):
        model = User
        search_field_name = "search"
        search_model_fields = ["email"]

Here is my call to the database

async def find_all_paginated(
    self,
    user_filter: UserFilter,
    skip: int = 0,
    limit: int = 100,
) -> list[UserRead]:
    query = select(self.model).offset(skip).limit(limit)
    query = user_filter.filter(query)
    results = await self.session.scalars(query)
    return results.fetchall()

I would like to be able to do something like this:

• /api/users/?organizations__id__neq={org_id}
• /api/users/?organizations__id={org_id}

I'm sure the Organization Filter works, I tried it by doing a select on organizations.

Thank you in advance. 😄