How to efficiently query for a subset of required fields

[johnthagen]

I have an ormar Model that has a set of required database fields. I need to set them as required to model the underlying database correctly and have the OpenAPI endpoints be correct.

But in another route I want to query just a subset of the fields in the database for the same table to make the query/serialization more efficient. As explained here: https://collerek.github.io/ormar/queries/select-columns/

Mandatory fields cannot be excluded as it will raise ValidationError, to exclude a field it has to be nullable.

So I can't reuse the same Model.

I tried to create another Model with only a subset of the fields but poitning to the same tablename, but then I got a SQLAlchemy error:

sqlalchemy.exc.InvalidRequestError: Table 'my_table' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

What is the best way to make queries for a subset of fields for which there is an existing model with required fields defined?

[collerek]

This one is quite tricky -> cause if you want to have multiple models operating on one table and each of them has different required columns you would end up with some of the models that should be read only.

Cause if you insert a row without columns that are required by the other model you wouldn't be able to read this row with another model as the data would be corrupted (required columns would be null).

The other solution is that each model is still storing the data in the same table, but each row is "marked" as one of the models, so the data is stored in the same table but rows are actually separated and operates on their own rows only.

Both of those scenarios are kinds of inheritance that are not supported as of now, but that would also not solve your use case as you want to read the same rows with different models.

The recommended solution is to use either values or values_list cause in those calls you can exclude any column you don't want (cause the result is not parsed to the model you don't have to worry about missing fields in the validation). Then you should either parse this result to newly created pydantic models explicitly or use response_model in fastapi.

[johnthagen]

Thanks! .values() really helps here. Might I suggest that the docs be updated to:

Mandatory fields cannot be excluded as it will raise ValidationError, to exclude a field it has to be nullable. Consider using .values() to do this.

[collerek]

Can you please make a PR for this?

[johnthagen]

Can you please make a PR for this?

#366