many-to-many

ManyToMany(to, through) has the required parameter to and the optional through, and it takes the target and relational model classes.

Sqlalchemy columns and types are automatically obtained from the target model.

Sqlalchemy columns: classes of primary key columns of the target model
Type (for pydantic): Type of target model

definition model

from typing import List, Optional

import databases
import ormar
import sqlalchemy

DATABASE_URL = "sqlite:///test.db"

ormar_base_config = ormar.OrmarConfig(
    database=databases.Database(DATABASE_URL), metadata=sqlalchemy.MetaData()
)


class Author(ormar.Model):
    ormar_config = ormar_base_config.copy(tablename="authors")

    id: int = ormar.Integer(primary_key=True)
    first_name: str = ormar.String(max_length=80)
    last_name: str = ormar.String(max_length=80)


class Category(ormar.Model):
    ormar_config = ormar_base_config.copy(tablename="categories")

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=40)


class Post(ormar.Model):
    ormar_config = ormar_base_config.copy(tablename="posts")

    id: int = ormar.Integer(primary_key=True)
    title: str = ormar.String(max_length=200)
    categories: Optional[List[Category]] = ormar.ManyToMany(Category)
    author: Optional[Author] = ormar.ForeignKey(Author)

Create sample data:

guido = await Author.objects.create(first_name="Guido", last_name="Van Rossum")
post = await Post.objects.create(title="Hello, M2M", author=guido)
news = await Category.objects.create(name="News")

inverse relationship

The foreign key field automatically registers the opposite side of the relationship.

By default, it is the child (source) model name + s, as in the post in the following code snippet:

class Category(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="categories")

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=40)


class Post(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    title: str = ormar.String(max_length=200)
    categories: Optional[List[Category]] = ormar.ManyToMany(Category)

# create some sample data
post = await Post.objects.create(title="Hello, M2M")
news = await Category.objects.create(name="News")
await post.categories.add(news)

# now you can query and access from both sides:
post_check = Post.objects.select_related("categories").get()
assert post_check.categories[0] == news

# query through auto registered reverse side
category_check = Category.objects.select_related("posts").get()
assert category_check.posts[0] == post

Reverse relationships expose APIs to manage related objects from the parent.

relevant name

By default, the correlation name is generated in the same way as the foreign key relationship (class. name. lower ()+'s'), but you can also override this name by providing the correlation name parameter, as follows:

categories: Optional[Union[Category, List[Category]]] = ormar.ManyToMany(
        Category, through=PostCategory, related_name="new_categories"
    )

!!!warning When you provide multiple relationships to the same model, related_names will no longer be generated automatically for you. Therefore, in this case, you must provide related_names for all fields except one (one can be default and generated) or for all related fields.

Skip the reverse relationship

If you're sure you don't want a reverse relationship, you can use the skip_reverse = True flag for ManyToMany.

If you set the skip_reverse flag internally, the field is still registered at the other end of the relationship, so you can:

Filter by relevant model fields in reverse model
order_by Related model fields in reverse model

But you cannot:

Use related_name to access related fields from the reverse model
Even if you select correlation from the opposite side of the model, the returned model does not populate the reverse instance (connections are not blocked, so you can still filter and sort relationships)
This relationship is not populated in model_dump () and json () When* is populated from dictionary or json (also via fastapi), you cannot pass nested related objects. Depending on the extra settings in pydantic configuration, it will be ignored or raise an error.

Examples:

class Category(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="categories")

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=40)


class Post(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    title: str = ormar.String(max_length=200)
    categories: Optional[List[Category]] = ormar.ManyToMany(Category, skip_reverse=True)

# create some sample data
post = await Post.objects.create(title="Hello, M2M")
news = await Category.objects.create(name="News")
await post.categories.add(news)

assert post.categories[0] == news  # ok
assert news.posts  # Attribute error!

# but still can use in order_by
categories = (
    await Category.objects.select_related("posts").order_by("posts__title").all()
)
assert categories[0].first_name == "Test"

# note that posts are not populated for author even if explicitly
# included in select_related - note no posts in model_dump()
assert news.model_dump(exclude={"id"}) == {"name": "News"}

# still can filter through fields of related model
categories = await Category.objects.filter(posts__title="Hello, M2M").all()
assert categories[0].name == "News"
assert len(categories) == 1

through the model

Alternatively, if you want to add additional fields, you can explicitly create and pass model classes.

import databases
import ormar
import sqlalchemy

DATABASE_URL = "sqlite:///test.db"

ormar_base_config = ormar.OrmarConfig(
    database=databases.Database(DATABASE_URL), metadata=sqlalchemy.MetaData()
)


class Category(ormar.Model):
    ormar_config = ormar_base_config.copy(tablename="categories")

    id = ormar.Integer(primary_key=True)
    name = ormar.String(max_length=40)


class PostCategory(ormar.Model):
    ormar_config = ormar_base_config.copy(tablename="posts_x_categories")

    id: int = ormar.Integer(primary_key=True)
    sort_order: int = ormar.Integer(nullable=True)
    param_name: str = ormar.String(default="Name", max_length=200)


class Post(ormar.Model):
    ormar_config = ormar_base_config.copy()

    id: int = ormar.Integer(primary_key=True)
    title: str = ormar.String(max_length=200)
    categories = ormar.ManyToMany(Category, through=PostCategory)

!!!Note that even if you do not provide it through the model, it is automatically created for you and must still be included in the example of the alembic migration.

!!!tip Note that if you want to customize the Through model name or database table name for that model, you need to provide the Through model.

If you do not provide a Pass field, the field is generated for you.

The default naming convention is:

For class names, it is a union of two class names (parent + other), so in the example above it will be PostCategory
For table names, it's similar, but with a dash and an s at the end of the class lowercase name, which in the example above would be posts_categorys

Customizing by Relationship Name

By default, the correlation model name defaults to lowercase through model relationship names.

So in this example:

... # course declaration omitted
class Student(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    courses = ormar.ManyToMany(Course)

# will produce default Through model like follows (example simplified)
class StudentCourse(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="students_courses")

    id: int = ormar.Integer(primary_key=True)
    student = ormar.ForeignKey(Student) # default name
    course = ormar.ForeignKey(Course)  # default name

To customize the field/relationship names in the Through model, you can now use the new parameters of ManyToMany:

through_relation_name -The field name leading to the model declaring ManyToMany
through_reverse_relation_name-Name of the field leading to the model to which ManyToMany leads

Examples:

... # course declaration omitted
base_ormar_config = ormar.OrmarConfig(
    database=databases.Database("sqlite:///db.sqlite"),
    metadata=sqlalchemy.MetaData(),
)


class Student(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    courses = ormar.ManyToMany(Course,
                               through_relation_name="student_id",
                               through_reverse_relation_name="course_id")

# will produce Through model like follows (example simplified)
class StudentCourse(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="student_courses")

    id: int = ormar.Integer(primary_key=True)
    student_id = ormar.ForeignKey(Student) # set by through_relation_name
    course_id = ormar.ForeignKey(Course)  # set by through_reverse_relation_name

!!!Note that explicit declaration of relationships in the Through model is forbidden, so even if you provide your own custom Through model, you cannot change the name there, and you need to use the same through_relationship_name and through_reverse_relationship_name parameters.

across the fields

The through field is automatically added to the opposite side of the relationship.

Exposed fields are named lowercase by class name.

The exposed field explicitly does not load any relationship because the relationship is populated in the ManyToMany field, so it is useful only if additional fields are provided on the Through model.

In the example model setup, look like this:

import databases
import ormar
import sqlalchemy

DATABASE_URL = "sqlite:///test.db"

ormar_base_config = ormar.OrmarConfig(
    database=databases.Database(DATABASE_URL), metadata=sqlalchemy.MetaData()
)


class Category(ormar.Model):
    ormar_config = ormar_base_config.copy(tablename="categories")

    id = ormar.Integer(primary_key=True)
    name = ormar.String(max_length=40)


class PostCategory(ormar.Model):
    ormar_config = ormar_base_config.copy(tablename="posts_x_categories")

    id: int = ormar.Integer(primary_key=True)
    sort_order: int = ormar.Integer(nullable=True)
    param_name: str = ormar.String(default="Name", max_length=200)


class Post(ormar.Model):
    ormar_config = ormar_base_config.copy()

    id: int = ormar.Integer(primary_key=True)
    title: str = ormar.String(max_length=200)
    categories = ormar.ManyToMany(Category, through=PostCategory)

The through field can be used as a normal model field in most QuerySet operations.

Note that the pass field is only appended to the relevant end of the query, so:

post = await Post.objects.select_related("categories").get()
# source model has no through field
assert post.postcategory is None
# related models have through field
assert post.categories[0].postcategory is not None

# same is applicable for reversed query
category = await Category.objects.select_related("posts").get()
assert category.postcategory is None
assert category.posts[0].postcategory is not None

The pass field can be used to filter data.

post = (
        await Post.objects.select_related("categories")
        .filter(postcategory__sort_order__gt=1)
        .get()
        )

!!!tip Note that although the actual instances are not populated in the source model, in queries, order by statements, etc., you can access them from both sides through the model. So the following query has exactly the same effect (note access through categories)

```python
post = (
    await Post.objects.select_related("categories")
    .filter(categories__postcategory__sort_order__gt=1)
    .get()
    )
```

The model allows queries to be used sequentially.

post = (
        await Post.objects.select_related("categories")
        .order_by("-postcategory__sort_order")
        .get()
    )

You can also use fields and except_fields to select subsets of columns in the normal QuerySet fashion.

post2 = (
        await Post.objects.select_related("categories")
        .exclude_fields("postcategory__param_name")
        .get()
        )

!!! Warning Note that because through fields explicitly invalidates all relational fields because the relationships are populated in the ManyToMany field, standard model methods such as save() and update() should not be used until the fields are reloaded from the database.

If you want to modify the pass-through field in-place, remember to reload it from the database. Otherwise, you'll set the relationship to None, effectively rendering the field useless!

# always reload the field before modification
await post2.categories[0].postcategory.load()
# only then update the field
await post2.categories[0].postcategory.update(sort_order=3)

Note that reloading the model actually reloads the relationships as pk_only models (only primary keys are set), so they are not fully populated, but sufficient to preserve the relationships when updated.

!!!warns that if you use ie fastapi, correlation models loaded partially through fields may cause pydantic validation errors (this is the main reason they are not populated by default). Therefore, you either need to exclude correlation fields from the response or load correlation models completely. In the example above, this means:python await post2.categories[0].postcategory.post.load() await post2.categories[0].postcategory.category.load() or, you can use load_all():python await post2.categories[0].postcategory.load_all()

The preferred way to update is through the queryset proxy update() method

# filter the desired related model with through field and update only through field params
await post2.categories.filter(name='Test category').update(postcategory={"sort_order": 3})

relations act

add

Add (Project: Model,**kwargs)

Allows you to add models to many-to-many relationships.

# Add a category to a post.
await post.categories.add(news)
# or from the other end:
await news.posts.add(post)

!!!Warning In all non-None cases, the primary key value of the correlation model must exist in the database.

1	`Otherwise an IntegrityError will be raised by your database driver library.`

If you declare models using the Through model with additional fields, you can populate submodels during the addition of them to the relationship.

To do this, pass keyword arguments with field names and values to the add() call.

Note that this only applies to many-to-many relationships.

post = await Post(title="Test post").save()
category = await Category(name="Test category").save()
# apart from model pass arguments referencing through model fields
await post.categories.add(category, sort_order=1, param_name='test')

eliminate

Delete relevant models one by one.

Relationships in the database are also deleted.

1	`await news.posts.remove(post)`

eliminate

Delete all related models with one call.

Relationships in the database are also deleted.

wait for news.posts.clear()

query set proxy

Reverse relationships expose the QuerysetProxy API, which allows you to query related models just like you would issue a normal query.

To find out which methods of QuerySet are available, read the querysetproxy below