Fetching few fields from a Reverse Relation

[igormorgado]

I would like to retrieve specified fields in a reverse foreign key relation. But when I use select_related into the key it returns Alias for given relation not found.. Something like

await Model.objects.select_related('fkey__column').get(pk=num)

I could get await Model.objects.select_related('fkey').get(pk=num), but If fkey is too big I will get unnecessary data. Is that doable?

Below a snippet to show my issue

from typing import Optional, List
import ormar as orm
import databases
import sqlalchemy


DATABASE_URL = 'sqlite:///db.sqlite'
database = databases.Database(DATABASE_URL)
metadata = sqlalchemy.MetaData()

class BaseMeta(orm.ModelMeta):
    metadata = metadata
    database = database


class Person(orm.Model):
    id: int = orm.Integer(primary_key=True)
    name: str = orm.String(max_length=32)
    class Meta(BaseMeta):
        tablename = "persons"


class Item(orm.Model):
    id: int = orm.Integer(primary_key=True)
    name: str = orm.String(max_length=32)
    person: Optional[Person] = orm.ForeignKey(Person, nullable=True)
    on_sale: bool = orm.Boolean(nullable=False, default=False)
    class Meta(BaseMeta):
        tablename = "items"


async def create_db():
    engine = sqlalchemy.create_engine(DATABASE_URL)
    await database.connect()
    metadata.drop_all(engine)
    metadata.create_all(engine)
    return metadata


async def tests():
    person = await Person.objects.create(name='Person')

    itema = await Item.objects.create(
            name='ItemA',
            person=person,
            on_sale=False
    )

    itemb = await Item.objects.create(
            name='Itemb',
            person=person,
            on_sale=False)


    person_db1 = await Person.objects.select_related('items').get(pk=1)
    assert person_db1.items[0].name == 'ItemA'

    # This fails with alias name issues
    person_db2 = await Person.objects.select_related('items__name').get(pk=1)
    assert person_db2.items[0].name == 'ItemA'


async def disconnect_db():
    await database.disconnect()


async def main():
    await create_db()
    await tests()
    await disconnect_db()


if __name__ == "__main__":
    import asyncio
    asyncio.run(main())

[collerek]

With select_related you can only select nested models, not the columns. To select subset of fields, combine select_related with fields and/or exclude_fields.

[igormorgado]

Tried with both methods, maybe I'm doing something wrong (in both cases I have changed the on_sale field to nullable=True otherwise it fails on validation)

    person_db2 = await Person.objects.select_related('items').fields([
        'name',
        'items__name'
    ]).get(pk=1)
    print("Method 2:", person_db2.json(indent=2))

Outputs:

{
  "id": 1,
  "name": "Person",
  "items": [
    {
      "id": 1,
      "name": "ItemA",
      "on_sale": null
    }
  ]
}

And method 3

    person_db3 = await Person.objects.select_related('items').exclude_fields([
        'id',
        'items__id',
        'items__on_sale'
    ]).get(pk=1)
    print("Method 3:", person_db3.json(indent=2))

outputs

{
  "id": 1,
  "name": "Person",
  "items": [
    {
      "id": 1,
      "name": "ItemA",
      "on_sale": null
    }
  ]
}

In both cases I notice that the model is validated, hence, If I exclude some mandatory/non-nullable field, It raises:

ValidationError: 1 validation error for Item
on_sale
  none is not an allowed value (type=type_error.none.not_allowed)

Hence I have to: a. Change the model field to nullable (not possible in many cases); or b. add it to the return data (not wanted).

Is there something else I can do?

[michaeltoohig]

Ditto to that last point. I have a hashed_password field for my user model and it is understandably a nullable=False field since users need passwords. But to return a user model with .select_related without the hashed_password is raising the ValidationError.

[collerek]

Both pydantic and fastapi don't know if a given model is used in request or response.

So you cannot exclude mandatory (not nullable) fields in both pydantic and ormar, as without those the model would be invalid during creation (and at init pydantic/ormar don't know if you read or write to the database). It would require pydantic modification and changes in fastapi that I doubt will ever happen.

So basically in the case of mandatory fields you need a separate pydantic model (and no sense for ormar one as it's used only in final response). You can read more in docs: https://collerek.github.io/ormar/fastapi/response/ there is also section for request.

[igormorgado]

My case isn´t exactly a response, I'm just fetching the object then creating a smaller object to handle internally. IMHO there should be a way to .get() the object (in this moment validations occurs normally then select_fields() when the validations would not occurs, since is not the original data. Of course, if a return selected_object when a a given orm.Model is used in response_model, the validation should occurs normally.

Don't know if is doable.

[collerek]

The moment you call get() you get the ormar model that is constrained by pydantic rules.
So if you have fields there that are not nullable but not wanted the easiest is to switch to other model anyway.

You can even create manually or generate from ormar and simply switch like:

person_wo_fields = PersonPydanticModelWithoutFields(**await Person.objects.select_related('items').get(pk=1).dict())

Of course best to exclude all the unwanted fields in a query to speed up the query and later exclude the not nullable fields by parsing to pydantic model.

There is somewhere a discussion about read-only models where you can have multiple models with different fields set each to directly query the database, but it's not implemented.