Better understanding `create_pydantic_model` with foreign keys

[badlydrawnrob]

So I'm having a little trouble understanding create_pydantic_model where foreign keys are concerned — I have a feeling it might be easier (in some cases) to just create your own Pydantic models — perhaps the documentation could be a little more clear and extensive in this area? I imagined the automatic Pydantic models are flexible enough for most queries, but can't figure out how to do it reliably.

See example code here, I've tried:

Flat shape

I'm happy with a flat shape, but how do you create_pydantic_model for it? I think the way I've been trying it is wrong.

Without any pydantic model

fruits = await (
    Fruits.select(
        Fruits.all_columns(exclude=[Fruits.id, Fruits.color]), # Return the fruits table
        Fruits.color.all_columns() # Join on the colors table
    )
)

return fruits

Output without a response_model pydantic type (a list of fruits):

[
  {
    "image": null,
    "name": "Banana",
    "url": "4352d224-b0ce-4639-8696-942b2220fc0a",
    "color.id": 1,
    "color.color": "#cc0f35",
    "color.background": "#feecf0",
    "color.name": "red"
  }
]

What I've tried to do (flat shape)

My first thought was to include_columns=(Fruits.name, Fruits.color, Fruits.color.name, ...) but it seems only Fruits columns are allowed there (not the foreign table fields).

I've also tried a plain model with basic arguments (single result example)

FruitsModelOut: Any = create_pydantic_model(
    table=Fruits,
    model_name="FruitsModelOut",
)

fruits = await (
    Fruits.select(
        Fruits.all_columns(exclude=[Fruits.id, Fruits.color]), # Return the fruits table
        Fruits.color.all_columns() # Join on the colors table
    ).first()
)

FruitsModelOut(**fruits)

Outputs this, with none of the extra fields I'd expect (with color also set to null)

{
  "color": null,
  "image": null,
  "name": "Banana",
  "url": "4352d224-b0ce-4639-8696-942b2220fc0a"
}

Nested shape

This feels a little easier to understand, but I'd prefer a flatter model ...

FruitsAllModelOut: Any = create_pydantic_model(
    table=Fruits,
    model_name="FruitsAllModelOut",
    nested=True,
    include_default_columns=True,
)

@fruits_router.get("/", response_model=List[FruitsAllModelOut])
async def retrieve_all_fruits():
    fruits = await (
        Fruits.select(
            Fruits.all_columns(exclude=[Fruits.id, Fruits.color]), # Return the fruits table
            Fruits.color.all_columns() # Join on the colors table
        ).output(nested=True)
    )

    return fruits

Response (unfortunately it gives null value for id, but you can remove the include_default_columns argument to hide it)

[
  {
    "id": null,
    "color": {
      "id": 1,
      "color": "#cc0f35",
      "background": "#feecf0",
      "name": "red"
    },
    "image": null,
    "name": "Banana",
    "url": "4352d224-b0ce-4639-8696-942b2220fc0a"
  }
]

TL;DR

So yeah, using "magic" at the moment feels a little harder to understand than just creating your own Pydantic models for foreign key fields with a flat shape, or for more complex queries. Perhaps this is the limitation of create_pydantic_types? Or I'm misunderstanding.

[sinisaos]

@badlydrawnrob If you look at the source code of create_pydantic_model, if you want to use FK, you have to use nested=True argument and I think that is the correct way to deal with nested Pydantic models. You can achieve this with objects or select queries as well. Something like this.

# nested Pydantic model
FruitsModelOut: Any = create_pydantic_model(
    table=Fruits,
    model_name="FruitsModelOut",
    nested=True,
)

# using objects query
@app.get("/", response_model=List[FruitsModelOut])
async def retrieve_all_fruits():
    fruits = await Fruits.objects(Fruits.all_related())
    return fruits

# using select query
@app.get("/", response_model=List[FruitsModelOut])
async def retrieve_all_fruits():
    fruits = await Fruits.select(
        Fruits.all_columns(exclude=[Fruits.id, Fruits.color]),
        Fruits.color.all_columns(),
    ).output(nested=True)
    return fruits

The result is nicely nested JSON response

[
  {
    "color": {
      "color": "#cc0f35",
      "background": "#feecf0",
      "name": "red"
    },
    "image": null,
    "name": "Banana",
    "url": "ece2894a-be27-4723-ba5a-ee5d5a163f82"
  }
]

If you want, as you said, a flat shape (if I understand correctly, you need a flat JSON response where the FK columns are not nested), you can write your own Pydantic models and there is nothing wrong with that. I mostly write custom Pydantic models and I don't use create_pydantic_model a lot (I think it is primarily used for Piccolo Admin). My main weakness of create_pydantic_model is the lack of support for M2M columns which can be easily solved with a custom Pydantic model.
To get back to the flat shape (although I don't understand why you would do that) you can do something like this

from pydantic import BaseModel, UUID4

class FruitsModelOut(BaseModel):
    name: str
    image: str | None = None
    url: UUID4
    # flatten Colors FK
    color_color: str
    color_background: str
    color_name: str

@app.get("/", response_model=List[FruitsModelOut])
async def retrieve_all_fruits():
    fruits = await Fruits.select(
        Fruits.image,
        Fruits.name,
        Fruits.url,
        Fruits.color.color.as_alias("color_color"),
        Fruits.color.background.as_alias("color_background"),
        Fruits.color.name.as_alias("color_name"),
    )
    return fruits

and result then is

[
  {
    "name": "Banana",
    "image": null,
    "url": "ece2894a-be27-4723-ba5a-ee5d5a163f82",
    "color_color": "#cc0f35",
    "color_background": "#feecf0",
    "color_name": "red"
  }
]