aggregate function
Currently 6 aggregate functions are supported.
count
count(distinct: bool = True) -> int
Returns the number of rows matching a given condition (i.e. filters and exclusions applied). If distinct is True (default), this returns the selected number of primary rows. If False, the count will be the total number of rows returned (including extra rows for one-to-many or many-to-many left select_lated table joins). False is the legacy (defective) behavior of the workflow that depends on it.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 | class Book(ormar.Model):
ormar_config = ormar.OrmarConfig(
database=databases.Database(DATABASE_URL),
metadata=sqlalchemy.MetaData(),
tablename="book"
)
id: int = ormar.Integer(primary_key=True)
title: str = ormar.String(max_length=200)
author: str = ormar.String(max_length=100)
genre: str = ormar.String(
max_length=100,
default="Fiction",
choices=["Fiction", "Adventure", "Historic", "Fantasy"],
)
# returns count of rows in db for Books model
no_of_books = await Book.objects.count()
|
exist
Existence () -> Boolean
Returns a Boolean value to confirm that there are rows that match the given criteria (applied with filters and exclusions)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 | class Book(ormar.Model):
ormar_config = ormar.OrmarConfig(
database=databases.Database(DATABASE_URL),
metadata=sqlalchemy.MetaData(),
tablename="book"
)
id: int = ormar.Integer(primary_key=True)
title: str = ormar.String(max_length=200)
author: str = ormar.String(max_length=100)
genre: str = ormar.String(
max_length=100,
default="Fiction",
choices=["Fiction", "Adventure", "Historic", "Fantasy"],
)
# returns a boolean value if given row exists
has_sample = await Book.objects.filter(title='Sample').exists()
|
sum
Sum (Column)-> Arbitrary
Returns the sum of the columns of rows that match a given condition (applies filters, excludes if previously set).
You can pass one or more column names, including related columns.
So far, each column passed is aggregated separately (so sum(col1+col2) is impossible, you can use sum(col1, col2) and add the sum of the 2 returns in python)
You cannot sum non-numeric columns.
If aggregating one column, returns a single value directly as the result If aggregating multiple columns, returns a dictionary containing column: result pairs
The given model is 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
32
33
34
35 | from typing import Optional
import databases
import ormar
import sqlalchemy
from tests.settings import DATABASE_URL
database = databases.Database(DATABASE_URL)
metadata = sqlalchemy.MetaData()
base_ormar_config = ormar.OrmarConfig(
metadata=metadata,
database=database,
)
class Author(ormar.Model):
ormar_config = base_ormar_config.copy(tablename="authors", order_by=["-name"])
id: int = ormar.Integer(primary_key=True)
name: str = ormar.String(max_length=100)
class Book(ormar.Model):
ormar_config = base_ormar_config.copy(
tablename="books", order_by=["year", "-ranking"]
)
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)
ranking: int = ormar.Integer(nullable=True)
|
An example usage might be 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 | author = await Author(name="Author 1").save()
await Book(title="Book 1", year=1920, ranking=3, author=author).save()
await Book(title="Book 2", year=1930, ranking=1, author=author).save()
await Book(title="Book 3", year=1923, ranking=5, author=author).save()
assert await Book.objects.sum("year") == 5773
result = await Book.objects.sum(["year", "ranking"])
assert result == dict(year=5773, ranking=9)
try:
# cannot sum string column
await Book.objects.sum("title")
except ormar.QueryDefinitionError:
pass
assert await Author.objects.select_related("books").sum("books__year") == 5773
result = await Author.objects.select_related("books").sum(
["books__year", "books__ranking"]
)
assert result == dict(books__year=5773, books__ranking=9)
assert (
await Author.objects.select_related("books")
.filter(books__year__lt=1925)
.sum("books__year")
== 3843
)
|
average
Average (Column)-> Arbitrary
Returns the average of columns of rows matching a given condition (filters applied, excluded if previously set).
You can pass one or more column names, including related columns.
So far, each column passed is aggregated separately (so sum(col1+col2) is impossible, you can use sum(col1, col2) and add the sum of the 2 returns in python)
You cannot average non-numeric columns.
If aggregating one column, returns a single value directly as the result If aggregating multiple columns, returns a dictionary containing column: result pairs
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 | from typing import Optional
import databases
import ormar
import sqlalchemy
from tests.settings import DATABASE_URL
database = databases.Database(DATABASE_URL)
metadata = sqlalchemy.MetaData()
base_ormar_config = ormar.OrmarConfig(
metadata=metadata,
database=database,
)
class Author(ormar.Model):
ormar_config = base_ormar_config.copy(tablename="authors", order_by=["-name"])
id: int = ormar.Integer(primary_key=True)
name: str = ormar.String(max_length=100)
class Book(ormar.Model):
ormar_config = base_ormar_config.copy(
tablename="books", order_by=["year", "-ranking"]
)
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)
ranking: int = ormar.Integer(nullable=True)
|
An example usage might be 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 | author = await Author(name="Author 1").save()
await Book(title="Book 1", year=1920, ranking=3, author=author).save()
await Book(title="Book 2", year=1930, ranking=1, author=author).save()
await Book(title="Book 3", year=1923, ranking=5, author=author).save()
assert round(float(await Book.objects.avg("year")), 2) == 1924.33
result = await Book.objects.avg(["year", "ranking"])
assert round(float(result.get("year")), 2) == 1924.33
assert result.get("ranking") == 3.0
try:
# cannot avg string column
await Book.objects.avg("title")
except ormar.QueryDefinitionError:
pass
result = await Author.objects.select_related("books").avg("books__year")
assert round(float(result), 2) == 1924.33
result = await Author.objects.select_related("books").avg(
["books__year", "books__ranking"]
)
assert round(float(result.get("books__year")), 2) == 1924.33
assert result.get("books__ranking") == 3.0
assert (
await Author.objects.select_related("books")
.filter(books__year__lt=1925)
.avg("books__year")
== 1921.5
)
|
minute
Minutes (column)-> Any
Returns the minimum value of the column of rows matching the given criteria (applied with the filter, excluded if previously set).
You can pass one or more column names, including related columns.
So far, each column passed is aggregated separately (so sum(col1+col2) is impossible, you can use sum(col1, col2) and add the sum of the 2 returns in python)
If aggregating one column, returns a single value directly as the result If aggregating multiple columns, returns a dictionary containing column: result pairs
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 | from typing import Optional
import databases
import ormar
import sqlalchemy
from tests.settings import DATABASE_URL
database = databases.Database(DATABASE_URL)
metadata = sqlalchemy.MetaData()
base_ormar_config = ormar.OrmarConfig(
metadata=metadata,
database=database,
)
class Author(ormar.Model):
ormar_config = base_ormar_config.copy(tablename="authors", order_by=["-name"])
id: int = ormar.Integer(primary_key=True)
name: str = ormar.String(max_length=100)
class Book(ormar.Model):
ormar_config = base_ormar_config.copy(
tablename="books", order_by=["year", "-ranking"]
)
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)
ranking: int = ormar.Integer(nullable=True)
|
An example usage might be 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 | author = await Author(name="Author 1").save()
await Book(title="Book 1", year=1920, ranking=3, author=author).save()
await Book(title="Book 2", year=1930, ranking=1, author=author).save()
await Book(title="Book 3", year=1923, ranking=5, author=author).save()
assert await Book.objects.min("year") == 1920
result = await Book.objects.min(["year", "ranking"])
assert result == dict(year=1920, ranking=1)
assert await Book.objects.min("title") == "Book 1"
assert await Author.objects.select_related("books").min("books__year") == 1920
result = await Author.objects.select_related("books").min(
["books__year", "books__ranking"]
)
assert result == dict(books__year=1920, books__ranking=1)
assert (
await Author.objects.select_related("books")
.filter(books__year__gt=1925)
.min("books__year")
== 1930
)
|
high limit
Max (Column)-> Arbitrary
Returns the maximum value of a column of rows matching a given condition (applied with the filter, excluded if previously set).
Returns the minimum value of the column of rows matching the given criteria (applied with the filter, excluded if previously set).
You can pass one or more column names, including related columns.
So far, each column passed is aggregated separately (so sum(col1+col2) is impossible, you can use sum(col1, col2) and add the sum of the 2 returns in python)
If aggregating one column, returns a single value directly as the result If aggregating multiple columns, returns a dictionary containing column: result pairs
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 | from typing import Optional
import databases
import ormar
import sqlalchemy
from tests.settings import DATABASE_URL
database = databases.Database(DATABASE_URL)
metadata = sqlalchemy.MetaData()
base_ormar_config = ormar.OrmarConfig(
metadata=metadata,
database=database,
)
class Author(ormar.Model):
ormar_config = base_ormar_config.copy(tablename="authors", order_by=["-name"])
id: int = ormar.Integer(primary_key=True)
name: str = ormar.String(max_length=100)
class Book(ormar.Model):
ormar_config = base_ormar_config.copy(
tablename="books", order_by=["year", "-ranking"]
)
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)
ranking: int = ormar.Integer(nullable=True)
|
An example usage might be 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 | author = await Author(name="Author 1").save()
await Book(title="Book 1", year=1920, ranking=3, author=author).save()
await Book(title="Book 2", year=1930, ranking=1, author=author).save()
await Book(title="Book 3", year=1923, ranking=5, author=author).save()
assert await Book.objects.max("year") == 1930
result = await Book.objects.max(["year", "ranking"])
assert result == dict(year=1930, ranking=5)
assert await Book.objects.max("title") == "Book 3"
assert await Author.objects.select_related("books").max("books__year") == 1930
result = await Author.objects.select_related("books").max(
["books__year", "books__ranking"]
)
assert result == dict(books__year=1930, books__ranking=5)
assert (
await Author.objects.select_related("books")
.filter(books__year__lt=1925)
.max("books__year")
== 1923
)
|
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.
count
Exactly the same as the count function above, but allows you to select columns from related objects at the other end of the relationship.
!!!tip To learn more about QuerysetProxy, visit the QuerysetProxy section
exist
Identical to the existence function above, but allows you to select columns from related objects at the other end of the relationship.
sum
Identical to the sum function above, but allows you to sum columns of related objects at the other end of the relationship.
average
Identical to the avg function above, but allows you to average columns of related objects at the other end of the relationship.
minute
Identical to the min function above, but allows you to select the fewest columns from related objects at the other end of the relationship.
high limit
Identical to the max function above, but allows you to select the largest column from related objects at the other end of the relationship.
!!!tip To learn more about QuerysetProxy, visit the QuerysetProxy section