relation

Ormar currently supports two types of relationships:

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

You can find a very basic definition example of each relationship below.

To learn more about methods, possibilities, definitions, and more, read the rest of the documentation.

foreign key

To define many-to-one relationships, use foreign key fields.

from typing import Dict, Optional, Union

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 Department(ormar.Model):
    ormar_config = ormar_base_config.copy()

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


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

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

!!! Tip To learn more about one-to-many relationships, visit the Foreign Key section

reverse foreign key

One-to-many relationship definitions also use ForeignKey, which automatically registers for you.

So, in the example above.

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

    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 learn more about many-to-one relationships (that is, changing the name of a generated field), visit the Foreign Key section

!!!Tip ReverseforeignKey allows you to query related models using queryset-proxy.

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

many to many

To define many-to-many relationships, use the ManyToMany field.

class Category(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
        tablename="categories",
    )

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

class Post(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        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 learn more about many-to-many relationships, visit the many-to-many section

!!!Tip ManyToMany allows you to query related models using 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.

across the fields

As part of the ManyToMany relationship, you can define a pass-through model that can contain additional fields that can be used for filtering, sorting, etc. Fields defined like this will be exposed on the opposite side of the current query for the m2m model.

Therefore, if you query from Model A to Model B, only Model B exposes the through field. Which makes sense because it is a one-way model/field for each related model.

class Category(ormar.Model):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
        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):
    ormar_config = ormar.OrmarConfig(
        database=database,
        metadata=metadata,
        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.OrmarConfig(
        database=database,
        metadata=metadata,
    )

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

!!!tip To learn more about many-to-many relationships and fields, visit many-to-many sections

!!!Tip ManyToMany allows you to query related models using 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, relationships follow the model default sort order, so the Primary_key column is in ascending order, or whatever sort order is in the ormar_config object.

!!! Tip To learn more about model sort order, visit the Models section of the document

However, you can modify the order in which related models are loaded during a query by supplying the orders_by and related_orders_by parameters to the relationship.

In relationships, you can sort only by directly related model columns or, for many-to-many columns, by model columns {through_field_name}__{column_name}

An example configuration might look like this:

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


base_ormar_config = ormar.OrmarConfig(
    database=database,
    metadata=metadata,
)


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

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


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

    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 call:

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

await Book.objects.select_related("author").all() - Authors will be sorted by author name in ascending order

Self-reference and deferred reference

To create an automatic relationship or to create two models that reference each other in at least two different relationships (remember that the reverse is automatically registered for you), you need to use ForwardRef in the input module.

PersonRef = ForwardRef("Person")

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

    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 learn more about self-referencing and deferred relationships, visit the deferred comments section