Relations

Currently ormar supports two types of relations:

• One-to-many (and many-to-one) with ForeignKey field
• Many-to-many with ManyToMany field

Below you can find a very basic examples of definitions for each of those relations.

To read more about methods, possibilities, definition etc. please read the subsequent section of the documentation.

ForeignKey

To define many-to-one relation use ForeignKey field.

class Department(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

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


class Course(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    department: Optional[Union[Department, Dict]] = ormar.ForeignKey(Department)

Tip

To read more about one-to-many relations visit foreign-keys section

Reverse ForeignKey

The definition of one-to-many relation also uses ForeignKey, and it's registered for you automatically.

So in relation ato example above.

class Department(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    # there is a virtual field here like follows
    courses: Optional[List[Course]] = ormar.ForeignKey(Course, virtual=True)
    # note that you DO NOT define it yourself, ormar does it for you.

Tip

To read more about many-to-one relations (i.e changing the name of generated field) visit foreign-keys section

Tip

Reverse ForeignKey allows you to query the related models with queryset-proxy.

It allows you to use await department.courses.all() to fetch data related only to specific department etc.

ManyToMany

To define many-to-many relation use ManyToMany field.

class Category(ormar.Model):
    class Meta:
        tablename = "categories"
        database = database
        metadata = metadata

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

class Post(ormar.Model):
    class Meta:
        tablename = "posts"
        database = database
        metadata = metadata

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

Tip

To read more about many-to-many relations visit many-to-many section

Tip

ManyToMany allows you to query the related models with queryset-proxy.

It allows you to use await post.categories.all() but also await category.posts.all() to fetch data related only to specific post, category etc.

Through fields

As part of the ManyToMany relation you can define a through model, that can contain additional fields that you can use to filter, order etc. Fields defined like this are exposed on the reverse side of the current query for m2m models.

So if you query from model A to model B, only model B has through field exposed. Which kind of make sense, since it's a one through model/field for each of related models.

class Category(ormar.Model):
    class Meta(BaseMeta):
        tablename = "categories"

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

# you can specify additional fields on through model
class PostCategory(ormar.Model):
    class Meta(BaseMeta):
        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):
    class Meta(BaseMeta):
        pass

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

Tip

To read more about many-to-many relations and through fields visit many-to-many section

Tip

ManyToMany allows you to query the related models with queryset-proxy.

It allows you to use await post.categories.all() but also await category.posts.all() to fetch data related only to specific post, category etc.

Relationship default sort order

By default relations follow model default sort order so primary_key column ascending, or any sort order se in Meta class.

Tip

To read more about models sort order visit models section of documentation

But you can modify the order in which related models are loaded during query by providing orders_by and related_orders_by parameters to relations.

In relations you can sort only by directly related model columns or for ManyToMany columns also Through model columns {through_field_name}__{column_name}

Sample configuration might look like this:

database = databases.Database(DATABASE_URL)
metadata = sqlalchemy.MetaData()


class BaseMeta(ormar.ModelMeta):
    metadata = metadata
    database = database


class Author(ormar.Model):
    class Meta(BaseMeta):
        tablename = "authors"

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


class Book(ormar.Model):
    class Meta(BaseMeta):
        tablename = "books"

    id: int = ormar.Integer(primary_key=True)
    author: Optional[Author] = ormar.ForeignKey(
        Author, orders_by=["name"], related_orders_by=["-year"]
    )
    title: str = ormar.String(max_length=100)
    year: int = ormar.Integer(nullable=True)
    ranking: int = ormar.Integer(nullable=True)

Now calls:

await Author.objects.select_related("books").get() - the books will be sorted by the book year descending

await Book.objects.select_related("author").all() - the authors will be sorted by author name ascending

Self-reference and postponed references

In order to create auto-relation or create two models that reference each other in at least two different relations (remember the reverse side is auto-registered for you), you need to use ForwardRef from typing module.

PersonRef = ForwardRef("Person")


class Person(ormar.Model):
    class Meta(ModelMeta):
        metadata = metadata
        database = db

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    supervisor: PersonRef = ormar.ForeignKey(PersonRef, related_name="employees")


Person.update_forward_refs()

Tip

To read more about self-reference and postponed relations visit postponed-annotations section