Using sqlalchemy paginate with multiple entities at once

[gfguilherme]

Hello everyone,

I'm trying to use fastapi_pagination.ext.sqlalchemy.paginate() to paginate the results of a sqlalchemy.select() statement that selects multiple entities at once.

Here's an example of the query I'm trying to paginate:
stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)

I'm getting Pydantic validation errors for all fields, and I suspect that this might be because the query execution returns a tuple with the entities when multiple entities are selected.

Has anyone else encountered this issue and found a solution? Any help or suggestions would be greatly appreciated.

Thank you!

[uriyyo]

Hi @gfguilherme,

Could you please show what your schema looks like?

Could you please try to use a schema like this?

class SearchOut(BaseModel):
    user: UserOut = Field(..., alias="User")
    group: GroupOut = Field(..., alias="Group")

    class Config:
        orm_mode = True

[gfguilherme]

Hello @uriyyo, thank you for responding!

Using the schema you suggested, I was able to get the response I was looking for.

class SearchOut(BaseModel):
    user: User = Field(..., alias="User")
    address: Address = Field(..., alias="Address")

    class Config:
        orm_mode = True

However, the object I receive through FastAPI has the keys in sentence case, such as "User" and "Address". Is there a way to convert them to lower case, such as "user" and "address"?

{
  "User": {
    "id": 1,
    "name": "Alice",
    "email": "alice@gmail.com"
  },
  "Address": {
    "id": 1,
    "user_id": 1,
    "street": "Maple Avenue",
    "city": "Springfield",
    "state": "Illinois",
    "zip_code": "62704"
  }
},

Also, if I want to perform a query that includes the individual attribute "Address.state":
stmt = ( select(User, Address.state) .join(Address, User.id == Address.user_id) .order_by(User.id, Address.id) )

How can I write a schema that includes "Address.state"? e.g:

{
  "user": {
    "id": 1,
    "name": "Alice",
    "email": "alice@gmail.com"
  },
  "address": {
    "state": "Illinois"
  }
}

Thank you for your help!

[uriyyo]

@gfguilherme Could you please try code bellow:

class AddressSchema(BaseModel):
    state: str

    class Config:
        orm_mode = True


class UserSchema(BaseModel):
    id: int
    name: str
    email: str

    class Config:
        orm_mode = True


class SearchOutSchema(BaseModel):
    user: UserSchema
    address: AddressSchema

    class Config:
        orm_mode = True


@app.get(
    "/users",
    response_model=Page[SearchOutSchema],
)
def get_users(db: Session = Depends(get_db)) -> Any:
    return paginate(
        db,
        select(User, Address.state).join(Address, User.id == Address.user_id).order_by(User.id, Address.id),
        transformer=lambda items: [{"user": user, "address": {"state": state}} for user, state in items],
    )

[uriyyo]

Hi @gfguilherme,

Do you need any help? Can I mark this discussion as answered?