Filtering models by their many to many relationship

[farzadsbakhtiar]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from sqlmodel import SQLModel, Relationship, Field, select

def find_users(workspace: Workspace, min_logins: int):
  """ find all users of the workspace with at least min_logins logins """
  statement = select(User).where(User.logins >= min_logins)
                     .where(workspace in User.workspaces) # -- this doesn't work!
  ...


class UserWorkspace(SQLModel, table=True):
  user_id: int = Field(foreign_key='user.id')
  workspace_id: int = Field(foreidgn_key='workspace.id')


class User(SQLModel, table=True):
  id: int ...
  username: str
  logins: int 
  workspaces: list[Workspace] = Relationship(link_model=UserWorkspace)


class Workspace(SQLModel, table=True):
  id: int ...

Description

I can't find a way to filter models by their relationship if it is a x-to-many relationship. The example above tries to find
all users of a workspace with at least min_logins, but there is no way to filter the users by their workspace.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10.0

Additional Context

No response

[8thgencore]

To filter models by their relationship in a x-to-many relationship, you can use the any_ method of the relationship attribute. Here's how you can modify your code to filter users by their workspace:

from sqlmodel import SQLModel, Relationship, Field, select, any_

def find_users(workspace: Workspace, min_logins: int):
  """ find all users of the workspace with at least min_logins logins """
  statement = select(User).where(User.logins >= min_logins) \
                           .where(User.workspaces.any_(id=workspace.id))
  ...

class UserWorkspace(SQLModel, table=True):
  user_id: int = Field(foreign_key='user.id')
  workspace_id: int = Field(foreign_key='workspace.id')

class User(SQLModel, table=True):
  id: int ...
  username: str
  logins: int 
  workspaces: list[Workspace] = Relationship(link_model=UserWorkspace)

class Workspace(SQLModel, table=True):
  id: int ...

In this modified code, the any_ method is used to filter the users by their workspace. The id attribute of the Workspace model is used to filter the users, and it is compared to the workspace.id attribute, which is the ID of the workspace passed to the find_users function.

[farzadsbakhtiar]

Thank you very much, that solves the problem.
Where is the reference documentation for this?

[antont]

function sqlalchemy.sql.expression.any_

[farzadsbakhtiar]

Is there a doc that describes the relationship of sqlalchemy and sqlmodel?

[antont]

All db operations of SQLModel are SQLAlchemy code, the select imported there is actually imported from SQLAlchemy etc. So in simple terms SQLModel is SQLAlchemy. It just integrates Pydantic with SQLAlchemy ORM (declarative_base), so that same Pydantic models then work automatically both for SQLAlchemy and e.g. FastAPI use.

Maybe someone knows a nice doc telling the full pic.

[vlntsolo]

There might be a typo, in any_() method. I had to replace it with any() without underscore to make it work.