Skip to content

# Querying

## Filtering

ODMantic uses QueryExpression objects to handle filter expressions. These expressions can be built from the comparison operators. It's then possible to combine multiple expressions using the logical operators. To support the wide variety of operators provided by MongoDB, it's possible as well to define the filter 'manually'.

### Comparison operators

There are multiple ways of building QueryExpression objects with comparisons operators:

1. Using python comparison operators between the field of the model and the desired value
     `==`, `!=`, `<=`, `<`, `>=`, `>`

2. Using the functions provided by the `odmantic.query` module

   - query.eq
   - query.ne
   - query.gt
   - query.gte
   - query.lt
   - query.lte
   - query.in_
   - query.not_in

3. Using methods of the model's field and the desired value

   - `field.eq`
   - `field.ne`
   - `field.gte`
   - `field.gt`
   - `field.lte`
   - `field.lte`
   - `field.in_`
   - `field.not_in`

Type checkers

Since there is currently not any type checker plugin, the third usage might create some errors with type checkers.

#### Equal

Filter the trees named "Spruce":

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14

    from odmantic import Model, query

    class Tree(Model):
        name: str
        average_size: float

    Tree.name == "Spruce"
    #> QueryExpression({'name': {'$eq': 'Spruce'}})
    Tree.name.eq("Spruce")
    #> QueryExpression({'name': {'$eq': 'Spruce'}})
    query.eq(Tree.name, "Spruce")
    #> QueryExpression({'name': {'$eq': 'Spruce'}})

Equivalent raw MongoDB filter:

    {"name": "Spruce"}

Using equality operators with Enum fields

Building filters using `Enum` fields is possible as well.

Example of filter built on an Enum field

Filter the 'small' trees:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22

    from enum import Enum

    from odmantic import Model, query

    class TreeKind(str, Enum):
        BIG = "big"
        SMALL = "small"

    class Tree(Model):
        name: str
        average_size: float
        kind: TreeKind

    Tree.kind == TreeKind.SMALL
    #> QueryExpression({'kind': {'$eq': 'small'}})
    Tree.kind.eq(TreeKind.SMALL)
    #> QueryExpression({'kind': {'$eq': 'small'}})
    query.eq(Tree.kind, TreeKind.SMALL)
    #> QueryExpression({'kind': {'$eq': 'small'}})

Equivalent raw MongoDB filter:

    {'kind': 'small'}

More details about Enum fields.

#### Not Equal

Filter the trees that are not named "Spruce":

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14

    from odmantic import Model, query

    class Tree(Model):
        name: str
        average_size: float

    Tree.name != "Spruce"
    #> QueryExpression({'name': {'$ne': 'Spruce'}})
    Tree.name.ne("Spruce")
    #> QueryExpression({'name': {'$ne': 'Spruce'}})
    query.ne(Tree.name, "Spruce")
    #> QueryExpression({'name': {'$ne': 'Spruce'}})

Equivalent raw MongoDB filter:

    {"name": {"$ne": "Spruce"}}

#### Less than (or equal to)

Filter the trees that have a size that is less than (or equal to) 2:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21

    from odmantic import Model, query

    class Tree(Model):
        name: str
        average_size: float

    Tree.average_size < 2
    #> QueryExpression({'average_size': {'$lt': 2}})
    Tree.average_size.lt(2)
    #> QueryExpression({'average_size': {'$lt': 2}})
    query.lt(Tree.average_size, 2)
    #> QueryExpression({'average_size': {'$lt': 2}})

    Tree.average_size <= 2
    #> QueryExpression({'average_size': {'$lte': 2}})
    Tree.average_size.lte(2)
    #> QueryExpression({'average_size': {'$lte': 2}})
    query.lte(Tree.average_size, 2)
    #> QueryExpression({'average_size': {'$lte': 2}})

Equivalent raw MongoDB filter (less than):

    {"average_size": {"$lt": 2}}

Equivalent raw MongoDB filter (less than or equal to):

    {"average_size": {"$lte": 2}}

#### Greater than (or equal to)

Filter the trees having a size that is greater than (or equal to) 2:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21

    from odmantic import Model, query

    class Tree(Model):
        name: str
        average_size: float

    Tree.average_size > 2
    #> QueryExpression({'average_size': {'$gt': 2}})
    Tree.average_size.gt(2)
    #> QueryExpression({'average_size': {'$gt': 2}})
    query.gt(Tree.average_size, 2)
    #> QueryExpression({'average_size': {'$gt': 2}})

    Tree.average_size >= 2
    #> QueryExpression({'average_size': {'$gte': 2}})
    Tree.average_size.gte(2)
    #> QueryExpression({'average_size': {'$gte': 2}})
    query.gte(Tree.average_size, 2)
    #> QueryExpression({'average_size': {'$gte': 2}})

Equivalent raw MongoDB filter (greater than):

    {"average_size": {"$gt": 2}}

Equivalent raw MongoDB filter (greater than or equal to):

    {"average_size": {"$gte": 2}}

#### Included in

Filter the trees named either "Spruce" or "Pine":

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12

    from odmantic import Model, query

    class Tree(Model):
        name: str
        average_size: float

    Tree.name.in_(["Spruce", "Pine"])
    #> QueryExpression({'name': {'$in': ['Spruce', 'Pine']}})
    query.in_(Tree.name, ["Spruce", "Pine"])
    #> QueryExpression({'name': {'$in': ['Spruce', 'Pine']}})

Equivalent raw MongoDB filter:

    {"name": {"$in": ["Spruce", "Pine"]}}

#### Not included in

Filter the trees neither named "Spruce" nor "Pine":

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12

    from odmantic import Model, query

    class Tree(Model):
        name: str
        average_size: float

    Tree.name.not_in(["Spruce", "Pine"])
    #> QueryExpression({'name': {'$nin': ['Spruce', 'Pine']}})
    query.not_in(Tree.name, ["Spruce", "Pine"])
    #> QueryExpression({'name': {'$nin': ['Spruce', 'Pine']}})

Equivalent raw MongoDB filter:

    {"name": {"$nin": ["Spruce", "Pine"]}}

### Evaluation operators

#### Match (Regex)

Filter the trees with a name starting with 'Spruce':

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11

    from odmantic import Model, query

    class Tree(Model):
        name: str

    Tree.name.match(r"^Spruce")
    #> QueryExpression({'name': re.compile('^Spruce')})
    query.match(Tree.name, r"^Spruce")
    #> QueryExpression({'name': re.compile('^Spruce')})

Equivalent raw MongoDB filter:

    {"name": {"$regex": "^Spruce"}}

### Logical operators

There are two ways of combining QueryExpression objects with logical operators:

1. Using python 'bitwise' operators between the field of the model and the desired value
     `&`, `|`

Warning

When using those operators make sure to correctly bracket the expressions to avoid python operator precedence issues.

1. Using the functions provided by the `odmantic.query` module
   - query.and_
   - query.or_
   - query.nor_

#### And

Filter the trees named Spruce (AND) with a size less than 2:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19

    from odmantic import Model, query

    class Tree(Model):
        name: str
        size: float

    (Tree.name == "Spruce") & (Tree.size <= 2)
    #> QueryExpression(
    #>     {
    #>         "$and": (
    #>             QueryExpression({"name": {"$eq": "Spruce"}}),
    #>             QueryExpression({"size": {"$lte": 2}}),
    #>         )
    #>     }
    #> )
    query.and_(Tree.name == "Spruce", Tree.size <= 2)
    #> ... same output ...

Equivalent raw MongoDB filter:

    {"name": "Spruce", "size": {"$lte": 2}}}

Implicit AND

When using find, find_one or count, you can specify multiple queries as positional arguments and those will be implicitly combined with the `AND` operator.

#### Or

Filter the trees named Spruce OR the trees with a size greater than 2:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19

    from odmantic import Model, query

    class Tree(Model):
        name: str
        size: float

    (Tree.name == "Spruce") | (Tree.size > 2)
    #> QueryExpression(
    #>     {
    #>         "$or": (
    #>             QueryExpression({"name": {"$eq": "Spruce"}}),
    #>             QueryExpression({"size": {"$gt": 2}}),
    #>         )
    #>     }
    #> )
    query.or_(Tree.name == "Spruce", Tree.size > 2)
    #> ... same output ...

Equivalent raw MongoDB filter:

    {
      "$or":[
        {"name":"Spruce"},
        {"size":{"$gt":2}}
      ]
    }

#### Nor

Filter the trees neither named Spruce NOR bigger than 2 (size):

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17

    from odmantic import Model, query

    class Tree(Model):
        name: str
        size: float

    query.nor_(Tree.name == "Spruce", Tree.size > 2)
    #> QueryExpression(
    #>     {
    #>         "$nor": (
    #>             QueryExpression({"name": {"$eq": "Spruce"}}),
    #>             QueryExpression({"size": {"$gt": 2}}),
    #>         )
    #>     }
    #> )

Equivalent raw MongoDB filter:

    {
      "$nor":[
        {"name":"Spruce"},
        {"size":{"$gt":2}}
      ]
    }

NOR Equivalence

The following logical expressions are equivalent:

- A NOR B NOR C
- NOT(A OR B OR C)
- NOT(A) AND NOT(B) AND NOT(C)

`query.nor_` operator naming

query.and_ and query.or_ require to add an extra underscore to avoid overlapping with the python keywords. While it could've been possible to name the NOR operator query.nor, the extra underscore has been kept for consistency in the naming of the logical operators.

### Embedded documents filters

It's possible to build filter based on the content of embedded documents:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18

    from odmantic import AIOEngine, EmbeddedModel, Model

    class CapitalCity(EmbeddedModel):
        name: str
        population: int

    class Country(Model):
        name: str
        currency: str
        capital_city: CapitalCity

    Country.capital_city.name == "Paris"
    #> QueryExpression({'capital_city.name': {'$eq': 'Paris'}})
    Country.capital_city.population > 10 ** 6
    #> QueryExpression({'capital_city.population': {'$gt': 1000000}})

Equivalent raw MongoDB filters:

    {"capital_city.name": {"$eq": "Paris"}}

    {"capital_city.population": {"$gt": 1000000}}

Filtering across References

Currently, it is not possible to build filter based on referenced objects.

### Raw MongoDB filters

Any QueryExpression can be replaced with raw MongoDB filters. Thus, it's completely possible to use traditional filters with the find, find_one or count methods.

You can find more details about building raw query filters using the Model in the Raw query usage section.

## Sorting

ODMantic uses SortExpression objects to handle sort expressions.

There are multiple ways of building SortExpression objects:

1. Using implicit `Model` fields:

   Ascending sort

   To sort `Publisher` instances by ascending `Publisher.founded`:

       await engine.find(Publisher, sort=Publisher.founded)

   This example refers to the code showcased in the Overview.

2. Using the functions provided by the `odmantic.query` module

   - query.asc
   - query.desc

3. Using methods of the model's field and the desired value

   - `field.asc`
   - `field.desc`

Type checkers

Since there is currently not any type checker plugin, the third usage might create some errors with type checkers.

### Ascending

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16

    from odmantic import AIOEngine, Model, query

    engine = AIOEngine()

    class Tree(Model):
        name: str
        average_size: float

    # The following queries are equivalent,
    # they will sort `Tree` by ascending `average_size`

    await engine.find(Tree, sort=Tree.average_size)
    await engine.find(Tree, sort=Tree.average_size.asc())
    await engine.find(Tree, sort=query.asc(Tree.average_size))

### Descending

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15

    from odmantic import AIOEngine, Model, query

    engine = AIOEngine()

    class Tree(Model):
        name: str
        average_size: float

    # The following queries are equivalent,
    # they will sort `Tree` by descending `average_size`

    await engine.find(Tree, sort=Tree.average_size.desc())
    await engine.find(Tree, sort=query.desc(Tree.average_size))

### Sort on multiple fields

We can pass a `tuple` to the `sort` kwarg, this will enable us to make a more complex sort query:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14

    from odmantic import AIOEngine, Model, query

    engine = AIOEngine()

    class Tree(Model):
        name: str
        average_size: float

    # This query will first sort on ascending `average_size`, then
    # on descending `name` when `average_size` is the same

    await engine.find(Tree, sort=(Tree.average_size, Tree.name.desc()))

### Embedded model field as a sort key

We can sort instances based on the content of their embedded models.

Sorting by an embedded model field

We can sort the countries by descending order of the population of their capital city:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17

    from odmantic import AIOEngine, EmbeddedModel, Model
    from odmantic.query import desc

    class CapitalCity(EmbeddedModel):
        name: str
        population: int

    class Country(Model):
        name: str
        currency: str
        capital_city: CapitalCity

    engine = AIOEngine()
    await engine.find(Country, sort=desc(Country.capital_city.population))