How to query data from 'grandparent' table to form_ajax_refs

[NightSkySK]

I've been looking for a solution for a few days already and I can't find the right keywords to find the solution which I should apply in that case. (including searching flask-admin-related topics)

Let's assume that we have three tables one to many relations to each other. Like Warehouse, Rack, and Locations, so we can have multiple warehouse each warehouse has multiple Racks and each Rack have multiple Locations on it.

Not sure if it's needed but let's assume the following models:

import uuid
from typing import List, Optional

from sqlalchemy import ForeignKey, String, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship


class Warehouse(Base):
    warehouse_uuid: Mapped[str] = mapped_column(
        # UUIDType(binary=False),
        String(length=36),
        primary_key=True,
        index=True,
        default=lambda: str(uuid4()),
    )
    warehouse_short: Mapped[str] = mapped_column(
        String(3),
        unique=True,
        index=True,
        nullable=False,
    )
    warehouse_name: Mapped[str] = mapped_column(
        String(100),
        unique=True,
        nullable=False,
    )
    company_uuid: Mapped[str] = mapped_column(ForeignKey("company.company_uuid"))
    company: Mapped["Company"] = relationship("Company", back_populates="warehouse")
    racks: Mapped[Optional[List["Rack"]]] = relationship(
        back_populates="warehouse"
    )

    def __str__(self) -> str:
        return f"{self.warehouse_short} - {self.warehouse_name}"
    


class Rack(Base):
    rack_uuid: Mapped[str] = mapped_column(
        Text(length=36),
        primary_key=True,
        index=True,
        default=lambda: str(uuid.uuid4()),
    )
    rack_id: Mapped[str] = mapped_column(index=True, nullable=False)
    warehouse_uuid: Mapped[str] = mapped_column(
        ForeignKey("warehouse.warehouse_uuid"),
    )
    warehouse: Mapped["Warehouse"] = relationship(back_populates="racks")
    locations: Mapped[Optional[List["Location"]]] = relationship(
        back_populates="racks"
    )

    def __str__(self) -> str:
        return f"{self.warehouse_uuid} - {self.rack_id}"
    

class Location(Base):
    location_uuid: Mapped[str] = mapped_column(
        # UUIDType(binary=False),
        String(length=36),
        primary_key=True,
        index=True,
        default=lambda: str(uuid4()),
    )
    location_name: Mapped[str] = mapped_column(
        String(5),
        index=True,
        nullable=False,
    )
    rack_uuid: Mapped[str] = mapped_column(ForeignKey("rack.storage_uuid"))
    storages: Mapped["Rack"] = relationship(
        back_populates="locations"
    )

And of course question is related with sqladmin.

@register_view
class LocationAdmin(ModelView, model=Location):
    column_list = [
        Location.location_uuid,
        Location.location_name,
        Location.rack_uuid,  
    ]
    form_ajax_refs = {
        "racks": {
            "fields": [
                "rack_id",
            ],
            "order_by": "rack_id",
        }
    }

Once I create a new Location using this form_ajax_refs I can search and find field {warehouse_uuid} - {rack_id}, but of course I don't know which uuid belongs to which warehouse which is not very helpful. Once I create new location I would like to have visibility for {warehouse_name} - {rack_id} or even {company_name} - {warehouse_name} - {rack_id} so even if there is multiple same rack id on different warehouses I still able to choose the right one. How to solve it?

BTW SQLAdmin is awesome and I really want to learn this tool indeph. Great work! @aminalaee

[aminalaee]

Hmm..not sure what exactly the question is, but if your issue is that you can't select the right Rack instance then maybe update this:

class Rack(...):
    def __str__(self) -> str:
        return f"{self.warehouse.name} - {self.rack_id}"

I guess this will lead to some errors but that's solvable. Let me know if I got that right.

[NightSkySK]

Yes it's definitely going in the right direction, but not without new roadblocks:

INFO:     127.0.0.1:37365 - "GET /location/ajax/lookup?name=rack&term=a HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
[...]
    raise orm_exc.DetachedInstanceError(
sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <Rack at 0x2425dded0d0> is not bound to a Session; lazy load operation of attribute 'location' cannot proceed (Background on this error at: https://sqlalche.me/e/20/bhk3)

my engine and session are defined:

SQLALCHEMY_DATABASE_URL = "sqlite+aiosqlite:///./sql_app.sqlite"
engine = create_async_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)

async_session_maker = async_sessionmaker(
    engine,
    expire_on_commit=False,
)


async def get_async_session() -> AsyncGenerator[AsyncSession, None]:
    async with async_session_maker() as session:
        try:
            yield session
        finally:
            await session.close()

and Admin setup:

def create_admin(
    app: FastAPI, engine: Engine, auth_backend: AuthenticationBackend
) -> Admin:
    admin = Admin(
        app,
        engine,
        authentication_backend=auth_backend,
        base_url="/admin",
        session_maker=async_session_maker,
        title="mSpaceLock Admin Panel",
    )
    return admin


def register_views_to_admin(
    admin: Admin, views: list[Type[ModelView | BaseView]]
) -> None:
    for view in views:
        admin.add_view(view)


def setup_admin(app: FastAPI, engine: Engine) -> None:
    authentication_backend = AdminAuth(secret_key=settings.SECRET_KEY)
    admin = create_admin(app=app, engine=engine, auth_backend=authentication_backend)
    register_views_to_admin(admin, views=admin_views)

[aminalaee]

I can't think of any nice way to handle this, as this is how asyncio SQLAlchemy works, the __str__ method is not able load relationships.
In this specific case of Ajax the source is here:

sqladmin/sqladmin/ajax.py

Line 59 in 0abdef0

return {"id": str(get_object_identifier(model)), "text": str(model)}

Maybe the class can be overridden to make format method async, and instead do something like await model.format() and implement this instead of str.
And next step would be here:

sqladmin/sqladmin/models.py

Line 686 in 0abdef0

self._form_ajax_refs[name] = create_ajax_loader(

I think this can be a good start and we can figure out how to add this in SQLAdmin.

[NightSkySK]

If I understand correctly if I wouldn't overcomplicate my project by going into asyncio connection to db, __str__ the method would handle a load this relationship? I recently switched my project to async as I planned to use the fastapi-users library, but I haven't implemented it and stayed with another authentication method. Also, I found in topics here fastapi-users difficulty to work well with SQLAdmin AuthenticationBackend , so I'm not going in this direction any more. Theoretically is still a chance to revert back to a synchronized connection...

Or is there anything I can help to solve it to make it work with asyncio?

[aminalaee]

I think if you switch sync this issue will disappear, but anyway this should be fixed.
The problem with async is that the in __str__ method of Rack, you can't use Warehouse properties so:

# Rack
def __str__(self):
    return f"{self.warehouse.name} - {self.rack_id}"

Because the Rack instance queried did not load the Warehouse related model. But it can be more complicated if Warehouse also has a relation to another model.

We could work around this issue in SQLAdmin but it'll just fail in other places, for example if you log a Rack instance it will fail, it has nothing todo with SQLAdmin but if I'm not wrong the Rack instance won't load the related models by default.

So my solution is that we should avoid the __str__ method and add methods and overriding

sqladmin/sqladmin/ajax.py

Line 14 in 0abdef0

class QueryAjaxModelLoader:

is simpler.