Fields Lookup¶

Fields lookup is what you put into the filter, exclude, get methods, or into the Q object as keyword arguments.

For example, .filter(age__lt=18) - here the age__lt means:

age is a field of the model
lt is the lookup type, which means less than

If the lookup type is not provided, e.g. .filter(name="John") by default eq lookup type is used.

Common field lookups¶

The “eq” lookup type¶

from models.user.person import Person
from amsdal_utils.query.utils import Q

Person.objects.filter(Q(gender__eq="male"), name__eq="John")

# that is equal to
Person.objects.filter(Q(gender="male"), name="John")

The “neq” lookup type¶

Opposite of the eq:

from models.user.person import Person

# get all persons with gender != male
Person.objects.filter(gender__neq="male")

# get customer that are not admin
Customer.objects.filter(is_admin__neq=True)

The “gt” lookup type¶

Greater than:

from models.user.person import Person

# get all persons with age > 18
Person.objects.filter(age__gt=18)

The “gte” lookup type¶

Greater than or equal to.

from models.user.person import Person

# get all persons with age >= 18
Person.objects.filter(age__gte=18)

The “lt” lookup type¶

Less than.

The “lte” lookup type¶

Less than or equal to.

The “in” lookup type¶

In a given iterable:

from models.user.person import Person

# get all persons with ages either 20, 30 or 40
Person.objects.filter(age__in=[20, 30, 40])

The “contains” lookup type¶

Case-sensitive containment test.

from models.user.person import Person

# find persons whose name contains "har"
Person.objects.filter(name__contains="har")

The query above can return “Charles”, “Charlie”, but will not return “Harry" coz it has the capital “H”.

The “icontains” lookup type¶

Case-insensitive containment test.

from models.user.person import Person

# find persons whose name contains "har"
Person.objects.filter(name__icontains="har")

The query above can return “Charles”, “Charlie”, and “Harry" as well.

The “startswith” lookup type¶

Case-sensitive starts-with.

The “istartswith” lookup type¶

Case-insensitive starts-with.

The “endswith” lookup type¶

Case-sensitive ends-with.

The “iendswith” lookup type¶

Case-insensitive ends-with.

The “isnull” lookup type¶

Takes eitherTrueorFalse, which correspond to SQL queries ofIS NULLandIS NOT NULL, respectively.

Nested fields lookup¶

By default, every nested lookup that is not a common field lookup, is treated as a JSON nested field lookup.

SyncAsync

from models.user.person import Person

Person.objects.filter(json_field__nested_field__eq="value").execute()

from models.user.person import Person

await Person.objects.filter(json_field__nested_field__eq="value").aexecute()

There are two special cases of nested lookup: _metadata and _address.

You can also filter by fields of a referenced object. To do this, first do the select_related and then filter by the field of the referenced object.

SyncAsync

from models.user.person import Person

Person.objects.select_related("company").filter(company__name="value").execute()

from models.user.person import Person

await Person.objects.select_related("company").filter(company__name="value").aexecute()

This also works with multiple levels of select_related:

SyncAsync

from models.user.person import Person

Person.objects.select_related(
    "company",
    "company__location",
).filter(company__location__name="value").execute()

from models.user.person import Person

await Person.objects.select_related(
    "company",
    "company__location",
).filter(company__location__name="value").aexecute()

Metadata¶

Using _metadata field name you can filter by any of Metadata fields. For example, you can filter by is_deleted field:

from models.user.person import Person

# Count the number of deleted persons
Person.objects.filter(_metadata__is_deleted=True).count()

You can combine it with common lookups to build more complex queries:

SyncAsync

from datetime import datetime, timedelta
from models.user.person import Person

# Count the number of persons created during latest 24 hours
start_timestamp = int((datetime.now() - timedelta(hours=24)).timestamp())
result = Person.objects.filter(
    _metadata__created_at__gt=datetime.now() - time
).count().execute()

from datetime import datetime, timedelta
from models.user.person import Person

# Count the number of persons created during latest 24 hours
start_timestamp = int((datetime.now() - timedelta(hours=24)).timestamp())
result = await Person.objects.filter(
    _metadata__created_at__gt=datetime.now() - time
).count().aexecute()

Address¶

Using _address field you can filter objects by:

object_id
object_version
class_version

Note

The _address field will work only if you have historical connection, including lakehouse connection.

For example, to get list of all possible versions of specific person with id 12345, we can build the following QuerySet:

SyncAsync

from amsdal_utils.models.enums import Versions
from models.user.person import Person


result = Person.objects.filter(
    _address__object_id='12345',
    _address__object_version=Versions.ALL,
    _address__class_version=Versions.ALL,
).execute()

from amsdal_utils.models.enums import Versions
from models.user.person import Person


result = await Person.objects.filter(
    _address__object_id='12345',
    _address__object_version=Versions.ALL,
    _address__class_version=Versions.ALL,
).aexecute()

Filtering by objects¶

You can filter by objects using the __eq lookup and object to filter by a pointer to the object:

SyncAsync

from models.user.person import Person
from models.user.location import Location

new_york = Location.objects.get(name="New York").execute()
result = Person.objects.filter(location=new_york).execute()

from models.user.person import Person
from models.user.location import Location

new_york = await Location.objects.get(name="New York").aexecute()
result = await Person.objects.filter(location=new_york).aexecute()

You can also filter by a reference to the object. The equivalent of the previous example is:

SyncAsync

Person.objects.filter(location__eq=new_york.get_metadata().reference).execute()

await Person.objects.filter(location__eq=(await new_york.aget_metadata()).reference).aexecute()