Skip to content

Join and subquery

To join one table to another so that the related model is also loaded, you can use the following methods.

  • select_related(related: Union[List, str]) -> QuerySet

  • select_all(follow: bool = True) -> QuerySet

  • prefetch_related(related: Union[List, str]) -> QuerySet

  • model (作为商品类名时使用复数)

    • Model.load() method

    • query set proxy

    • QuerysetProxy.select_related(related: Union[List, str]) method

    • QuerysetProxy.select_all(follow: bool=True) method
    • QuerysetProxy.prefetch_related(related: Union[List, str]) method

select the relevant

select_related(related: Union[List, str]) -> QuerySet

Allows prefetching related models during the same query.

When using select_lated, only one query is always run against the database, which means that a (sometimes complex) join is generated and later nested models are processed in python.

To get the correlation model, use the foreign key name.

To link related model relationships, use double underscores between names.

!!!Note that if you are from django, note that ormarselect_lated in django differs from-> in that you can only select a single relationship type, whereas in ormar you can select correlation across foreign key relationships, the opposite of the foreign key (hence the virtually auto-generated key), and the ManyToMany field (hence all relationships for the current version).

!!!tip To control which model fields are selected, use the fields() and except_fields()QuerySet methods.

!!!tip To control the order of models (main or nested), use the order_by() method.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
class Album(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    is_best_seller: bool = ormar.Boolean(default=False)

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

    id: int = ormar.Integer(primary_key=True)
    album: Optional[Album] = ormar.ForeignKey(Album)
    title: str = ormar.String(max_length=100)
    position: int = ormar.Integer()
    play_count: int = ormar.Integer(nullable=True)


# Django style
album = await Album.objects.select_related("tracks").all()

# Python style
album = await Album.objects.select_related(Album.tracks).all()

# will return album with all columns tracks

You can provide a string or list of strings (or a field/list of fields)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
class SchoolClass(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="schoolclasses")

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


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=100)


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

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    schoolclass: Optional[SchoolClass] = ormar.ForeignKey(SchoolClass)
    category: Optional[Category] = ormar.ForeignKey(Category, nullable=True)


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

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    schoolclass: Optional[SchoolClass] = ormar.ForeignKey(SchoolClass)
    category: Optional[Category] = ormar.ForeignKey(Category, nullable=True)


# Django style
classes = await SchoolClass.objects.select_related(
    ["teachers__category", "students"]).all()

# Python style
classes = await SchoolClass.objects.select_related(
    [SchoolClass.teachers.category, SchoolClass.students]).all()

# will return classes with teachers and teachers categories
# as well as classes students

The Many2Many field behaves exactly the same, allowing you to enter the name of the Many2Many field and get the final model for you.

!!!Warning If you set the ForeignKey field to non-nullable (and therefore required) during all queries, non-nullable models will be prefetched automatically, even if you do not include them in select_lated.

Note

So operations like filter(), select_related(), limit() and offset() etc. can be chained.

Something like Track.object.select_related("album").filter(album__name="Malibu").offset(1).limit(1).all()

check all

select_all(follow: bool = False) -> QuerySet

By default, when you select all(), no relationships are loaded, and similarly, when you use select_lated(), you need to explicitly specify all relationships that should be loaded. This can be cumbersome if you also want to include nested relationships.

This is why select_all() is introduced, so all relationships of the model are loaded by default (as opposed to the all() method).

By default, only models that are directly related to the parent model from which the query is run are added.

If follow=True is set, it also adds related models of related models.

!!!info In order not to fall into an infinite loop, because the dependent model also maintains a relationship with the parent model access model set.

1
2
3
4
That way already visited models that are nested are loaded, but the load do not
follow them inside. So Model A -> Model B -> Model C -> Model A -> Model X
will load second Model A but will never follow into Model X.
Nested relations of those kind need to be loaded manually.

Sample dates are as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
base_ormar_config = OrmarConfig(
    database=databases.Database(DATABASE_URL, force_rollback=True),
    metadata=sqlalchemy.MetaData(),
)


class Address(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="addresses")

    id: int = ormar.Integer(primary_key=True)
    street: str = ormar.String(max_length=100, nullable=False)
    number: int = ormar.Integer(nullable=False)
    post_code: str = ormar.String(max_length=20, nullable=False)



class Branch(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="branches")

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100, nullable=False)
    address = ormar.ForeignKey(Address)


class Company(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="companies")

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100, nullable=False, name="company_name")
    founded: int = ormar.Integer(nullable=True)
    branches = ormar.ManyToMany(Branch)

To select all companies with all branches and addresses, you simply query:

1
2
3
4
companies = await Company.objects.select_all(follow=True).all()

# which is equivalent to:
companies = await Company.objects.select_related('branches__address').all()

Of course, in this case, it's easy to emit explicit relationship names in select_lated, but the benefits of select_all() show up when you have multiple relationships.

For example, if Company has 3 relationships, and all 3 relationships have its own 3 relationships, you must issue 9 relationship strings to select_lated, and select_all() cannot change the relationship name.

!!!note Note that you can link select_all() with other QuerySet methods (such as filter, exclude_fields, etc.). To exclude relationships, use the except_fields() call with the name of the relationship to exclude (also nested).

prefetch correlation

prefetch_related(related: Union[List, str]) -> QuerySet

Allow prefetching of related models during query-but as opposed to select_lated, each subsequent model is retrieved in a separate database query.

With prefetch_lated, each model runs a query against the database, which means you execute multiple queries one after the other.

To get the correlation model, use the foreign key name.

To link related model relationships, use double underscores between names.

!!!tip To control which model fields are selected, use the fields() and except_fields()QuerySet methods.

!!!tip To control the order of models (main or nested), use the order_by() method.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
class Album(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    is_best_seller: bool = ormar.Boolean(default=False)

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

    id: int = ormar.Integer(primary_key=True)
    album: Optional[Album] = ormar.ForeignKey(Album)
    title: str = ormar.String(max_length=100)
    position: int = ormar.Integer()
    play_count: int = ormar.Integer(nullable=True)


# Django style
album = await Album.objects.prefetch_related("tracks").all()

# Python style
album = await Album.objects.prefetch_related(Album.tracks).all()


# will return album will all columns tracks

You can provide a string or a list of strings

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
class SchoolClass(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="schoolclasses")

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


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=100)


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

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    schoolclass: Optional[SchoolClass] = ormar.ForeignKey(SchoolClass)
    category: Optional[Category] = ormar.ForeignKey(Category, nullable=True)


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

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    schoolclass: Optional[SchoolClass] = ormar.ForeignKey(SchoolClass)
    category: Optional[Category] = ormar.ForeignKey(Category, nullable=True)


# Django style
classes = await SchoolClass.objects.prefetch_related(
    ["teachers__category", "students"]).all()

# Python style
classes = await SchoolClass.objects.prefetch_related(
    [SchoolClass.teachers.category, SchoolClass.students]).all()
# will return classes with teachers and teachers categories
# as well as classes students

The Many2Many field behaves exactly the same, allowing you to enter the name of the Many2Many field and get the final model for you.

!!!warning If you set the ForeignKey field to non-nullable (and therefore required) during all queries, non-nullable models will be prefetched automatically, even if you do not include them in select_lated.

Note

So operations like filter(), select_related(), limit() and offset() etc. can be chained.

Something like Track.object.select_related("album").filter(album__name="Malibu").offset(1).limit(1).all()

Select correlation and prefetch correlation

Which of-> select_lated or prefetch_lated should you use?

Well, it really depends on your data. The best answer is to try it yourself and see which one performs faster/better within the limits of your system.

What to remember:

manifestation

Number of queries: select_lated always performs one query against the database, while prefetch_lated performs multiple queries. Typically, query (I/O) operations are the slowest, but not necessarily.

Number of rows: Suppose there are 10,000 objects in table A, each object has 3 children in table B, and then each object in table B has 2 children in table C. As follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
                     Model C
                   /
           Model B - Model C
         / 
Model A  - Model B - Model C
       \           \ 
        \            Model C
         \
           Model B - Model C
                   \ 
                     Model C

This means that select_lated will always return 60,000 rows (10,000*3 *2), later compressed to 10,000 models.

How many rows will return prefetch_lated?

Well, it depends, if each of models B and C is unique, then it will return 10,000 rows in the first query and 30,000 rows in the second query.(Each of the 3 children of A in Table B is unique), and 60,000 rows (each of the children in Table B is unique). The 2 children of Model B in Table C are unique in the third query.)

In this case, select_lated seems like a better choice, not only does it run a query compared to 3 prefetch_lated, but it returns 60,000 rows compared to 100,000 prefetch_lated (10+30+60k).

But what if each model A has exactly the same 3 model B's, and each model C has exactly the same model C's? Select_lated will still return 60 000 rows, while prefetch_lated will return 10 000 rows for model A, 3 rows for model B, and 2 rows for model C. So 10 006 rows total. Now, depending on the structure of the model (i.e., if it has long Text() fields, etc.), prefetch_lated may be faster, although it requires three separate queries to be executed instead of one.

memory

Ormar does not keep a registry of loaded models.

This means that in the select_lated example above, you will always have 10,000 model A, 30,000 model B (even though the only 3 - select_lated process in the database generates new child models for each parent model), and 60,000 model C.

If rows 1, 10, 100, etc. share the same model B and you update one of them, the remaining rows sharing the same child will not be updated immediately. If you save the changes to the database, the changes will only be available after reloading (load each child individually or load the entire query again). This means select_lated will use more memory because each child object is instantiated as a new object

  • Clearly using his own space.

!!!Note that if we decide to introduce caching, this may change in future releases.

By default, all children (or events where the same model is loaded more than 2 times) are completely separate, distinct python objects, even though they represent the same row in the database.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
They will evaluate to True when compared, so in example above:

```python
# will return True if child1 of both rows is the same child db row 
row1.child1 == row100.child1

# same here:
model1 = await Model.get(pk=1)
model2 = await Model.get(pk=1) # same pk = same row in db
# will return `True`
model1 == model2
```

but

```python
# will return False (note that id is a python `builtin` function not ormar one).
id(row1.child1) == id(ro100.child1)

# from above - will also return False
id(model1) == id(model2)
```

In contrast, with prefetch_lated, each unique distinct child model is instantiated only once, and the same child model is shared among all parent models. This means that in the prefetch_lated example above, if there are 3 distinct models in Table B and 2 distinct models in Table C, only 5 child nested models will be shared among all Model A instances. This also means that if you update any attribute, all parent objects update that attribute because they share the same child object.

model method

Each model instance has a set of methods to save, update, or load itself.

to load

You can load the ForeignKey correlation model by calling the load() method.

Load() can be used to refresh the model from the database if it has been changed by another process.

!!!tip Read more about the load() method in Model Methods

QuerysetProxy method

Returns a list of related models when directly accessing the related ManyToMany field and ReverseForeignKey.

But it also exposes a subset of the QuerySet API, so you can filter, create, select related models, and so on directly from the parent model.

select the relevant

Exactly the same as the select_lated function above, but allows you to get related objects from the other end of the relationship.

!!!tip To learn more about QuerysetProxy, visit the QuerysetProxy section

check all

Exactly the same as the select_all function above, but allows you to get related objects from the other end of the relationship.

!!!tip To learn more about QuerysetProxy, visit the QuerysetProxy section

prefetch correlation

Exactly the same as the prefetch_lated function above, but allows you to get related objects from the other end of the relationship.

!!!tip To learn more about QuerysetProxy, visit the QuerysetProxy section