Select a subset of columns

To select only selected columns in the model, you can use the following function.

• fields(columns: Union[List, str, set, dict]) -> QuerySet

• exclude_fields(columns: Union[List, str, set, dict]) -> QuerySet

• query set proxy

  • QuerysetProxy.fields(columns: Union[List, str, set, dict]) method
  • QuerysetProxy.exclude_fields(columns: Union[List, str, set, dict]) method

territory

fields(columns: Union[List, str, set, dict]) -> QuerySet

With fields(), you can select a subset of model columns to limit the data load.

Note that fields() and except_fields() apply both to the main model (on normal queries such as get, all, etc.) and to select_lated and prefetch_lated models (using nested notation).

The following example data is given:

import asyncio

import databases
import ormar
import sqlalchemy
from examples import create_drop_database
from tests.settings import DATABASE_URL

base_ormar_config = ormar.OrmarConfig(
    database=databases.Database(DATABASE_URL, force_rollback=True),
    metadata=sqlalchemy.MetaData(),
)


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)
    founded: int = ormar.Integer(nullable=True)


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

    id: int = ormar.Integer(primary_key=True)
    manufacturer = ormar.ForeignKey(Company)
    name: str = ormar.String(max_length=100)
    year: int = ormar.Integer(nullable=True)
    gearbox_type: str = ormar.String(max_length=20, nullable=True)
    gears: int = ormar.Integer(nullable=True)
    aircon_type: str = ormar.String(max_length=20, nullable=True)


@create_drop_database(base_config=base_ormar_config)
async def sample_data():
    # build some sample data
    toyota = await Company.objects.create(name="Toyota", founded=1937)
    await Car.objects.create(
        manufacturer=toyota,
        name="Corolla",
        year=2020,
        gearbox_type="Manual",
        gears=5,
        aircon_type="Manual",
    )
    await Car.objects.create(
        manufacturer=toyota,
        name="Yaris",
        year=2019,
        gearbox_type="Manual",
        gears=5,
        aircon_type="Manual",
    )
    await Car.objects.create(
        manufacturer=toyota,
        name="Supreme",
        year=2020,
        gearbox_type="Auto",
        gears=6,
        aircon_type="Auto",
    )


asyncio.run(sample_data())

You can select the specified field by passing a str, List[str], Set[str] or dict has nested definitions.

To include correlation models, use the symbol {related_name}__{column}[__{optional_next} etc.].

all_cars = await (
    Car.objects
    .select_related('manufacturer')
    .fields(['id', 'name', 'manufacturer__name'])
    .all()
)
for car in all_cars:
    # excluded columns will yield None
    assert all(getattr(car, x) is None for x in ['year', 'gearbox_type', 'gears', 'aircon_type'])
    # included column on related models will be available, pk column is always included
    # even if you do not include it in fields list
    assert car.manufacturer.name == 'Toyota'
    # also in the nested related models - you cannot exclude pk - it's always auto added
    assert car.manufacturer.founded is None

fields() can be called multiple times to build the columns to be selected.

If you include related models in the select_lated() call, but you don't specify columns in the field for those models

• Means a list of all fields of these nested models.

  all_cars = await ( Car.objects .select_related('manufacturer') .fields('id') .fields(['name']) .all() )

  all fields from company model are selected

  assert all_cars[0].manufacturer.name == 'Toyota' assert all_cars[0].manufacturer.founded == 1937

!!! Warning cannot exclude mandatory fields because it raises ValidationError, fields must be empty to exclude.

The `values()` method can be used to exclude mandatory fields, though data will
be returned as a `dict`.

You cannot exclude the Mandatory Model column-the Manufacturer__Name of the file.

await (
    Car.objects
    .select_related('manufacturer')
    .fields(['id', 'name', 'manufacturer__founded'])
    .all()
)
# will raise pydantic ValidationError as company.name is required

!!!The tip Pk column cannot be excluded-it is always added automatically, even if it is not explicitly included.

You can also pass fields to include as dictionaries or sets.

To mark a field for inclusion in a dictionary, use its name as the key and ellipses as the value.

To traverse nested models, use nested dictionaries.

To include the last level of fields rather than nested dictionaries, you can use collections.

To include the entire nested model, specify the model-related field names and ellipses.

You can see an equivalent example below:

# 1. like in example above
await (
    Car.objects
    .select_related('manufacturer')
    .fields(['id', 'name', 'manufacturer__name'])
    .all()
)

# 2. to mark a field as required use ellipsis
await (
    Car.objects
    .select_related('manufacturer')
    .fields({'id': ...,
             'name': ...,
             'manufacturer': {
                 'name': ...
                }
             })
    .all()
)

# 3. to include whole nested model use ellipsis
await (
    Car.objects
    .select_related('manufacturer')
    .fields({'id': ...,
             'name': ...,
             'manufacturer': ...
             })
    .all()
)

# 4. to specify fields at last nesting level 
# you can also use set - equivalent to 2. above
await (
    Car.objects
    .select_related('manufacturer')
    .fields({'id': ...,
             'name': ...,
             'manufacturer': {'name'}
             })
    .all()
)

# 5. of course set can have multiple fields
await (
    Car.objects
    .select_related('manufacturer')
    .fields({'id': ...,
             'name': ...,
             'manufacturer': {'name', 'founded'}
             })
    .all()
)

# 6. you can include all nested fields, 
# but it will be equivalent of 3. above which is shorter
await (
    Car.objects
    .select_related('manufacturer')
    .fields({'id': ...,
             'name': ...,
             'manufacturer': {'id', 'name', 'founded'}
             })
    .all()
)

Note

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

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

exclusion field

exclude_fields(columns: Union[List, str, set, dict]) -> QuerySet

With except_fields(), you can select a subset of model columns to exclude to limit data loading.

It is the opposite of the fields() method, so check the documentation above for options available.

In particular, examine how nested dictionaries and collections are passed as masks above to exclude fields from the entire hierarchy.

Note that fields() and except_fields() apply both to the main model (on normal queries such as get, all, etc.) and to select_lated and prefetch_lated models (using nested notation).

You can find some simple examples below:

import asyncio

import databases
import ormar
import sqlalchemy
from examples import create_drop_database
from tests.settings import DATABASE_URL

base_ormar_config = ormar.OrmarConfig(
    database=databases.Database(DATABASE_URL, force_rollback=True),
    metadata=sqlalchemy.MetaData(),
)


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)
    founded: int = ormar.Integer(nullable=True)


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

    id: int = ormar.Integer(primary_key=True)
    manufacturer = ormar.ForeignKey(Company)
    name: str = ormar.String(max_length=100)
    year: int = ormar.Integer(nullable=True)
    gearbox_type: str = ormar.String(max_length=20, nullable=True)
    gears: int = ormar.Integer(nullable=True)
    aircon_type: str = ormar.String(max_length=20, nullable=True)


@create_drop_database(base_config=base_ormar_config)
async def sample_data():
    # build some sample data
    toyota = await Company.objects.create(name="Toyota", founded=1937)
    await Car.objects.create(
        manufacturer=toyota,
        name="Corolla",
        year=2020,
        gearbox_type="Manual",
        gears=5,
        aircon_type="Manual",
    )
    await Car.objects.create(
        manufacturer=toyota,
        name="Yaris",
        year=2019,
        gearbox_type="Manual",
        gears=5,
        aircon_type="Manual",
    )
    await Car.objects.create(
        manufacturer=toyota,
        name="Supreme",
        year=2020,
        gearbox_type="Auto",
        gears=6,
        aircon_type="Auto",
    )


asyncio.run(sample_data())


# select manufacturer but only name,
# to include related models use notation {model_name}__{column}
all_cars = await (
    Car.objects
    .select_related('manufacturer')
    .exclude_fields([
        'year',
        'gearbox_type',
        'gears',
        'aircon_type',
        'company__founded'
    ])
    .all()
)
for car in all_cars:
    # excluded columns will yield None
    assert all(getattr(car, x) is None
               for x in [
                   'year',
                   'gearbox_type',
                   'gears',
                   'aircon_type'
               ])
    # included column on related models will be available,
    # pk column is always included
    # even if you do not include it in fields list
    assert car.manufacturer.name == 'Toyota'
    # also in the nested related models,
    # you cannot exclude pk - it's always auto added
    assert car.manufacturer.founded is None

# fields() can be called several times,
# building up the columns to select
# models included in select_related 
# but with no columns in fields list implies all fields
all_cars = await (
    Car.objects
    .select_related('manufacturer')
    .exclude_fields('year')
    .exclude_fields(['gear', 'gearbox_type'])
    .all()
)
# all fields from company model are selected
assert all_cars[0].manufacturer.name == 'Toyota'
assert all_cars[0].manufacturer.founded == 1937

# cannot exclude mandatory model columns,
# company__name in this example - note usage of dict/set this time
await (
    Car.objects
    .select_related('manufacturer')
    .exclude_fields([{'company': {'name'}}])
    .all()
)
# will raise pydantic ValidationError as company.name is required

!!!Warning Mandatory fields cannot be excluded because they raise ValidationError, which must be nullable to exclude fields.

The `values()` method can be used to exclude mandatory fields, though data will
be returned as a `dict`.

!!! Tip Pk column cannot be excluded-even if explicitly excluded, it is always automatically added.

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

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.

territory

Functionally identical to the fields 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

exclusion field

Identical to the except_fields 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