Skip to content

Filtering and sorting data

You can filter data using the following methods (sql where clause).

  • filter(*args, **kwargs) -> QuerySet

  • exclude(*args, **kwargs) -> QuerySet

  • get(*args, **kwargs) -> Model

  • get_or_none(*args, **kwargs) -> Optional[Model]

  • get_or_create(_defaults: Optional[Dict[str, Any]] = None, *args, **kwargs) -> Tuple[Model, bool]

  • all(*args, **kwargs) -> List[Optional[Model]]

  • query set proxy

    • QuerysetProxy.filter(*args, **kwargs) method
    • QuerysetProxy.exclude(*args, **kwargs) method
    • QuerysetProxy.get(*args, **kwargs) method
    • QuerysetProxy.get_or_none(*args, **kwargs) method
    • QuerysetProxy.get_or_create(_defaults: Optional[Dict[str, Any]] = None, *args, **kwargs) method
    • QuerysetProxy.all(*args, **kwargs) method

And sort the data using the following method (sql order by clause).

  • order_by(columns:Union[List, str, OrderAction]) -> QuerySet
  • query set proxy
    • QuerysetProxy.order_by(columns:Union[List, str, OrderAction]) method

filtration

screen

filter(*args, **kwargs) -> QuerySet

Allows you to filter by any model attribute/field and get instances using filters across FK relationships.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
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)
    name: str = ormar.String(max_length=100)
    position: int = ormar.Integer()
    play_count: int = ormar.Integer(nullable=True)


track = Track.objects.filter(name="The Bird").get()
# will return a track with name equal to 'The Bird'

tracks = Track.objects.filter(album__name="Fantasies").all()
# will return all tracks where the columns album name = 'Fantasies'

Django-style filters

You can change the filter operand using special filter suffixes:

  • exact-matches exactly with value, sql column =* can be written as album__name__exact='Malibu'
  • iexact -exact match sql column =(case insensitive)
    • can be written as album__name__iexact='malibu'
  • contains- sql column LIKE '%%'* can be written as album__name__contains ='Mal'
  • icontains - sql column LIKE '%%'(case insensitive)* can be written as album__name__icontains='mal'
  • in-sql column IN (<VALUE1>, <VALUE2>, ...)* can be written album__name__in=['Malibu', 'Barclay']
  • isnull - sql column IS NULL (and sql column IS NOT NULL)
    • can be written as album__name__isnull=True (isnotnull album__name__isnull=False)
  • gt - sql column>(greater than)* can be written as position__gt=3
  • gte - sql column>=(greater than or equal to)* can be written as position__gte=3
  • lt - sql column <(below)* can be written as position__lt=3
  • lte - sql column <=(less than or equal to)* can be written as position__lte=3
  • startswith - sql column LIKE '%'* can be written as album__name__startswith='Mal'
  • istartswith - sql column LIKE '%'(case insensitive)* can be written as album__name__istartswith='mal'
  • endswith - sql column LIKE '%'* can be written as album__name__endswith='ibu'
  • iendswith - sql column LIKE '%'(case-insensitive)* can be written as album__name__iendswith='IBU'

Some samples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# sql: ( product.name = 'Test'  AND  product.rating >= 3.0 ) 
Product.objects.filter(name='Test', rating__gte=3.0).get()

# sql: ( product.name = 'Test' AND product.rating >= 3.0 ) 
#       OR (categories.name IN ('Toys', 'Books'))
Product.objects.filter(
    ormar.or_(
        ormar.and_(name='Test', rating__gte=3.0), 
        categories__name__in=['Toys', 'Books'])
    ).get()
# note: to read more about and_ and or_ read complex filters section below

Python-style filters

  • Exact-exactly matches the value, sql column =
    • It can be written as www.example.com =='Malibu
  • iexact-exact match sql column =(case insensitive)
    • can be written as Track.album.name.iexact('malibu')
  • CONTAINS- sql COLUMN LIKE '%%' * It can be written as Track.album.name % 'Mal') * can be written as Track.album.name.contains('Mal')
  • icontains-sql column LIKE ' %%'(case insensitive)* can be written as Track.album.name.icontains('mal')
  • in-sql column IN (<VALUE1>, <VALUE2>, ...)*can be written Track.album.name << ['Malibu', 'Barclay']*can be written Track.album.name.in_(['Malibu', 'Barclay'])
  • isnull - sql column IS NULL (and sql column IS NOT NULL)* You can write Track.album.name>> None * Can be written as Track.album.name.isnull(True) * It can be written as not null. Track.album.name.isnull(False) * not null can be written ~(Track.album.name >> None) * not null can be written ~(Track.album.name.isnull(True))
  • gt-sql column>(greater than)* can be written as www.example.com> 3
  • gte-sql column>=(greater than or equal to)* can be written as www.example.com>= 3
  • lt-sql column <(below)* can be written as www.example.com <3
  • lte-sql column <=(less than or equal to)* can be written as www.example.com <= 3
  • startswith-sql column LIKE '%'* can be written as Track.album.name.startswith('Mal')
  • istartswith - sql column LIKE '%'(case insensitive)* can be written as Track.album.name.istartswith('mal')
  • endswith - sql column LIKE '%'* can be written as Track.album.name.endswith('ibu')
  • iendswith - sql column LIKE '%'(case insensitive)* can be written as Track.album.name.iendswith('IBU')

Some samples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# sql: ( product.name = 'Test'  AND  product.rating >= 3.0 ) 
Product.objects.filter(
    (Product.name == 'Test') & (Product.rating >= 3.0)
).get()

# sql: ( product.name = 'Test' AND product.rating >= 3.0 ) 
#       OR (categories.name IN ('Toys', 'Books'))
Product.objects.filter(
        ((Product.name == 'Test') & (Product.rating >= 3.0)) | 
        (Product.categories.name << ['Toys', 'Books'])
    ).get()

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()

!!!Warning Note that you do not have to specify the % wildcard in contains and other filters, it is added for you. If you include % in the search value, it will be escaped and treated as a literal percent sign within the text.

exclude

exclude(*args, **kwargs) -> QuerySet

Works exactly the same as filters, and all modifiers (suffixes) are the same, but returns the not condition.

So if you use filter(name ='John '), which equals where name ='John' in SQL, then except(name ='John ') equals where name <> 'John'

Note that all conditions are joined, so if you pass multiple values, it becomes the union of the conditions.

Excluding (name ='John ',age>=35) becomes where not (name='John' and age>=35)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
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)
    name: str = ormar.String(max_length=100)
    position: int = ormar.Integer()
    play_count: int = ormar.Integer(nullable=True)


notes = await Track.objects.exclude(position_gt=3).all()
# returns all tracks with position < 3

Complex filters (including OR)

By default, both filter() and except() methods combine the filter options provided with AND conditions so that filter(name="John", age__gt=30) translates to WHERE name = 'John' AND age > 30.

Sometimes it's useful to query a database with conditions that shouldn't be federated, such as WHERE name = 'John' OR age > 30, or to build a complex where query over which you want more control. After all,WHERE (name = 'John' OR age > 30) and city='New York' is completely different from WHERE name = 'John' OR (age > 30 and city='New York').

To construct OR and nested conditions, ormar provides two functions that can be used in filter() and except() in QuerySet and QuerysetProxy.

!!!Note that you can provide these methods in any other method that accepts*args, such as get() or all().

Calls to or_and and_can be nested within each other or combined with keyword arguments. Since it sounds more complicated than it is, let's look at some examples.

Given an example model like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
base_ormar_config = ormar.OrmarConfig(
    database=databases.Database(DATABASE_URL),
    metadata=sqlalchemy.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)
    title: str = ormar.String(max_length=100)
    year: int = ormar.Integer(nullable=True)

Let's create some sample data:

1
2
3
4
5
6
7
tolkien = await Author(name="J.R.R. Tolkien").save()
await Book(author=tolkien, title="The Hobbit", year=1933).save()
await Book(author=tolkien, title="The Lord of the Rings", year=1955).save()
await Book(author=tolkien, title="The Silmarillion", year=1977).save()
sapkowski = await Author(name="Andrzej Sapkowski").save()
await Book(author=sapkowski, title="The Witcher", year=1990).save()
await Book(author=sapkowski, title="The Tower of Fools", year=2002).save()

We can construct some example complex queries:

Let's choose Tolkien's books or books written after 1970.

sql: WHERE ( authors.name = 'J.R.R. Tolkien' OR books.year > 1970 )

Django style

1
2
3
4
5
6
books = (
    await Book.objects.select_related("author")
    .filter(ormar.or_(author__name="J.R.R. Tolkien", year__gt=1970))
    .all()
)
assert len(books) == 5

Python style

1
2
3
4
5
6
books = (
    await Book.objects.select_related("author")
    .filter((Book.author.name=="J.R.R. Tolkien") | (Book.year > 1970))
    .all()
)
assert len(books) == 5

Let us now choose books written by Tolkien after 1960 or before 1940.

sql: WHERE ( books.year > 1960 OR books.year < 1940 ) AND authors.name = 'J.R.R. Tolkien'

Django style

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# OPTION 1 - split and into separate call
books = (
    await Book.objects.select_related("author")
    .filter(ormar.or_(year__gt=1960, year__lt=1940))
    .filter(author__name="J.R.R. Tolkien")
    .all()
)
assert len(books) == 2

# OPTION 2 - all in one
books = (
    await Book.objects.select_related("author")
    .filter(
        ormar.and_(
            ormar.or_(year__gt=1960, year__lt=1940),
            author__name="J.R.R. Tolkien",
        )
    )
    .all()
)

assert len(books) == 2
assert books[0].title == "The Hobbit"
assert books[1].title == "The Silmarillion"

Python style

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
books = (
    await Book.objects.select_related("author")
    .filter((Book.year > 1960) | (Book.year < 1940))
    .filter(Book.author.name == "J.R.R. Tolkien")
    .all()
)
assert len(books) == 2

# OPTION 2 - all in one
books = (
    await Book.objects.select_related("author")
    .filter(
        (
            (Book.year > 1960) | (Book.year < 1940)
        ) & (Book.author.name == "J.R.R. Tolkien")
    )
    .all()
)

assert len(books) == 2
assert books[0].title == "The Hobbit"
assert books[1].title == "The Silmarillion"

Sapkovsky books before 2000 or Tolkien books after 1960

sql: WHERE ( ( books.year > 1960 AND authors.name = 'J.R.R. Tolkien' ) OR ( books.year < 2000 AND authors.name = 'Andrzej Sapkowski' ) )

Django style

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
books = (
    await Book.objects.select_related("author")
    .filter(
        ormar.or_(
            ormar.and_(year__gt=1960, author__name="J.R.R. Tolkien"),
            ormar.and_(year__lt=2000, author__name="Andrzej Sapkowski"),
        )
    )
    .all()
)
assert len(books) == 2

Python style

1
2
3
4
5
6
7
8
9
books = (
    await Book.objects.select_related("author")
    .filter(    
        ((Book.year > 1960) & (Book.author.name == "J.R.R. Tolkien")) |
        ((Book.year < 2000) & (Book.author.name == "Andrzej Sapkowski"))
    )
    .all()
)
assert len(books) == 2

Of course, these functions can have more than two conditions, so if we want books that contain "hobbits":

sql: WHERE ( ( books.year > 1960 AND authors.name = 'J.R.R. Tolkien' ) OR ( books.year < 2000 AND os0cec_authors.name = 'Andrzej Sapkowski' ) OR books.title LIKE '%hobbit%' )

Django style

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
books = (
    await Book.objects.select_related("author")
    .filter(
        ormar.or_(
            ormar.and_(year__gt=1960, author__name="J.R.R. Tolkien"),
            ormar.and_(year__lt=2000, author__name="Andrzej Sapkowski"),
            title__icontains="hobbit",
        )
    )
    .all()
)

Python style

1
2
3
4
5
6
7
8
9
books = (
    await Book.objects.select_related("author")
    .filter(    
        ((Book.year > 1960) & (Book.author.name == "J.R.R. Tolkien")) |
        ((Book.year < 2000) & (Book.author.name == "Andrzej Sapkowski")) |
        (Book.title.icontains("hobbit"))
    )
    .all()
)

If you want or need to, you can nest conditions deeper as needed, for example by implementing the following query:

sql:

1
2
3
WHERE ( ( ( books.year > 1960 OR books.year < 1940 ) 
AND authors.name = 'J.R.R. Tolkien' ) OR 
( books.year < 2000 AND authors.name = 'Andrzej Sapkowski' ) )

You can structure queries as follows:

Django style

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
books = (
    await Book.objects.select_related("author")
    .filter(
        ormar.or_(
            ormar.and_(
                ormar.or_(year__gt=1960, year__lt=1940),
                author__name="J.R.R. Tolkien",
            ),
            ormar.and_(year__lt=2000, author__name="Andrzej Sapkowski"),
        )
    )
    .all()
)
assert len(books) == 3
assert books[0].title == "The Hobbit"
assert books[1].title == "The Silmarillion"
assert books[2].title == "The Witcher"

Python style

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
books = (
    await Book.objects.select_related("author")
    .filter(        
        (
            (
            (Book.year > 1960) |
            (Book.year < 1940)
            ) &
            (Book.author.name == "J.R.R. Tolkien")
        ) |
        (
            (Book.year < 2000) & 
            (Book.author.name == "Andrzej Sapkowski")
        )
    )
    .all()
)
assert len(books) == 3
assert books[0].title == "The Hobbit"
assert books[1].title == "The Silmarillion"
assert books[2].title == "The Witcher"

You should now understand how ormar.or_and ormar.and_work. Of course, you can link them to any other method of query set, so in the example, a fully valid query might look like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
books = (
    await Book.objects.select_related("author")
    .filter(ormar.or_(year__gt=1980, author__name="Andrzej Sapkowski"))
    .filter(title__startswith="The")
    .limit(1)
    .offset(1)
    .order_by("-id")
    .all()
)
assert len(books) == 1
assert books[0].title == "The Witcher"

The same applies to python-style linking and nesting.

Django style

Note that with django style, you cannot supply the same keyword argument multiple times, so the query filter(ormar.or_(name='Jack', name='John')) below is not allowed. If you want to check multiple values of the same column, just use the in operator:filter(name__in=['Jack','John']).

If you pass only one argument to the or_or and_function, it is only enclosed in parentheses and has no effect on the actual query, so all three queries end up being the same:

1
2
3
await Book.objects.filter(title='The Hobbit').get()
await Book.objects.filter(ormar.or_(title='The Hobbit')).get()
await Book.objects.filter(ormar.and_(title='The Hobbit')).get()

!!!note Note or_and_queries will have WHERE (title ='The Hobbit') but the brackets are redundant and have no practical effect.

If you really need to use the same field name twice, you can use this feature. Remember that you cannot pass the same keyword argument to a function twice, so how do you query WHERE (authors.name LIKE '%tolkien%') OR (authors.name LIKE '%sapkowski%')) in the example?

You can't do this:

1
2
3
4
5
6
7
8
books = (
    await Book.objects.select_related("author")
        .filter(ormar.or_(
        author__name__icontains="tolkien",
        author__name__icontains="sapkowski" # you cannot use same keyword twice in or_!
    ))                                      # python syntax error
        .all()
)

But you can do this:

1
2
3
4
5
6
7
8
9
books = (
    await Book.objects.select_related("author")
        .filter(ormar.or_(
        ormar.and_(author__name__icontains="tolkien"), # one argument == just wrapped in ()
        ormar.and_(author__name__icontains="sapkowski")
    ))
        .all()
)
assert len(books) == 5

Python style

Note that with python style, you can use the same field as many times as you want.

1
2
3
4
5
6
7
8
books = (
    await Book.objects.select_related("author")
        .filter(
        (Book.author.name.icontains("tolkien")) |
        (Book.author.name.icontains("sapkowski"))
    ))                                      
        .all()
)

get

get(*args, **kwargs) -> Model

Get the first row from the database that satisfies the kwargs setting criteria.

When passing any args and/or kwargs, it is the equivalent of a shortcut to calling filter(*args, **kwargs).get()

!!!tip To learn more about filters, go to Filters.

1
To read more about `get` go to [read/get](../read/#get)

Get or None

Exactly the same as get described above, but returns None if no database record matching the condition is found instead of throwing an exception.

acquiring or creating

get_or_create(_defaults: Optional[Dict[str, Any]] = None, *args, **kwargs) -> Tuple[Model, bool]

A combination of create and get methods.

When passing any args and/or kwargs, it is the shortcut equivalent to calling filter(*args, **kwargs).get_or_create()

!!!tip To learn more about filters, go to Filters.

1
To read more about `get_or_create` go to [read/get_or_create](../read/#get_or_create)

!!!warning When a given item does not exist, you need to pass kwargs for all required fields of the model, including but not limited to the Primary_key column (unless it is autoincrement).

all

all(*args, **kwargs) -> List[Optional["Model"]]

For filter options set, returns all rows in the database for a given model.

When passing any kwargs, it is the equivalent of a shortcut to calling filter(*args, **kwargs).all()

!!!tip To learn more about filters, go to Filters.

1
To read more about `all` go to [read/all](../read/#all)

QuerysetProxy method

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

But at the same time it exposes a subset of the QuerySet API, so you can filter, create, select related models, etc. directly from the parent model.

screen

Same filtering functionality as above, but allows you to filter related objects from the other end of the relationship.

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

exclude

Exactly the same exclusion functionality as above, but allows you to filter related objects from the other side of the relationship.

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

get

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

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

Get or None

Exactly the same as get described above, but returns None if no database record matching the condition is found instead of throwing an exception.

acquiring or creating

Identical to the get_or_create function above, but allows you to filter related objects from the other end of the relationship.

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

all

All the same functionality as above, but allows you to filter related objects from the other side of the relationship.

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

sort

Order Basis

order_by(columns: Union[List, str, OrderAction]) -> QuerySet

Using order_by(), you can sort the results in the database based on the fields you select.

You can provide strings with field names or lists of strings with different fields.

Sorts in sql are applied in the order of the names you provide in order_by.

!!!tip By default, if you do not provide sorting or explicitly sort by all primary keys

!!!Warning If you sort by nested models, result rows will not be sorted by main model, otherwise mar will merge these child rows into one main model.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
Sample raw database rows result (sort by child model desc):
```
MODEL: 1 - Child Model - 3
MODEL: 2 - Child Model - 2
MODEL: 1 - Child Model - 1
```

will result in 2 rows of result:
```
MODEL: 1 - Child Models: [3, 1] # encountered first in result, all children rows combined
MODEL: 2 - Child Models: [2]
```

The main model will never duplicate in the result

Given an example model:

 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
44
45
46
import asyncio

import databases
import ormar
import sqlalchemy
from examples import create_drop_database

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

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


class Owner(ormar.Model):
    ormar_config = ormar_base_config.copy(tablename="owners")

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


class Toy(ormar.Model):
    ormar_config = ormar_base_config.copy(tablename="toys")

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


@create_drop_database(base_config=ormar_base_config)
async def run_query():
    # build some sample data
    aphrodite = await Owner.objects.create(name="Aphrodite")
    hermes = await Owner.objects.create(name="Hermes")
    zeus = await Owner.objects.create(name="Zeus")

    await Toy.objects.create(name="Toy 4", owner=zeus)
    await Toy.objects.create(name="Toy 5", owner=hermes)
    await Toy.objects.create(name="Toy 2", owner=aphrodite)
    await Toy.objects.create(name="Toy 1", owner=zeus)
    await Toy.objects.create(name="Toy 3", owner=aphrodite)
    await Toy.objects.create(name="Toy 6", owner=hermes)


asyncio.run(run_query())

To sort by main model field, simply provide the field name

Django style

1
2
3
4
5
6
toys = await Toy.objects.select_related("owner").order_by("name").all()
assert [x.name.replace("Toy ", "") for x in toys] == [
    str(x + 1) for x in range(6)
]
assert toys[0].owner == zeus
assert toys[1].owner == aphrodite

Python style

1
2
3
4
5
6
toys = await Toy.objects.select_related("owner").order_by(Toy.name.asc()).all()
assert [x.name.replace("Toy ", "") for x in toys] == [
    str(x + 1) for x in range(6)
]
assert toys[0].owner == zeus
assert toys[1].owner == aphrodite

To sort nested models, use dunder '__' to separate field names.

This way you can sort all relationship types->ForeignKey, Reverse Virtual FK, and ManyToMany fields.

Django style

1
2
3
4
toys = await Toy.objects.select_related("owner").order_by("owner__name").all()
assert toys[0].owner.name == toys[1].owner.name == "Aphrodite"
assert toys[2].owner.name == toys[3].owner.name == "Hermes"
assert toys[4].owner.name == toys[5].owner.name == "Zeus"

Python style

1
2
3
4
toys = await Toy.objects.select_related("owner").order_by(Toy.owner.name.asc()).all()
assert toys[0].owner.name == toys[1].owner.name == "Aphrodite"
assert toys[2].owner.name == toys[3].owner.name == "Hermes"
assert toys[4].owner.name == toys[5].owner.name == "Zeus"

To sort in descending order, provide a hyphen before the field name

Django style

1
2
3
4
5
6
7
8
owner = (
    await Owner.objects.select_related("toys")
        .order_by("-toys__name")
        .filter(name="Zeus")
        .get()
)
assert owner.toys[0].name == "Toy 4"
assert owner.toys[1].name == "Toy 1"

Python style

1
2
3
4
5
6
7
8
owner = (
    await Owner.objects.select_related("toys")
        .order_by(Owner.toys.name.desc())
        .filter(Owner.name == "Zeus")
        .get()
)
assert owner.toys[0].name == "Toy 4"
assert owner.toys[1].name == "Toy 1"

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()

Default sorting in ormar

Since there is no guarantee of the order of rows in the database, ormar always issues an order by sql clause to each (partial) query, even if you do not supply the order yourself.

By default, when querying a database with a given model, the models are sorted in ascending order by the Primary_key column. If you want to change the default behavior, you can do so by supplying the orders_by parameter to ormar_config.

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

By default, relationships follow the same order, but 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.

!!! Tip To read more about model sort order, visit the relationships section of the document

The order in which the order_by clause is applied is as follows:

  • Explicitly passing the order_by() call in the query
  • Relationships through orders_by and related_orders_by (if any)
  • ormar_config object orders_by for model
  • The model's primary_key column is in ascending order (backup, used if none of the above is provided)

Apply orders from only one source to each model (so that you can always override it in a single query).

This means that if you provide an explicit order_by for the model in the query, relationships and model sort order are skipped.

If you provide relationship one, model sorting is skipped.

Finally, if you provide one for the Model, the default value for primary_key is skipped.

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.

Order Basis

Exactly the same as the order_by function above, but allows you to sort related objects from the other side of the relationship.

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